Query Excel using ADO from VB.NET when a column name is a number

Question

I am using VB.NET to query via SQL an Excel spreadsheet using ADO.

The general way is like this:

SELECT [firstname], [secondname] FROM [Sheet1$]

Which works perfectly
However, one of the headings is called 3, so I want to do:

SELECT [firstname], [secondname], [3] FROM [Sheet1$]

However, this does not work. I know I can use * to get all columns, but later on I want to use (using dataReader):

dr("3") which won't work

Any ideas?

Answer 1

When using (or implying) HDR=YES in the connection string, an illegal column name will be replaced with the column's ordinal position appended with the letter F.

For example, if your column named 3 is the fifth column in the Excel Range then its name will be considered to be F5.