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."