469,625 Members | 892 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

How to Import data with leading zero

benchpolo
142 100+
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.
Jul 1 '10 #1
7 11267
Jerry Winston
145 Expert 100+
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.
Jul 2 '10 #2
benchpolo
142 100+
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.
Aug 20 '10 #3
ck9663
2,878 Expert 2GB
What "issue" are you having?

~~ CK
Aug 20 '10 #4
NeoPa
32,201 Expert Mod 16PB
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).
Aug 23 '10 #5
benchpolo
142 100+
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.
Aug 24 '10 #6
NeoPa
32,201 Expert Mod 16PB
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.
Aug 25 '10 #7
ck9663
2,878 Expert 2GB
True, Neo...

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

Good Luck!!!

~~ CK
Aug 25 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by GarryJones | last post: by
8 posts views Thread by Andrew Poulos | last post: by
4 posts views Thread by bobm2005 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.