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

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

Select all 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

Select all 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

Select all Open in new window

have an awesome day,
crystal