Concatenate 3 rows into single header row

[kestefon]

A third-party application I use exports data with 3 header rows. I usually have to go into Excel and concatenate the rows into a single header row, then export the file to Access to apply queries. I want to minimize the steps in this process, so I'm wondering if it's possible to do this concatenation in Access (with or without VBA).

Here's a screenshot:

[June7]

It's certainly possible and expect VBA will be required. Want to provide sample data?

[kestefon]

Yes, definitely. Here's a tab-delimited text file.


Sample_Data.txt

[June7]

The image didn't show in the original post first time I read it.

I see options:

1. Use wizard to import the CSV to table, the first line is field names, unfortunately Name and Time are reserved words and reserved words should not be used as field names.
Then do something with the additional 2 lines of the header (delete?).
Problem with this is numeric data will import as text because of the 2 extra header lines.

2. Open the CSV file as an object in VBA and write each line of the file to table.

[kestefon]

Option 2 seems like it'll probably be best for this. I'll give it a try. Thanks!

[kestefon]

Can you give me a hand with concatenating the rows? (see bold part of code) As it's written now, it would just write the 3 separate rows to a CSV file. I'd like the write the rows such that they're all concatenated into one single header rows.

Sub ConcatenateRows()
Dim fs As Object ''FileSystemObject
Dim tsIn As Object, tsOut As Object ''TextStream
Dim sFileIn As String, sFileOut As String
Dim aryFile As Variant, sTmp As Variant, i As Long


    sFileIn = "C:\documents]Data.csv"
    sFileOut = "C:\documents\Data_Formatted.csv"


    Set fs = CreateObject("Scripting.FileSystemObject")
    Set tsIn = fs.OpenTextFile(sFileIn, 1) ''ForReading


    sTmp = tsIn.ReadAll


    Set tsOut = fs.CreateTextFile(sFileOut, True) ''Overwrite
    aryFile = Split(sTmp, vbCrLf)


    'write first 3 rows to new csv file
   For i = 0 To 2
        tsOut.WriteLine aryFile(i)
      Next



    tsOut.Close


    DoCmd.TransferText acImportDelim, , "tblFormattedData", sFileOut, False
End Sub

[June7]

Why write out to a textfile and then import? Could just write data to table.

Are you saying the 2nd and 3rd rows should just be more fields? Try:

For i = 0 to 1
strLine = aryFile(i) & " "
Next
tsOut.WriteLine Trim(strLine)

[kestefon]

Here's an example of what I'm trying to do with the three rows. If you visualize the data in Excel (i.e. see the original screenshot), I would basically be concatenating each cell with the cells directly below it.

ConcatenatedRows.txt

[June7]

That does complicate. Will these header rows always be the same configuration?

I envision opening each row to its own array (or all 3 to a single 2-dimensional) and then concatenating the corresponding elements. The trick is determining which elements if the arrays are not the same size.

[kestefon]

Yeah, the headers will always have this configuration. When you say that the arrays aren't the same size, are you referring to the blank cells on the 2nd and 3rd rows (for instance, under MONTH, CLI, etc.?) Wouldn't I be able to include those in the arrays as blanks?

[June7]

Only if there is a delimiter between each that defines the columns. Since there appears to be a tab delimiter then yes, think the array elements will be established with Null or empty string. I've never had to try this.

For x = 1 to UBound(ary1)
strLine = strLine & ary1(x) & ary2(x) & ary3(x) & ","
Next
strLine = Left(strLine, Len(strLine)-1)

However, problem is the 2nd and 3rd rows aren't as long as row 1, so ary1 will still be larger and the above code is still too simple.

[kestefon]

Thanks. One more question if you don't mind...how would I modify that original code to write directly to a table?

[June7]

I edited my previous post while you posted.

Those are field names, right? Would the table already be created with those fields? Then the issue of concatenating field names would be irrelevant and code would just skip the first 3 rows and start writing data to table or more efficient might be to write to a recordset object first.

[kestefon]

By the way, I ended up doing this (see below). Basically, since the header labels shouldn't change, I just split the text file by its delimiter (after replacing the CrLf with a comma) and used the numerical location of each header element in my array. Thanks again for your help!

Sub ConcatenateRows()Dim fs As Object ''FileSystemObject
Dim tsIn As Object, tsOut As Object ''TextStream
Dim sFileIn As String, sFileOut As String
Dim aryFile As Variant, sTmp2 As Variant, rowA As Variant, rowB As Variant, rowC As Variant, sTmp As Variant, i As Long, j As Long, k As Long, otherRows As Variant


    sFileIn = "C:\docs\file.csv"
    sFileOut = "C:\docs\file_formatted.csv"


    Set fs = CreateObject("Scripting.FileSystemObject")
    Set tsIn = fs.OpenTextFile(sFileIn, 1) ''ForReading


    sTmp = tsIn.ReadAll


    Set tsOut = fs.CreateTextFile(sFileOut, True) ''Overwrite
    sTmp2 = Replace(sTmp, vbCrLf, ",")
    aryFile = Split(sTmp2, ",")
    otherRows = Split(sTmp, vbCrLf)

For i = 0 To 23



    If i < 23 Then
        tsOut.Write aryFile(i) & aryFile(i + 24) & aryFile(i + 48) & ","
    ElseIf i = 23 Then
        tsOut.Write aryFile(i) & aryFile(i + 24) & aryFile(i + 48) & vbCrLf
    End If


Next i

For j = 3 To UBound(otherRows)
tsOut.WriteLine otherRows(j)
Next j





 tsOut.Close




DoCmd.TransferText acImportDelim, , "NewCSV", sFileOut, False


End Sub