Different SQL queries in Excel VBA for csv files using ADODB connection

Question

I am trying to retrieve data from CSV file using a SQL Select in Excel VBA. This is what I've tried.

Sub GetMyCSVData()
Dim xlcon As ADODB.Connection
Dim xlrs As ADODB.Recordset

Set xlcon = New ADODB.Connection
Set xlrs = New ADODB.Recordset

Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer

currentDataFilePath = "C:\MyFolder"
currentDataFileName = "MyFile"

xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"
xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & 
  "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""

xlcon.Open

MyQuery = "SELECT * FROM [" & currentDataFileName & ".csv]"

xlrs.Open MyQuery, xlcon
xlrs.MoveFirst
nextRow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
Worksheets("Sheet1").Cells(nextRow, 1).CopyFromRecordset xlrs

xlrs.Close
xlcon.Close

Set xlrs = Nothing
Set xlcon = Nothing
End Sub

This is working fine, but then, I tried to be more precise with my query:

MyQuery = "SELECT MyField FROM [" & currentDataFileName & ".csv]"
xlrs.Open MyQuery, xlcon

This is not working (Error -2147217904 (80040e10)). And then:

MyQuery = "SELECT * FROM [" & currentDataFileName & ".csv] WHERE MyField=10"
xlrs.Open MyQuery, xlcon

This is also not working (same error).

Of course, I would like to use a combination of both things (select only some fields from the file and use some "where clauses" for different fields.

Any help would be appretiated.

FunThomas · Accepted Answer · 2019-01-14 09:28:10Z

2

I strongly assume that you don't have a field MyField in your data - maybe it's just a misspelling.

Change the query back to select * to receive all data and put some lines to dump the fieldnames. Note that the field-collection of a Recordset is 0-based. Btw: with ADO, you don't need to issue a MoveFirst:

xlrs.Open MyQuery, xlcon
Dim i As Integer
nextRow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
' Dump fieldnames
For i = 1 To xlrs.Fields.Count
    Worksheets("Sheet1").Cells(nextRow, i) = xlrs.Fields(i - 1).Name
Next i
nextRow = nextRow + 1
Worksheets("Sheet1").Cells(nextRow, 1).CopyFromRecordset xlrs

answered Jan 14, 2019 at 9:28

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

6 Comments

Javi Over a year ago

Believe me I have a MyField field in the data.

FunThomas Over a year ago

And you get all fields of the CSV separated into single cells?

Javi Over a year ago

No, I get them all in first column, separated with ";".

Javi Over a year ago

Maybe is it the reason? The csv uses ";" as the separator and not "," ??

FunThomas Over a year ago

Yepp, that's your problem. Easiest would be if you could change your CSV-file. If not, you will probably have to tinker around using a schema.ini file. See discussion at stackoverflow.com/q/3109360/7599798 (it's valid also for VBA)

|

Collectives™ on Stack Overflow

Different SQL queries in Excel VBA for csv files using ADODB connection

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related