PROBLEM: Excel treats first line of data as column headers?
When importing data from an Excel spreadsheet the Excel ISAM driver will always
treat the first row of data as a header. This is all very well and good
so long as your first row of data is a header and not real data. Here is
an example few rows from an Excel Spreadsheet.
||More Real Data
||Even More Real Data
If we try to import this into a SQl Server table then the first row will be
treated as column headers and provided we meet all other criteria in the load
we will only see one row inserted into SQL Server.
One of the suggested fixes can be found in the registry. If we look for this key FirstRowHasNames
at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\<version of
jet>\Engines\Excel then setting this value to 0 or False is supposed to enable us to treat the first row as data.
It is the author's experience that this is ineffective.
Fortunately there are a couple of things we can do to remedy this.
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
change one of the values. 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 in 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
Double click on the value and change the string to read "Excel 8.0;HDR=No"
2. Insert a dummy line into
the Excel spreadsheet
This will have the effect of bumping down
our data into cells which Excel acknowledges as being data.
You can find an example of how to do this at
Insert Header Row in Excel Spreadsheet
3. Use the JET provider
instead of the Excel Provider. (MDAC 2.1 - 2.5 SP1)
In the versions of MDAC mentioned you can
use the JET provider to connect to Excel which exposes the HDR extended
property. This was done away with in MDAC 2.6 and above
4. 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).
set pkg = DTSGlobalVariables.Parent
set conXL = pkg.Connections("Microsoft Excel 97-2000")
conXL.ConnectionProperties("Extended Properties").Value = "Excel 8.0;HDR=NO"
Main = DTSTaskExecResult_Success