How to Merge or Combine all CSV or TXT files in a folder in one file or worksheet

By admin On Apr 22, 2020

Spread the love

These are very easy methods of Merge all CSV or TXT files in a folder in one worksheet. especially the method in example 1 is my favorite.

Example 1

Merge all data from the CSV files in a folder into a text file

Note: with a few small changes you can also use this for text files. Replace *.csv for *.txt.

1) Press Windows Start Button and open Run box
2) Type cmd in Run and hit enter (“command” in Win 98)
3) Go to the folder with the CSV files (for help how to do that enter “help cd”)
4) Type copy *.csv all.txt and hit enter to copy all data in the files into all.txt, this file will be created in the folder.
5) Type exit and hit enter to close the DOS window

Now we must import the text file all.txt
into Excel.

1) Open Excel
2) When you use File Open to open all.txt the Text Import Wizard will help you import the file
3) Choose Delimited
4) Next
5) Check Comma
6) Finish

Example 2

This code will ask you to browse to the folder with the CSV files and
after you click OK in this dialog
it merges all data into a txt file and
then import and save it into a Excel file for you.

Copy the code
below into a normal module of a workbook :

Alt-F11
Insert>Module
Paste the macro
Alt q to go back to Excel
Alt F8 to open your macro
list
Select Merge_CSV_Files and press Run

There is no need to change anything in the code example for csv files to
test it.
But read the Tips below the macro if you not get the result you
want.

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
    
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
#Else
    Private Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
    
    Private Declare Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
#End If


Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_CSV_Files()
    Dim BatFileName As String
    Dim TXTFileName As String
    Dim XLSFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim DefPath As String
    Dim Wb As Workbook
    Dim oApp As Object
    Dim oFolder
    Dim foldername

    'Create two temporary file names
    BatFileName = Environ("Temp") & _
            "\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
    TXTFileName = Environ("Temp") & _
            "\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

    'Folder where you want to save the Excel file
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    'Set the extension and file format
    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007 or higher
        FileExtStr = ".xlsx": FileFormatNum = 51
        'If you want to save as xls(97-2003 format) in 2007 use
        'FileExtStr = ".xls": FileFormatNum = 56
    End If

    'Name of the Excel file with a date/time stamp
    XLSFileName = DefPath & "MasterCSV " & _
                  Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

    'Browse to the folder with CSV files
    Set oApp = CreateObject("Shell.Application")
    Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
    If Not oFolder Is Nothing Then
        foldername = oFolder.Self.Path
        If Right(foldername, 1) <> "\" Then
            foldername = foldername & "\"
        End If

        'Create the bat file
        Open BatFileName For Output As #1
        Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
                & Chr(34) & " " & TXTFileName
        Close #1

        'Run the Bat file to collect all data from the CSV files into a TXT file
        ShellAndWait BatFileName, 0
        If Dir(TXTFileName) = "" Then
            MsgBox "There are no csv files in this folder"
            Kill BatFileName
            Exit Sub
        End If

        'Open the TXT file in Excel
        Application.ScreenUpdating = False
        Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
                :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
                Space:=False, Other:=False

        'Save text file as a Excel file
        Set Wb = ActiveWorkbook
        Application.DisplayAlerts = False
        Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
        Application.DisplayAlerts = True

        Wb.Close savechanges:=False
        MsgBox "You find the Excel file here: " & vbNewLine & XLSFileName

        'Delete the bat and text file you temporary used
        Kill BatFileName
        Kill TXTFileName

        Application.ScreenUpdating = True
    End If
End Sub

Format or skip columns

If you want to format or skip a
columns then you see you can add FieldInfo in OpenText like
FieldInfo:=Array(Array(1, 2), Array(3, 4))

This example changes the format of column 1 and 3 (column number, format number)
This are the format numbers

1: General
2:         Text
3:         Month-Day-Year
4:         Day-Month-Year
5:         Year-Month-Day
6:         Month-Year-Day
7:         Day-Year-Month
8:         Year-Day-Month
9:         Skip column

Merge txt files instead of csv files

Replace

Print #1, “Copy ” & Chr(34) & foldername & “*.csv” _

With

Print #1, “Copy ” & Chr(34) & foldername & “*.txt” _

If you use it for txt files then you can change the delimiter or maybe you want to use FixedWidth.
The best thing you can do is to record a macro when you import one txt file manual.
Then look at the recorded code and add the code lines to Workbooks.OpenText .