SQL import wizard drops leading zero

Question

I've read all the posts about prefixing the numbers with ', and setting IMEX=1 in the connection string; nothing seems to do the trick for me.

Here's the setup: Excel column with mixed data - 99% numbers (some start with 0) 1% text.

PROGRAMATICALLY mporting into SQL Server 2005 table / column type - varchar(255).

Import works fine locally, but once i move the code to production (GoDaddy), it drops the leading 0's in the column.

Any ideas?

p.s. I knew about the registry change solution, matter of fact - the value was set to 0 on my dev box, but the answer made me realize that the value wasn't set on the PRODUCTION SERVER :)

Answer 1

Yes. The Excel driver only sample the 1st 8 rows to determine the data type. This means that it assumes the column is numeric is "bob" does not appear in rows 1 to 8.

The target table column datatype is irrelevant.

Ths issue has been there for a long time, I saw it in 2003 the first time.

BOL notes on excel import

Answer 2

We usually save the file as a .csv file or .txt file and then the issue doesn't occur.

Answer 3

The ISAM driver only samples the first 8 rows, but you can change that behaviour through a registry change:

http://sqlserversd.wordpress.com/2008/09/14/ssis-excel-values-import-as-nulls/

But yes, using Excel for machine-to-machine data transfer is a nightmare...Is there no other way you can be sent the data?