How to refer to Excel columns without headers in TSQL
I dived into documentation but since I didn't find any information (Well, actually I found similarities with some other SO questions but not what I want), I'm asking you guys to help me :
I'm executing a tsql query over an ADODB connection, to retrieve data from an Excel File (*.xlsx) into another one. This file is composed as follow :
Header1 Header2 Header3 --------- --------- --------- A1 B1 C1 A2 B2 C2 A3 B3 C3 .... .... ....
I want to retrieve the headers too so here's a part of the whole program, containing the connection string
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection Set rs = New ADODB.Recordset With con .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Data Source=path\file.xlsx; _ Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1""" Set rs = .Execute("Select * From [Sheet1$]") .Close End With
Here I retrieve all the columns, but what if I want column B, then column A, then column C, e.g. something like that :
Set rs = .Execute("Select colb, cola, colc From [Sheet1$]")
The problem there is that I don't know the terms which should replace colb, cola, colc since I can't use the headers of the columns
PS : I don't know much about these technologies, so I may be wrong with the terminology.
Not my answer, but from a colleague who sits next to me ;)
Set rs = .Execute("SELECT F2, F1, F3 FROM [Sheet1$]")