How to merge CSV files in Python

Concatenating CSV files with cat or copy repeats the header and silently misaligns columns when files list them in a different order. This script matches columns by name and records which file each row came from.

1. The script

Save as merge_csv.py:

import csv, sys
from pathlib import Path

def merge(paths, out, source=True):
    columns, index, rows = [], {}, []
    for path in paths:
        with open(path, newline='', encoding='utf-8-sig') as f:
            reader = csv.reader(f)
            header = next(reader, None)
            if header is None:
                continue
            slots = []
            for h in header:
                key = h.strip().lower()
                if key not in index:
                    index[key] = len(columns)
                    columns.append(h.strip())
                slots.append(index[key])
            for r in reader:
                if any(c.strip() for c in r):
                    row = {slots[i]: c for i, c in enumerate(r) if i < len(slots)}
                    rows.append((Path(path).name, row))
    with open(out, 'w', newline='', encoding='utf-8') as f:
        w = csv.writer(f)
        w.writerow((['source_file'] if source else []) + columns)
        for name, row in rows:
            w.writerow(([name] if source else []) + [row.get(i, '') for i in range(len(columns))])
    return len(rows), len(columns)

paths = sys.argv[2:]
n, c = merge(paths, sys.argv[1])
print(f'{len(paths)} files -> {n} rows x {c} columns in {sys.argv[1]}')

2. Run it

python3 merge_csv.py merged.csv data/*.csv

On our five test files it printed 5 files -> 164 rows x 59 columns in merged.csv: the 56 country-code columns, the extra column from one file and the 2 columns of the unrelated file, plus source_file in front.

3. What it does

Columns matched by name, ignoring case and surrounding spaces: a file whose columns are in reverse order and whose header says ISO3166-1-ALPHA-3 lines up with the others. The first spelling seen is kept.
Union of columns: a column that only some files have is added; rows from the other files get an empty cell.
source_file: the first column records the file each row came from. Call merge(paths, out, source=False) to leave it out.
Excel files: utf-8-sig strips the BOM; blank rows and empty files are skipped; quoted line breaks stay inside their row.

4. Limits

All rows are held in memory until the header is known; for many GB of input, read every header first, then stream the rows.
Comma-separated only; pass delimiter=';' to csv.reader for European Excel files. Cells beyond a file's header are dropped.

5. No code

The free merge CSV files tool does the same in your browser, with the same matching rules, and exports CSV or .xlsx. Nothing is uploaded. To go the other way, use the split CSV tool.

Frequently asked questions

Can I just use cat or head/tail?

Only if every file has the same header in the same order: head -n 1 first.csv > merged.csv, then tail -q -n +2 *.csv >> merged.csv. If a file is missing its final newline, its last row is glued to the next file's first row.

How do I do it with pandas?

pd.concat([pd.read_csv(p, dtype=str, keep_default_na=False) for p in paths]).to_csv('merged.csv', index=False) also matches columns by name, but exact name only: 'Email' and 'email ' become two columns. dtype=str keeps ids like 02134 and codes like NA as written.

What if the same column has different names?

Rename it in the header of the odd file first (for example Email Address to email); the script then lines it up with the others.

Written with AI assistance. The script was run on 2026-10-01 with Python 3 standard library only, on five files: four parts of the open country-codes dataset (one with its 56 columns in reverse order, a header in upper case with spaces, an extra column and an Excel BOM) and a two-column file with a quoted line break. The output was compared cell for cell with the free merge CSV tool on this site (0 mismatches), and the reordered file's values were checked against the original. PlainTables is our extension; the other methods are free and built into Excel, Google Sheets and pandas.