How to Import data with leading zero

[benchpolo]

I have an excel spreadsheet (.csv) format of list of zipcodes that I am trying to import to my custom table zipcodes.. I having issue in importing a zip code with leading zero. Can someone assist me on this? I'm using MSSQL server 2005 import/export wizard.
Thanks.

[Jerry Winston]

Make sure you're storing the zip codes in a string type field. Of course, there are no leading zeroes with numeric type fields, therefore I tend to think the Import/Export Wizard analyzed the data in the csv file and auto typed the zip field as a numeric type in the destination table.

Although truncating the leading zeroes is not the desired result, consider the storage, indexing, and querying implications of a CHAR(5) field versus an INT. The conversion truncates the leading zeroes, but does not loose any data that cannot be easily recovered through proper formatting.

[benchpolo]

But I need to see the leading zeroes in the table. For example, if I custom format the zip code as 08111 it should be 08111 also in SQL table.

[ck9663]

What "issue" are you having?

~~ CK

[NeoPa]

I think you'll need to export the data as a string. This means to include quotes around it. Otherwise, data with only numeric characters in it is quite correctly interpreted as numeric. That's how it works.

I'm not certain that overriding that in SQL is impossible, but I strongly suggest that you work more naturally with your data where possible (IE. Store string data as strings rather than relying on any auto-conversions).

[benchpolo]

When I place the data onto an excel spreadsheet, the data looks lils 09999, but after importing to SQL 2005 tables the leading zero is ignored.

[NeoPa]

It's not a good idea to use Excel to manipulate CSV files I'm afraid. It's very difficult to get it to stop making unfounded assumptions about your data. Numeric data which is enclosed in quotes is nevertheless treated as numeric data, even though the quotes indicate it should be treated as textual.

[ck9663]

True, Neo...

Bench, save your file as .CSV/.TXT and import that instead.

Good Luck!!!

~~ CK