How to merge CSV files into one

Excel opens small CSV files easily, but often there are many files of the same kind, such as daily exports, reports by region or keyword lists, and you need to merge them into one.

There are many ways to merge CSV files in general, but not that many with Excel. Let’s look at the main ones.

Combine CSV data with Power Query

Strictly speaking, this doesn’t merge files: it merges the data in them. The result is not a CSV, but an Excel table with a refreshable query. Still, the data really ends up in one file, which is often the actual goal (but not always).

• Put all the CSV files into one folder. Ideally, they have the same column structure and encoding (UTF-8).
• In a blank workbook, go to Data → Get Data → From File → From Folder.
• Select the folder and click Combine → Combine & Transform Data.
• In the dialog, choose the sample file, check the delimiter (comma or semicolon) and confirm.
• Power Query creates helper queries (Transform Sample File and others) and the main query that combines the files. Check the column data types and change them if needed.
• Click Home → Close & Load and choose where to put the result: a sheet or the Data Model.

When new CSV files arrive, drop them into the same folder and click Data → Refresh All. No more manual work. Keep in mind that a sheet holds at most 1,048,576 rows: for more, load the result to the Data Model only.

VBA macro: merge all CSV files in a folder into one

This procedure really merges several CSV files into one CSV. When you run it, a dialog asks for the folder with the files. All files with the .csv extension in the folder are merged into “!Merged.csv” in the same folder.

Unlike the simplest versions of this macro you’ll find on the web, it keeps the header row only from the first file, so the column names don’t repeat in the middle of the data. It assumes that all files have a header row and the same columns.

Sub MergeCSVFiles()
    Dim folder As String, fileName As String, outFile As String
    Dim outNum As Long, inNum As Long, lineText As String
    Dim fd As FileDialog, fileCount As Long, firstLine As Boolean
    Const OUT_NAME As String = "!Merged.csv"
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    If fd.Show <> -1 Then Exit Sub
    folder = fd.SelectedItems(1) & "\"
    outFile = folder & OUT_NAME
    outNum = FreeFile
    Open outFile For Output As #outNum
    fileName = Dir(folder & "*.csv")
    Do While fileName <> ""
        If fileName <> OUT_NAME Then
            inNum = FreeFile
            Open folder & fileName For Input As #inNum
            firstLine = True
            Do While Not EOF(inNum)
                Line Input #inNum, lineText
                ' keep the header only from the first file
                If Not (firstLine And fileCount > 0) Then Print #outNum, lineText
                firstLine = False
            Loop
            Close #inNum
            fileCount = fileCount + 1
        End If
        fileName = Dir
    Loop
    Close #outNum
    MsgBox "Files merged: " & fileCount, vbInformation
End Sub

Open the VBA editor (Alt+F11), insert a module (Insert → Module), paste the code and run it with F5 or the green Run button.

To keep the code for later, save the workbook as .xlsm or .xlsb, or store it in the Personal Macro Workbook, which is more convenient. It’s easy to adapt the code for TSV or TXT files: change “*.csv” to “*.tsv” or “*.txt”.

Merge CSV files with the command line

Without Excel at all, Windows can glue text files together. In File Explorer, open the folder, type cmd in the address bar and press Enter, then run:

copy *.csv merged.txt

Then rename merged.txt to .csv (the .txt extension prevents the command from including its own output file). The downside: the header of every file is copied too, and if a file doesn’t end with a line break, its last line sticks to the first line of the next file.

Merge CSV files in 2 clicks with !SEMTools

If you value your time, install the !SEMTools add-in: merging CSV files has been available there for free for years. The tool is on the !SEMTools tab in the Join/Split group of procedures.

It’s available in all versions of the add-in, Lite and Pro, and you don’t need to buy anything. Select the folder, and you get one CSV file:

The add-in has many other free tools. If you often work with text data, I recommend looking through all its features on this site, for example how to find duplicates in the merged data.

Common mistakes

• The merged file shows strange characters instead of letters. The files have different encodings. Re-save them in UTF-8 or set the encoding (File Origin) in Power Query.
• All data is in one column after opening. The delimiter is a semicolon, and your Excel expects a comma (or the other way round). Open the file via Data → From Text/CSV and choose the delimiter.
• Header rows appear in the middle of the data. The files were merged with their headers. Use the macro above or filter out the repeated header rows.
• Leading zeros disappeared from codes and ZIP codes. Excel converted them to numbers when opening. Import with Power Query and set the column type to Text.

Summary

I hope you found what you were looking for on this page and were able to merge your CSV files. Power Query is best when the files keep coming and you need a refreshable table, the VBA macro and the command line give you a real merged CSV, and the free !SEMTools tool does it in 2 clicks without any code.

You may also be interested in other join and combine tools.