Excel 2007 is stupid dealing with zipcodes

[Richnet]

I'm trying to import into a SQL 2008 DB. The data type on the column in the
DB is nvarchar(50) The spreadsheet we receive has a zip column that contains
both 5 digit and 9 digit zips. The 9 digit zips have hyphens. Regardless of
the format I apply to the column, Zip, text, custom #####-####, etc. - I lose
the leading zeros when they import. No problem there, I have a SQL script to
put them back. However, the zips that are 9-digit do not import AT ALL so I
get NULL as a result on several thousand zips.

OK fine then. I'll try first to format the zip column in the spreadsheet to
zip+9 so everything is in the same format and the OLEDB provider won't get
confused. You would think that the zips that are 5 digit i.e. 32904 would
change to 32904-0000 right? Nope, Excel gives you 0003-2904. Brilliant.

Anyone have any ideas other than something like concatenating -0000 onto all
the zips that are 5 digit in the spreadsheet first (which is cumbersome and I
should not have to do)before I run my import?

 

[Richnet]

The solution I came up with is to format the excel column to "text", then
change the data type on the zip field in the database to nvarchar(MAX). All
the zips import that way. I still lose the leading zeros on the 5-digit
zips, but the funny thing is the 9-didgit zips import fine, leading zeros
still intact.

 

[Ron Rosenfeld]

OK fine then. I'll try first to format the zip column in the spreadsheet to
zip+9 so everything is in the same format and the OLEDB provider won't get
confused. You would think that the zips that are 5 digit i.e. 32904 would
change to 32904-0000 right? Nope, Excel gives you 0003-2904. Brilliant.

I have no idea how your DB treats formatted Excel data. But if you want to
format cells that can have either 5 or 9 digit zip codes in Excel, and you are
getting the results you post, then you are not using the correct format option.

Formatting does not change the contents of the cell -- merely its appearance.

But if you want both 5 and 9 digit numbers to look like:
00000-0000

Then the format you should use is
Format/Cells/Number/Custom Type: [<100000]00000"-0000";00000-0000

or, if you want to turn the contents into a text string, you could set up a
helper column and use a formula like:

=TEXT(A1,"[<100000]00000""-0000"";00000-0000")


--ron