VBA ADO to Read a CSV file

[actjfc]

Excel friends,

I am trying to read a CSV table using the following VBA code found in: https://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/

I need help in three areas:

1) Can I read an external CSV table using this code? If so, how to setup a CSV file, InputData.csv, not an xls

2) Assuming I can read it, what would be the definition of sSQLSting? I do not understand how to setup the SQL instruction to read just one matching field. Something like: "SELECT * FROM InputData.csv WHERE Customer_ID = ('" & sCID & "') ". I am not certain how the Fieldnames are read or defined in a CSV file (the headings?), if possible.

3) After the second step is done, then I will assign some fields to specific locations in the workbook, like, Sheet1.Range("F3").Value = .Fields("ZIP_Code").Value. Is it the right code to assign a cell value from a CSV file? I do not need to load the whole data into an array, I just need one marching field, and I need to develop this option using VBA and ADO, not standard Excel functions.

I added a reference to Microsoft Activex Data Objects 2.8 Library.

Thanks for any help figuring this out.

Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String

DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below
'DBPath ="C:\InputData.xlsx"

'Using MSDASQL Provider
'sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

'Using Microsoft.Jet.OLEDB Provider - If you get an issue with Jet OLEDN Provider try MSDASQL Provider (above statement)
sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    
Conn.Open sconnect
    sSQLSting = "SELECT * From [DataSheet$]" ' Your SQL Statemnt (Table Name= Sheet Name=[DataSheet$])
    
    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        'ReturnArray = mrs.GetRows
                ''OR''
        '=>Paste the data into a sheet
        ActiveSheet.Range("A2").CopyFromRecordset mrs
    'Close Recordset
    mrs.Close

'Close Connection
Conn.Close

End Sub

 

[RoryA]

Yes, just specify a path rather than file name in the connection string, change the extended properties to:

Extended Properties='text;HDR=YES;FMT=Delimited'

and then replace the sheet name part with the name of the file.

 

[actjfc]

Thanks RoryA,

I have not worked with ADO before, so far this is what I did but it does not work, please take a look and let me know where my mistakes are located:

Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String

DBPath = "C:\ADOTEST\"

sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";Extended Properties='text;HDR=YES;FMT=Delimited'"";"

Conn.Open sconnect
    
    sSQLSting = "SELECT * From InputData.csv"   'Here I need to match WHERE Customer_ID = ('" & sCID & "') but let's test first the whole data set!
    
    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        'ReturnArray = mrs.GetRows
                ''OR''
        '=>Paste the data into a sheet
        ActiveSheet.Range("A2").CopyFromRecordset mrs
      
    'Close Recordset
    mrs.Close

Conn.Close

End Sub

Thanks!

 

[Norie]

How is the code not working?

 

[actjfc]

Here you go! I missed stating it in my prior post:

"Format of the initialization string does not conform to the OLE DB specification" Run Time Error -2147217805 (80040e73)

 

[John_w]

Try changing these two lines as shown:

    sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited';"
    sSQLSting = "SELECT * From InputData.csv Where [Customer_ID] = '" & sCID & "'"

 

[Norie]

There's a bit of a typo in the connection string, try this.

    sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";Extended Properties='text;HDR=YES;FMT=Delimited';"

 

[John_w]

Try changing these two lines as shown:

    sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited';"
    sSQLSting = "SELECT * From InputData.csv Where [Customer_ID] = '" & sCID & "'"

NB - path should be DBPath.

 

[actjfc]

My response crossed with yours. I will test it now. You are just answering my current questions in it!

I found my mistake, just by chance. I deleted these three characters ";" at the end of sconnect= line and now it works. It dumped the whole CSV table in the active workbook.

 

[Norie]

If Customer_ID is a numeric field you don't need the single quotes.

I am using this one to test: sSQLSting = "SELECT * From InputData.csv WHERE Customer_ID = " & 2500