Using SQL to query an Excel worksheet without a header row

Question

I have been searching for a solution for a while, and I find myself coming up empty handed.

The question is: Can you build a SQL query against a worksheet if you don't have a distinct header row for column references?

Easily enough:

  |     A     |     B
1 | FirstName | LastName
2 | John      | Davis
3 | Mary      | Parker

SELECT [LastName] FROM [Sheet1$] WHERE [FirstName] = 'John'

--> "Davis"

However, I tend to work with header-less CSV files, so what could I do if I don't have the header row to steer by?

  |     A     |     B
1 | John      | Davis
2 | Mary      | Parker

SELECT ??? FROM [Sheet1$] WHERE ??? = 'John'

To extend the question, it could equally interesting to know how to reference a row number - if possible - say I want to find the Last Name (column B) relative to row [2]

Thanks in advance for any input on my conundrum!

-B

Solution

You can go through this link:

http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx

Providing the HDR attribute as NO, will automatically name the columns as F1 to Fn.

Comments

[Bjørn H. Sandvik]

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sFile & "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"