R
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?
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?