Excel import gives NULL rows with DTS
February 20th, 2006 // 5:38 pm @ Amar
Sometimes, when the excel column contains numeric and text data, and you try to import the excel data using say DTS, it will give you either the text values or the numeric values and return NULLs for the other. This is a known problem in excel as it scans rows ( based on the set count, which can be 1 ), and determines the datatype for that column. Any row which does not adhere to that guessed datatype, gets imported as NULL for that column.
A solution to this problem as shown by the knowledge base is to add the option IMEX=1; in the connection string. If you use something like DAO to open the excel data, then you can write as according to the MS knowledge article ID 194124 :
Set Db = OpenDatabase(“C:TempBook1.xls”, _
False, True, “Excel 8.0; HDR=NO; IMEX=1;”)
But the problem is that if we are using DTS, and the excel connection object is opening the connection. So there is no code present as shown above in the package. But fortunately, there is a place where we can modify it. In the excel connections properties ( visible either thru disconnected edit, or a dynamic property task or thru the API functions ), there is a key called OLE DB Properties -> Extended Properties. Add the setting IMEX=1; to the existing value and your problem should be solved. You will be able to import the entire excel sheet data thru DTS without worrying about missed values. This setting puts the mode of excel as import, and retreives all values as text instead of doing a data type guessing.
Category : SQL Server
http://
5 years ago
Thanks for publishing this – I knew the solution from about a year ago, but had forgotten and needed it today. Your blog was nice and high in Google, gave a solution (unlike MSDN) and very clearly written. So many thanks!
http://
5 years ago
Thanks a lot.
http://
4 years ago
1. Format the column and rekey the data
If we go back to our Excel Spreadsheet and highlight the offending column then we can right click on one of the cells and it will bring up a context menu. In that context menu is “Format Cells”. We select that and then look down the list of options and select “Text”. Just doing this won’t magically fix the problem. We need to reenter the data as well.
2. Access the Extended Properties of the driver
If you are lucky enough to be using SQL Server 2000 then you can use Disconnected edit to access the extended poperties of the Excel ISAM driver and add an extra value pair to it. Where do we find these extended properties then? This is what I did on my PC
Right click on empty space in your DTS designer window
Choose “Disconnected Edit…”
Open up the connections
Open up your Excel Connection
Look in the OLE DB Properties of the connection
The tenth item in the list is Extended Properties
Now look across to the right hand pane and you will see a table with two columns. The one we want has value in the leftmost column and mine has “Excel 8.0;HDR=YES;”
Double click on the value and change the string to read “Excel 8.0;HDR=YES;IMEX=1″
There are registry key entries associated with this change and they can be found at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\\Engines\Excel
You will notice that ImportMixedTypes is set to “Text”.; Microsoft also say that you may need to additionally set the TypeGuessRows entry to a value of 8
with the information i exported data from excel to sql server
but i don’t want to reenter the data in excel sheet .
http://
4 years ago
Thank you, this is exactly what I was looking for…
http://
4 years ago
If you wanna force it for text, just have a macro to prefix all the cells with an apostrophe. This way, DTS will not miss any cell!!
Sub Macro1()
Call ConvertToString(“G”, 1281)
End Sub
Sub ConvertToString(SrcCol, LastRow)
Dim FirstDataRow As Integer: FirstDataRow = 4
For i = FirstDataRow To LastRow
Range(SrcCol & i).Select
Dim CurrVal As String: CurrVal = ActiveCell.Value
ActiveCell.Value = “‘” & CurrVal
Next
MsgBox (“Finished!”)
End Sub
Umair
3 months ago
I tried all the solutions including yours but no luck, lame MS.
Amar
3 months ago
Hi Umair, there are quite a few listed out in the comments. Thanks to everyone who posted alternate options. Did you try some of these solutions?