Combine CSV files with Excel VBA

Question

I have some csv files in one folder. They all contain 3 specific columns. The number of total columns and the order may vary.

I want to concatenate all 3 columns with an underscore and write them in a single column in the worksheet that is running the code.

Here is what I have so far:

 Option Explicit

Sub test()

Dim i As Long
Dim LastRow As Long
Dim Columns()

Columns = Array("Column1", "Column2", "Column3")

'Find Columns by Name
For i = 0 To 2
    Columns(i) = Rows(1).Find(What:=Columns(i), LookIn:=xlValues, LookAt:=xlWhole, _
    MatchCase:=False, SearchFormat:=False).Column
Next i

'Debug.Print Columns(0)
'Debug.Print Columns(1)
'Debug.Print Columns(2)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = 2 To LastRow
    Cells(i, 1) = Cells(i, Columns(0)) & "_" & Cells(i, Columns(1)) & "_" & Cells(i, Columns(2))
Next i

End Sub

As you can see, this does what I want, but only for the active sheet. I actually want to loop through all csv files in the same folder as the active sheet and write the results in the first sheet, first column of the sheet running the code (which is not a csv itself obviously). How can I do this?

thanks!

Answer 1

It depends how you are naming the worksheets you create from the CSV files. You could add all the worksheets to a collection and use a For...Each loop to execute the entire search and concatenate procedure within that loop. Note that you'd have to explicitly define the first sheet name as this won't change through successive loops:

Option Explicit

Sub test()

Dim i As Long
Dim LastRow As Long
Dim Columns()
Dim frontSheet as Worksheet
Dim wSheets as New Collection
Dim ws as Worksheet

Set frontSheet = Sheets("name of front sheet")

'Add all your CSV sheets to wSheets using the .Add() method.
For Each ws in wSheets

    Columns = Array("Column1", "Column2", "Column3")

    'Find Columns by Name
    For i = 0 To 2
        Columns(i) = ws.Rows(1).Find(What:=Columns(i), LookIn:=xlValues, LookAt:=xlWhole, _
        MatchCase:=False, SearchFormat:=False).Column
    Next i

    'Debug.Print Columns(0)
    'Debug.Print Columns(1)
    'Debug.Print Columns(2)

    LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row


    For i = 2 To LastRow
        frontsheet.Cells(i, 1) = ws.Cells(i, Columns(0)) & "_" & ws.Cells(i, Columns(1)) & "_" & ws.Cells(i, Columns(2))
    Next i

Next ws

End Sub

It's often slow and labourious to open CSV files in excel but VBA can read them as text files using a TextStream. Furthermore, file scripting objects let you work with files and directories directly. Something like this might be a better approach if you don't need to keep the files in a worksheet afterwards:

Sub SearchFoldersForCSV()

Dim fso As Object
Dim fld As Object
Dim file As Object
Dim ts As Object
Dim strPath As String
Dim lineNumber As Integer
Dim lineArray() As String
Dim cols() As Integer
Dim i As Integer
Dim frontSheet As Worksheet
Dim frontSheetRow As Integer
Dim concatString As String

Set frontSheet = Sheets("name of front sheet")
frontSheetRow = 1

strPath = "C:\where-im-searching\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strPath)

For Each file In fld.Files

    If (Right(file.Name, 3) = "csv") Then

        Debug.Print file.Name

        Set ts = file.OpenAsTextStream()

        lineNumber = 0

        Do While Not ts.AtEndOfStream

            lineNumber = lineNumber + 1
            lineArray = Split(ts.ReadLine, ",")

            If (lineNumber = 1) Then

                'We are at the first line of the .CSV so
                'find index in lineArray of columns of interest

                'Add extra ElseIf as required

                For i = LBound(lineArray) To UBound(lineArray)
                    If lineArray(i) = "Column 1" Then
                        cols(1) = i
                    ElseIf lineArray(i) = "Column 2" Then
                        cols(2) = i
                    ElseIf lineArray(i) = "Column 3" Then
                        cols(3) = i
                    End If
                Next i

            Else

                'Read and store the column of interest from this
                'row by reading the lineArray indices found above.

                concatString = ""
                For i = LBound(cols) To UBound(cols)
                    concatString = concatString & lineArray(i) & "_"
                Next i

                concatString = Left(concatString, Len(concatString) - 1)

                frontSheet.Cells(frontSheetRow, 1).Value = concatString
                frontSheetRow = frontSheetRow + 1

            End If

        Loop

        ts.Close

    End If

Next file

End Sub

You can find more information on FileSystemObject and TextStream here.

Answer 2

This is a code that will loop through a folder

Sub Button1_Click()
    Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook

    Set Wb = ThisWorkbook
    'change the address to suite
    MyDir = "C:\WorkBookLoop\"
    MyFile = Dir(MyDir & "*.xls")    'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)

        'do something here




        MyFile = Dir()
    Loop

End Sub