Preserve leading zeros when importing Excel into SQL

Question

My office uses excel to prepare our data before importing it into a SQL database. However, we have been expreiencing the following error.

When the data is imported from one computer it loses all of the leading zeros. However, when it is imported from a different computer it imports perfectly.

An example of the leading zeros are that our item numbers are required to be formatted as "001, 002, 003,... 010, 011, 012,... 100, 101, 102, ect".

1) The excel file is stored on a server so there is no difference in the file. 2) If the users swap workstations the result stays with the computer, and doesn't switch with the user. 3) The data is formatted as text. It has been formatted as text both from the Data Tab and from Format Cells.

Is there a setting within excel that is specific to the computer and not the spreadsheet which will affect exporting the data? Or is there a non-excel specific setting which will cause this?

Masinissa

HOw are you importing the data into the database? PLease show some code or something relevant. – Arrivederci

what version of sql-server? – Pigeonwing

Answer 1

Its best to avoid the 'TEXT' format option. Confusingly, it does not force the contents of a cell to be a text data type, and it wreaks havoc when a formula references a 'TEXT' format.

To add to the previous answer (with all of the caveats about if this is a good idea), you can use the TEXT worksheet function

=TEXT(A1,"000")

to guarantee an actual text string with leading zeros if needed.

Impossibility

Answer 2

Not sure what the benefit of padding data before inserting it into the database would be...(takes more space, slower searching, etc.). Sounds like you're formatting it for output (?), which might be more efficiently done elsewhere.

But anyway -- here are some ideas for your SELECT (sql) statement:

RIGHT(1000 + [excel field], 3)

or another one would be

REPLICATE('0', 3 - LEN([excel field])) + [excel field]

Something you can do to the Excel field itself (before import) is prefix it with a ' (apostrophe). Notice if you type 0007 into Excel, it will change it to 7, but if you type '0007, it will keep the leading zeros.

Pigeonwing

Absolutely! The apostrophe as a prefix to retain zeroes in the text field is another good fix. – Agneta

Answer 3

Depending on number of leading zeroes that you require, you can select your data/column in Excel, go into Excel >> Format >> Custom >> type in however many zeroes you require into the Type field (i.e. 000000000 for a 9-digit number with leading zeroes), and it will automatically preface with the correct number of leading zeroes to make the numerical string the correct length (i.e. 4000 = 00004000).

Note, this only works with numerical data, not text, but depending on the scenario it may be more useful to retain your data in numerical format - the example you gave listed numerical data only, and often retaining the numerical format is a benefit for analysis.

Agneta

Careful here, the Excel format only effects the way the number is displayed. The actual contents of the cell (which is what is usually read) would still be '1' rather than '001'. – Impossibility

True, we'll need to wait for the asking party to clarify at what point in the analysis process the Excel file is used. My assumption was that Excel is the end-product, because very few organizations would use Excel to feed into a database application or whatnot (I could be wrong, and you know what they say about assumptions...) – Agneta