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

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

Open in new window

hindersalivaAsked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi,

perhaps you might reference the CSV file directly as a table in an SQL statement.
SELECT A.* FROM
[Text;FMT=Delimited;HDR=YES;CharacterSet=437;DATABASE=c:\path].[Filename.csv]  as A

Open in new window

You may need to change some of the parameters like CharacterSet and HDR (header row)

fyi, CopyFromRecordset works with both ADO and DAO recordsets

*  I realize this is an Excel question -- you can test this directly in Access by setting SQL of a query to see if you get records; if you can link, then you can construct SQL for a recordset in code too

~~~
as Norie mentioned, your code does not write the headers...

here is another method, written by NateO, for writing headers:
  'Field Names - Stack into Array
  Dim fldArr() As String

   'more code

    With rs
      'Stack a String Array with the Field Names
      ReDim fldArr(0 To .Fields.Count - 1)
 
      For j = LBound(fldArr) To UBound(fldArr)
        Let fldArr(j) = .Fields(j).Name
      Next j

   'more code

           'Pass our dynamic Field String Array to A1,
           'stretched to the Right for number of Elements
            ws.Range("a1").Resize(, UBound(fldArr) + 1).Value = fldArr

Open in new window

If you are getting some headers but not others, I might question the format of the file ... perhaps better to open it and use some logic to fix what is wrong then save (or save copy), close, and link. You could loop too but with large files, it might be faster to link. There might also be a difference in being able to correctly parse (good article, Fabrice -- and it was funny too).

~~~
if the goal is to get the data into Excel, you can also do something like this:
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;c:\path\filename.csv", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "MyTablename"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Open in new window


have an awesome day,
crystal
0
 
hindersalivaAuthor Commented:
UPDATE:
I changed the Provider to Provider=Microsoft.ACE.OLEDB.12.0
Now I get a row for the Headers but only some headers come through.

I'm going to dig into this a little more. See if the CSV file is odd.
0
 
Fabrice LambertFabrice LambertCommented:
You might need to write a Schema.ini file, describing the CSV file.

There a great (and humorous) article about this on MSDN:
https://msdn.microsoft.com/en-us/library/ms974559.aspx
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Can you post a sample of the CSV with dummy data?
0
 
hindersalivaAuthor Commented:
I tried it with the attached file Friends.csv. Curiously when I put 'Headers=Yes' I get no headers. When I put 'Headers=No' I do get headers.

Same result with JET and ACE.

Can someone explain what's happening here please?
Friends.csv
0
 
hindersalivaAuthor Commented:
Norie, see my comment and attached file 'Friends.csv'
0
 
NorieVBA ExpertCommented:
Where's your code to get the headers?

I would expect to see something like this which puts the headers in row 1 and then the data starting in row 2.
Dim a As ADODB.Recordset
Dim fld As ADODB.Field
Dim rng As Range

    Set a = getData("Friends.csv")
    
    a.Open

    Set rng = Range("A1")
    
    For Each fld In a.Fields
        rng.Value = fld.Name
        Set rng = rng.Offset(, 1)
    Next fld

    Range("A2").CopyFromRecordset a

    a.Close

Open in new window


By the way, I know you close the recordset but you really should close the connection too.
0
 
hindersalivaAuthor Commented:
Norie, I can see I would need a separate 'GetHeaders' procedure. But I DO get some of the Headers, but not all. That's the mystery.
0
 
hindersalivaAuthor Commented:
Sorry it took so long to close this question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.