How to Merge CSV Files in Command Prompt, PowerShell or Bash

You have a folder of monthly exports and want one file. The top answers say to type copy *.csv combined.csv, or pipe Import-Csv into Export-Csv, or run cat. All of them finish without an error, and most of them damage the result in a way you will not see until someone counts the rows.

So rather than repeat them, we ran each one on Windows 11 — Command Prompt, Windows PowerShell 5.1 and Git Bash — and parsed the output. This guide gives the results, then the versions that hold up.

The Test Files

Three tiny files, each with the header id,name, built to contain the things real exports contain:

A correct merge has one header and five rows. For speed, we also merged three parts of a real-sized file: 99.4 MB, 2.5 million rows.

Command Prompt: copy

copy *.csv combined.csv took well under a second and produced a file that opens. Inside it:

• The header appears three times, twice in the middle of the data.
• Two lines are glued together. b.csv has no line break after its last row, so it ran straight into the next file’s header: 4,"Smith, J"id,name. One row is corrupted and one header is buried inside it.
• An extra byte sits at the very end. In its default text mode, copy appended an end-of-file marker (Ctrl+Z, byte 0x1A). Python’s CSV reader returned it as a final row with one invisible character in it.

copy /b *.csv combined.csv treats the files as binary and does not add the marker. Nothing else improves. It also carries byte order marks into the middle of the file: when a later file began with one, three invisible bytes landed in front of that file’s header, turning it into a column name that looks exactly like id and does not match it. One point in its favour: running the command a second time did not pull combined.csv into itself.

copy /b merged the 99.4 MB set in about a second. It is the right tool only for files with no header row that all end in a line break.

PowerShell: Import-Csv and Export-Csv

This is the one-liner most answers give:

Get-ChildItem -Filter *.csv | ForEach-Object { Import-Csv $_.FullName } | Export-Csv merged.csv -NoTypeInformation

It is a real improvement, because Import-Csv parses the files instead of copying bytes. Once we fixed the problems below, the header appeared once, the row with no final line break came through whole, and the line break inside c.csv’s value survived. But run as written, it has four problems.

1. It Reads Its Own Output

merged.csv is created in the folder being listed, so it becomes one of the inputs, and Import-Csv keeps reading rows that Export-Csv is still appending. Our three files, 85 bytes in total, became a 2.8 MB file in ten seconds, and the first time we ran it the file passed 150 MB before we stopped it. Wrapping Get-ChildItem in parentheses, another common tip, only fixes the first run: run the command again and the old merged.csv is in the list, and it loops again. Keep the input files in their own folder and write the result outside it.

2. Windows PowerShell Writes ASCII

Zoë came out as Zo?. Microsoft’s reference for Export-Csv in Windows PowerShell 5.1, the version built into Windows, is plain about it: “The default value is ASCII.” The question mark is written into the file, so the letter cannot be recovered later. Adding -Encoding UTF8 fixed it and also wrote a byte order mark, which is what Excel needs to show accents correctly. PowerShell 7 defaults to UTF-8 without a byte order mark.

3. Columns Come from the First File Only

Microsoft’s documentation says Export-Csv “organizes the file based on the properties of the first object that you submit”, and “if the remaining objects have additional properties, those property values are not included in the file.” We merged a file with id,name and then one with name,id,email. The email column disappeared, with no warning. Listing the columns yourself with Select-Object fixes it, as in the second example below.

4. A #TYPE Line at the Top

Without -NoTypeInformation, Windows PowerShell 5.1 made the first line of the file #TYPE System.Management.Automation.PSCustomObject, which any other program takes as the header. PowerShell 6 and later no longer write it.

The Versions That Worked

With the CSV files in a folder called exports, run this from the folder above it:

Get-ChildItem .\exports -Filter *.csv |
  ForEach-Object { Import-Csv $_.FullName } |
  Export-Csv .\merged.csv -NoTypeInformation -Encoding UTF8

When the files do not all have the same columns, name every column you want and, if it helps, record where each row came from:

Get-ChildItem .\exports -Filter *.csv | ForEach-Object {
  $file = $_.Name
  Import-Csv $_.FullName | Select-Object *, @{ Name = 'source_file'; Expression = { $file } }
} | Select-Object id, name, email, source_file |
  Export-Csv .\merged.csv -NoTypeInformation -Encoding UTF8

Both put quotes around every value, "1","Ann". That is valid CSV and any CSV reader handles it; in PowerShell 7, -UseQuotes AsNeeded writes quotes only where they are required.

The cost is speed. Import-Csv turns every row into an object, and on the 99.4 MB set the first version took more than two minutes. The quotes also made the result bigger than its inputs: 127.0 MB.

macOS and Linux: awk, Not cat

cat *.csv > merged.csv is the same byte-for-byte join as copy /b, with the same repeated headers and glued rows. A loop of tail -n +2 commands, the usual way to drop the headers, fixed the headers but still produced 4,"Smith, J"5,"line one, because tail copies the missing line break as faithfully as everything else. This one worked:

awk 'FNR==1 && NR!=1 {next} {print}' exports/*.csv > merged.csv

FNR is the line number within the current file and NR the line number overall, so the rule skips line 1 of every file except the first. print ends each line with a line break, so a file with no final line break cannot run into the next one. The line break inside c.csv’s value was kept, and so was Zoë, because awk never changes the encoding. It merged the 99.4 MB set in one to three seconds.

Keep the output out of the folder you are reading. We ran the same command as awk … *.csv > merged.csv inside the folder of exports. The first run was fine; the second run picked up the previous merged.csv as an input, read back the rows it was writing, and had produced a 33.5 GB file by the time we stopped it — the PowerShell problem again, only faster.

Its other limit is the same as copy’s: it matches columns by position, so every file needs the same columns in the same order. On Windows it runs in Git Bash, where the result came out with Unix line endings; Excel reads those without complaint.

Python: When the Columns Differ

When files have columns in a different order, or some files have extra columns, you need something that reads each header. This script uses only Python’s standard library:

import csv, glob

files = sorted(glob.glob("exports/*.csv"))
columns = []
for name in files:
    with open(name, encoding="utf-8-sig", newline="") as f:
        for col in next(csv.reader(f)):
            if col not in columns:
                columns.append(col)

with open("merged.csv", "w", encoding="utf-8", newline="") as out:
    writer = csv.DictWriter(out, fieldnames=columns)
    writer.writeheader()
    for name in files:
        with open(name, encoding="utf-8-sig", newline="") as f:
            writer.writerows(csv.DictReader(f))

It first collects every column name, in the order it first appears, then writes each row under the right heading and leaves missing values empty. utf-8-sig removes a byte order mark if a file has one, so id matches id. newline="" is what keeps line breaks inside quoted values intact. We added a fourth test file with a byte order mark and the columns name,id,email; the result was one header, id,name,email, and six correct rows.

It reads one row at a time, so file size is limited by disk space, not memory. On the 99.4 MB set it took anywhere from 21 seconds to five minutes, depending on what else the laptop was doing. If the result is going to be opened in Excel by double-clicking, change the output encoding to utf-8-sig so accents display correctly.

Which Command to Use

Timings are from a Windows 11 laptop that was busy with other work, so they varied from run to run, and yours will differ. The order never changed: byte copying took about a second, awk a few, and the commands that parse every row took far longer.

Without the Command Line

The CSV merge tool does what the Python script does: it reads every header, lines columns up by name, writes one header, and can add a source_file column. It runs in your browser and reads the files from your disk, so nothing is uploaded, and the merged file can be larger than Excel could open. For why lining columns up by position goes wrong, and how to check a merge afterwards, see merging CSV files that have different columns. If the merged file turns out to be too big to work with, splitting it from the command line covers the reverse job.