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]

Thanks, now it is working fine! Thanks to all! There are several procedures that may do the same we did here. Like ADO, DAO, OLE DB, and ODBC. To work with this workbook, the user has to add the reference to Microsoft Activex Data Objects 2.8 Library. Is there a way to do the same without the need to check any Data Objects Reference. Also, I wonder if there any difference in how fast the code runs? What would be the difference in time? Is it worthy? In my case, it runs in about 5 seconds reading a test table with 40000 records with 20 fields. My challenge is that the end user may need to run it three or four times anytime uses the final workbook, so the time adds up. Morevoer, the user has to manually install the Data Objects Reference, only once, but they are not knowlegeable about it. Is there a way to install it automatically? Again, thanks for your help!

 

[Norie]

You can use late-binding.

Replace this code,

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

with this

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

    Set Conn = CreateObject("ADODB.Connection")
    Set mrs = CreateObject("ADODB.Recordset")

 

[actjfc]

Thanks, Norie! It works perfectly! No need to check a reference. The speed is the same. To decide what code we should use, I need to present several ways to do the same. Is there any other way to tweak the code to make it "different" just like the way you did it? Even if it needs another Library Reference, I need to have several ways to do it regardless, preferable without checking references, but all suggestions are welcome!

I am searching now how to do the same using:

Set DB = OpenDatabase(????)
Set RS = DB.OpenRecordset("SELECT * FROM WHERE ", dbOpenDynaset)

I am lost here!

Thanks!