troubleshooting Question

Excel VBA - Get data from a closed CSV. Headers?

Avatar of hindersaliva
hindersalivaFlag for United Kingdom of Great Britain and Northern Ireland asked on
* ADOVBAMicrosoft ExcelMicrosoft Office
9 Comments3 Solutions939 ViewsLast Modified:
I'm experimenting with code I have found here
https://stackoverflow.com/questions/11635526/return-csv-file-as-recordset

It is supposed to get data from a CSV file, without opening it, and paste the data at "A1" on the activesheet. However, even though the connection specified that it has Headers (HDR=Yes) only some headers are coming through for me. The first row is row 1 of the data in the CSV.

Do I need to get the Headers separately? If so, how? (SEE UPDATE BELOW)
Thanks for any pointers.

Public Function getData(fileName As String) As ADODB.Recordset

    Dim path As String
    path = "C:\ExcelOMG Cookbook 2016\Closed CSV\"
    Dim cN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set cN = New ADODB.Connection
    Set RS = New ADODB.Recordset
'    cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
'                   "Data Source=" & path & ";" & _
'                   "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
                   
                   
    cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & path & ";" & _
                   "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
                   
                   
                   
    RS.ActiveConnection = cN
    RS.Source = "select * from " & fileName
    Set getData = RS

End Function


Sub GetDataFromCSV()
    Dim a As ADODB.Recordset
    Set a = getData("DataFile.csv")
    a.Open
    
    Range("A1").CopyFromRecordset a
    
    a.Close

End Sub
SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros