CODEWITHSUNDEEP
t-sqlexceladodbvba
Julien Ganis
Julien Ganis

Reputation: 81

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

Regards

PS : I don't know much about these technologies, so I may be wrong with the terminology.

Upvotes: 2

Views: 442

Answers (2)

Juliusz
Juliusz

Reputation: 2105

Not my answer, but from a colleague who sits next to me ;) 

Set rs = .Execute("SELECT F2, F1, F3 FROM [Sheet1$]")

(not tested)

Upvotes: 3

Juliusz
Juliusz

Reputation: 2105

Have you tried to read the columns as they are in the file:

Set rs = .Execute("SELECT * FROM [Sheet1$]")

and later map them to the required variables? I mean - what do you do with rs later in your code?

Upvotes: 0

Related Questions