Use first line of Query as Column Headers

[AndrewDotto]

Hi Guys,

Here's the background.

I get a CSV mailed to me, it's monstrous. I have a DB that i import this CSV into, (after alot of manipulation), and then run my queries to get what i need.

I've got some code that will autoimport this CSV into a table and then i can use nested queries to clean the data up, but it's hard to auto append the data to the final table because the column headers i need are not the first fields of the CSV, when autoloaded into a table, it's always row 4.

So, what i'm wanting is to assign a row of data as the column headers for a query so i can then append the data into a final table for my querying without having to manually assign the columns each time.

a little help??

 

[namliam]

IIRC you can tell access to skip rows, or import a specific range starting at row 4... headers will come from there.

Though this may be excel related.
For a csv, if need be manipulate the file using the Filesystemobject before importing it?

 

[Ranman256]

1. If you link the file as an attached table, you wont need headers, build an append query to do the 'import', the query assigns field1 to NAME, field2 to ADDRESS.
Then you only save the text file, then run the import query.
or
2. you have a 'header' file with only the headers in it. Have an old DOS .BAT file that concats it with the main file to the 'import' file.
copy header1.txt + data.csv importFile.txt

this will copy header to the new file ,then append data.csv to the new file, thus putting header and data together in the final filename.

 

[arnelgp]

so maybe before autoimporting your csv file, manipulate the csv so that the Header goes to 1 row:

Sub subReadTextFile(ByVal strFileName As String)

    Dim strTextLine As String
    Dim strFinalText As String
    Dim strNewFileName As String
    Dim iFile As Integer
    Dim lngLine As Long
    
    iFile = FreeFile
    Open strFileName For Input As #iFile
    Do Until EOF(1)
        lngLine = lngLine + 1
        Line Input #iFile, strTextLine
        ' if we are at line 4 thats the Header
        If lngLine = 4 Then
            Exit Do
        End If
    Loop
    strFinalText = strFinalText & strTextLine
    Close #iFile
    lngLine = 0
    iFile = FreeFile
    Open strFileName For Input As #iFile
    Do Until EOF(1)
        lngLine = lngLine + 1
        Line Input #iFile, strTextLine
        ' read all lines except line 4 (Header)
        If lngLine <> 4 Then
            strFinalText = strFinalText & vbCrLf & strTextLine
        End If
    Loop
    Close #iFile
    'save to new file
    strNewFileName = Replace(strFileName, ".csv", "") & "_NEW.csv"
    Call subSaveStream(strNewFileName, strFinalText)
    strFinalText = ""
End Sub

Public Sub subSaveStream(ByVal sFileName As String, ByVal sText As String)

    Dim fso As scripting.FileSystemObject
    Dim TxtStream As scripting.TextStream
    Set fso = New scripting.FileSystemObject
    Set TxtStream = fso.CreateTextFile(sFileName, True)
    With TxtStream
        .Write sText
        .Close
    End With
    Set TxtStream = Nothing
    Set fso = Nothing
End Sub