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

 

[actjfc]

Thank you! It is working now! It copies the matching 2500 entire record in one row in the active workbook. I know that when I code MS Access I can asign the other columns within the same matching record using expresions like:

Sheet1.Range("B10").Value = .Fields("Zip_Code")

How can I do this in this CSV situation?

Thanks!

 

[Norie]

In exactly the same way.

 

[actjfc]

I received a response of "invalid or unqualified reference" at .Fields in Sheet1.Range("B10").Value = .Fields("Zip_Code"). Thanks!

 

[Norie]

Unless you have that code within a With...End With structure that references the recordset you would have to precede .Fields with a reference to the recordset.

With mrs
    Sheet1.Range("B10").Value = .Fields("Zip_Code")
End With

' or 

Sheet1.Range("B10").Value = mrs.Fields("Zip_Code")

 

[actjfc]

Thanks! It was the final detail to make it work. It is amazing!

 

[actjfc]

A final question came up for me. How do I detect if there is no matching records? or if there only one matching record in the data set? Thanks!

 

[Norie]

Check the EOF of the recordset?

 

[John_w]

mrs.RecordCount returns -1 for .csv files, so can't be used.

Instead, this should give the number of records:

    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row - 1

 

[actjfc]

Thank you! It was driving me crazy! I kept getting mrs.RecordCount= -1.

I cannot not dump the CSV table in the workbook to count them as you suggested. I need to identify if the Customer ID was found or not inside the CSV file. So, I can use an IF statement to continue. I wanted to make something like

If mrs.RecordCount= 1 then 'Found XXXX else 'Not found yyyy End if.

Thanks!

 

[John_w]

I cannot not dump the CSV table in the workbook to count them as you suggested. I need to identify if the Customer ID was found or not inside the CSV file. So, I can use an IF statement to continue.

After the mrs.open:

    If Not mrs.EOF Then
        MsgBox "Found"
    Else
        MsgBox "Not found"
    End If