PROBLEM: Why does Excel Insert NULLs into my table when I know there is good data in there?

When you import data from Excel the driver looks at the first 8 rows of data in a column and makes the decision that all data is to be of that type. So for example say I have the following data in an Excel spreadsheet column

Row Number	Row Data
1	ABC
2	DEF
3	GHI
4	JKL
5	MNO
6	PQR
7	STU
8	VWX
9	1
10	2

If we try to import this column into a character field in a SQL Server table then Excel will at the time of import look at the data and decide that our data is text. Fair enough I hear you all shout because it is and there is no reason you cannot enter a string of '1' into a SQL Server table. However. Excel will not see it like this. Instead it will look at lines 9 and 10 and decide they are incorrect and insert NULL instead. This is the best of the two outcomes for your package. If the column which you are inserting into does not allow nulls then the transformation will fail.

FIXES:

Fortunately we do have a couple of things we can do which will help us to evade the problem.

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\<version of 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

One word of warning with doing this though is that setting IMEX to 1 causes Excel to change the way it handles mixed data so when you next go to do updates or inserts using this column the results you want may not be what you get. As always be careful and test,test and then test again.

3. Use an ActiveX script to access the Extended Properties of the driver.

You can drop an ActiveX script onto your designer and add this code (change the properties to match your environment).

        
Function Main()

	dim pkg
	dim conXL
	dim prp

	set pkg = DTSGlobalVariables.Parent
	
	set conXL = pkg.Connections("Microsoft Excel 97-2000")

	conXL.ConnectionProperties("Extended Properties").Value = "Excel 8.0;HDR=NO;IMEX=1"

	Main = DTSTaskExecResult_Success
End Function    
    
    

UPDATE - 31/05/2005

On the newsgroups recently this same issue appeared and a new piece of advice surfaced from Helge Rutz. Thanks Helge.

"The Excel provider can behave a little bit strangely. The import continues as long as there are rows with values, but unfortunately sometimes datatype formating is interpreted as data, resulting in the import of NULL values. The driver will keep reading rows as long as it thinks there is data in the cells. When you look at the spreadsheet you may see no data and be confused but what you have to remember is that the formatting of the cells is considered by the driver to be data.. The only way to avoid this, regardless of formatting, is by specifying a SELECT statement for the excel data where you define a WHERE clause with which column should not be NULL. Just select the query option for the source of the transformation and use the Tablename shown in table dropdown above."