How to Solve Problem That Excel Data Imported Into SQL Server Via DTS Became Null
November 21st, 2007 by Andrew Chen
If you are working with SQL Server then you will find from time to time you will need to import data from Excel into SQL server in order to query or manipulate the data easily. One way and I think the most used method to import data from Excel into SQL server is to use DTS (Data Transformation Service).
Once it determines the data type of a column it will ignore the data that is not conforming to the data type and does not do any implicit conversion. The result is that if your column has number and text then most likely your number or your text will became NULL after they are imported into SQL server. I had tried to see whether DTS has properties I can change to take care of this problem but I failed. So a workaround have to be use to take care of this issue.
One way to work around this problem is to open your excel file, copy your column to notepad and then format the column as text in excel. After that copy the data from notepad and paste it back to the column as text. After that the column can be import with out losing any values. You would ask what if I need a scheduled job to do that. Well there is another way. That is to link the Excel file into SQL server by running the following statement.
EXEC sp_addlinkedserver 'Excel', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\Book1.xls', NULL, 'Excel 8.0;HDR=Yes;IMEX=1'
Then you use a sql statement like this to import the data into SQL server. You may need to change the security setting of the linked server in order to run the query successfully
select * into ExcelData from Excel...Sheet1$
The trick is on the option “IMEX=1″. It tells the Jet engine not to guess the data type and treat them all as text (nvarchar by default)
Related Posts:
(No Ratings Yet)
Loading ...
The Data UpLoader to SQL Server that’s available free on the Pervasive DataTools web site let’s you map data from Excel and many other data sources to tables in SQL Server. It includes a transformation scripting language for cleaning and manipulating the data, and it lets you select the data type for each field in the SQL Server table from a list of real SQL Server data types. Pretty cool tool…I would recommend it to just about anyone who needs to convert data to SQL Server.
Hope this helps!
Thanks a lot for the tip. Worked perfectly!
Thanks so much for that tip.
I was banging my head into a wall for a day or so.
Worked perfectly!
Thanks,
Travis
Thanks a lot.
I shall be saying good bye to all those CONCATENATE apostrophe formulas in Excel.
Moran
Another alternative is a registry hack on the SQL Server host machine:
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel
change “TypeGuessRows” to 0
This tells DTS to examine all the data before setting the data type.
Rad… i was wracking my brain trying to figure out why I kept importing nulls when the data was clearly there. I even tried converting the cell formatting in excel to just TEXT… still nothing. the notepad trick worked.
I wanted to thank you for this tip. It help me sort out a problem with NULL values when importing to SQL using Jet engine.
I am glad that help. Thank you for commenting
Thanks so much for an intelligent and usable article.
Thank you thank you thank you! That worked, nothing else suggested anywhere else, ever, has worked for me.
I want to expand on what Alan Houck mentioned.
Firstly, while the default for TypeGuessRows is 8, setting this to 0 “only” examines the first 16,384 lines. Obviously I say “only” because that’s far better than 8 but it doesn’t examine ALL 65536 rows of an Excel 2000 or 2003 sheet (not sure about 2007 or 2010 as I haven’t used those but I know they allow more than 2^16 rows).
Secondly, I’ve done imports with SQL Server 2005 Import Wizard and even with TypeGuessRows set to 0, I’d still get NULL imports when a column had mixed datatypes (and there were far less than 16,384 rows of data imported).
That being said, I’ve had experiences where I didn’t use a script with the “IMEX=1″ parameter and the TypeGuessRows change helped import the data correctly (usually an error was avoided in these instances “column doesn’t allow mixed datatypes”). I’ve also had experiences where the “IMEX=1″ was used in a script and that was the only way to get data imported correctly…but in all of those instances the TypeGuessRows were set to 0 so I’d be interested to see an instance where “IMEX=1″ doesn’t work because TypeGuessRows = 8.
I think the best bet is, change TypeGuessRows AND use a script with IMEX=1 at all times.
HTH