Merge CSV files with different column orders using only Python's standard library

Merging monthly CSV exports sounds trivial until the files have different column orders, extra columns, or a BOM from Excel. Here is a short standard-library script that handles all three and records where each row came from.

The script

import csv, sys, glob

def merge(paths, out):
    rows, cols = [], []
    for p in paths:
        with open(p, newline="", encoding="utf-8-sig") as f:
            r = csv.DictReader(f)
            for c in r.fieldnames or []:
                if c not in cols:
                    cols.append(c)
            for row in r:
                row["source_file"] = p
                rows.append(row)
    cols.append("source_file")
    with open(out, "w", newline="", encoding="utf-8") as f:
        w = csv.DictWriter(f, fieldnames=cols, restval="")
        w.writeheader()
        w.writerows(rows)
    return len(rows), len(cols)

if __name__ == "__main__":
    out = sys.argv[1]
    paths = sorted(p for a in sys.argv[2:] for p in glob.glob(a))
    n, c = merge(paths, out)
    print(f"{n} rows, {c} columns -> {out}")

Why it works

• csv.DictReader matches values by header name, so column order does not matter.
• utf-8-sig strips the byte order mark that Excel adds, which would otherwise turn the first header into \ufeffsku.
• The output columns are the union of all headers, in the order first seen. restval="" fills the gaps.
• A source_file column lets you trace a bad row back to its file.

Quick test

With jan.csv (sku,price) and feb.csv (price,sku,color, saved with a BOM):

$ python merge_csv.py all.csv jan.csv feb.csv
3 rows, 4 columns -> all.csv

price,sku,color,source_file
7,B1,red,feb.csv
5,A1,,jan.csv
6,A2,,jan.csv

Limits

It loads everything into memory, so it suits files up to a few hundred MB, not huge exports. It also assumes every file uses commas and UTF-8; for semicolon-separated exports, pass delimiter=";" to DictReader.