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.

"Richnet" wrote:

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?