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.

Real Data	More Real Data	Even More Real Data
More Data	More Data	More 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.

FIXES:

Fortunately there are a couple of things we can do to remedy this.

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

"Excel 8.0;HDR=YES"

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

        
Function Main()

	dim pkg
	dim conXL

	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
End Function