How do I specify multiple header rows when using SQL to query an Excel spreadsheet?

Question

I'm trying to query an Excel spreadsheet using SQL. The problem I'm facing is that one of the sheets I would like to query has 2 header rows. This is a sheet from a report I receive weekly from an external source and have an archive going back 3 years, so simply removing the extra row isn't an option.

Is there any way to tell Excel(/SQL) to treat both rows as headers (or to ignore the first row altogether - the important header information is in the second row)?

here is my connection string if it makes any difference:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & fileName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0"";"

Thanks,

Gary

Answer 1

You can query a range of cells starting from row 2:

SELECT * FROM [Sheet1$A2:S100]

The troublesome part is finding what the end of the range should be. You could put in a ridiculously large number, but then you'd have to add special handling for the rows of NULL at the end.