Importing from Excel returns NULL values when it shouldn't

[ptpenry]

I need to import an Excel 97 spreadsheet into SQL 2000. Most columns import correctly. However, one column which has a combination of text, numeric entries and NULLS refuses to import the numerics - they just show as NULL.

I have set up the destination table to be "Varchar(30) NULL" which should import the 3 formats correctly. I have tried setting the Excel column format to be General and Text but neither work.

I'm not sure whether I need to make changes to Excel or SQL Server to make this work. Any ideas ?

Thanks

 

[JayKusch]

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 &quot;Text&quot;.; 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(&quot;Microsoft Excel 97-2000&quot

conXL.ConnectionProperties(&quot;Extended Properties&quot.Value = &quot;Excel 8.0;HDR=NO;IMEX=1&quot;

Main = DTSTaskExecResult_Success
End Function





Thanks

J. Kusch