SQL Import Wizard crashes to zero
I read all posts about prefixing numbers with 'and set IMEX = 1 in the connection string; nothing seems to do the trick for me.
Here: Excel mixed data column - 99% numbers (some start with 0) 1% text.
PROGRAM ACTIVATING on SQL Server 2005 table / column table - varchar (255).
The import works fine locally, but as soon as I move the code into a file (GoDaddy) it drops the leading 0 in the column.
Any ideas?
ps I knew about the solution to change the registry, fact - the value was set to 0 in my dev box, but the answer made me realize that the value was not set in the PRODUCTION SERVER :)
source to share
The ISAM driver only displays the first 8 lines, but you can change this behavior by changing the registry:
http://sqlserversd.wordpress.com/2008/09/14/ssis-excel-values-import-as-nulls/
But yes, using Excel to transfer data between machines is a nightmare ... Is there no other way to send data?
source to share
Yes. The Excel driver only selects the first 8 rows to determine the data type. This means the column is assumed to be numeric: "bob" does not appear on lines 1 through 8.
The data type of the column of the target table does not matter.
The problem has been there for a long time , I saw it in 2003 for the first time.
source to share
There is a quick and clever way to do this. following these steps, but first copy all data / columns and rows from the actual excel sheet to another excel sheet just to be safe so that you have the actual data to compare.
Steps:
- Copy all the values in the column and paste them into notepad.
- Now change the column type to text in your Excel sheet (it truncates leading / trailing zeros), don't worry about that.
- Go to Notepad and copy all the values that you pasted just now.
- Go to Excel sheet and paste the values in one column.
If you have more than one column with 0 values, just repeat the same. Your excel document is now ready to import with null values :).
Happy Days.
source to share