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

Simon · Accepted Answer · 2011-11-29 14:50:02Z

7

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.

answered Nov 29, 2011 at 14:50

Simon

1,2071 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related