Merging CSV files into one file without losing rows or mangling characters

Column order, header names, encodings and BOMs all differ between exports. The merge order that works, and the four failures that make rows disappear silently.

Merging CSV files looks like a one-liner until the first real folder. The failures are rarely loud — you get a file with fewer rows than you expected and no error, and you find out a month later.

The four silent failures

1. Encoding. Chinese exports from Excel on Windows are usually GBK, not UTF-8. Read one as UTF-8 and pandas either raises or, worse, with errors="ignore", quietly drops the bytes it cannot decode. Column names come back as garbage and the merge produces empty columns.
2. BOM. A UTF-8 file saved by Excel starts with EF BB BF. Read it as plain UTF-8 and the first column is named \ufeffid, not id. Your merge then has two different columns called "id" and half the rows fill the wrong one.
3. Header drift. Same data, different names: Revenue / revenue / the Chinese for revenue / Amount (USD). Concatenating first and mapping later drops rows, because a groupby on one of those names ignores the other three entirely.
4. Column order. Two files with identical headers in a different order. Plain concatenation by position puts phone numbers in the email column, and nothing complains.

The order that works

Read → normalize each file → then merge. Never merge first.

import pandas as pd, os

RENAME = {"revenue ": "revenue", "Amount (USD)": "revenue"}
frames = []

for fn in sorted(os.listdir(folder)):
    if not fn.lower().endswith(".csv") or fn.startswith("~$"):
        continue

    path = os.path.join(folder, fn)

    # 1. encoding: try utf-8-sig (handles BOM), fall back to gbk
    for enc in ("utf-8-sig", "gbk"):
        try:
            df = pd.read_csv(path, dtype=str, encoding=enc)
            break
        except UnicodeDecodeError:
            continue
    else:
        print("SKIP (no encoding worked):", fn)
        continue

    # 2. header names: strip, lower, then apply the known aliases
    df.columns = [c.strip().lower() for c in df.columns]
    df = df.rename(columns=RENAME)

    # 3. provenance: you will want to know which file a bad row came from
    df["source_file"] = fn
    frames.append(df)

merged = pd.concat(frames, ignore_index=True, sort=False)
merged.to_csv("merged.csv", index=False, encoding="utf-8-sig")

Three details in there earn their keep. dtype=str stops pandas turning 007 into 7 and 20240101 into a date. sort=False keeps column order stable across files instead of sorting them alphabetically. source_file is the column you will thank yourself for when a total looks wrong.

Checking the merge before you use it

print("files merged :", len(frames))
print("rows merged  :", len(merged))
print("per file     :", merged.source_file.value_counts().to_dict())
print("empty cols   :", merged.columns[merged.isna().all()].tolist())

If files merged is smaller than the number of files in the folder, one file failed to parse and was skipped — that is the failure you were about to miss. A column that is entirely empty means a header mismatch, not missing data.

When the file is too big to open

Past roughly a million rows Excel will not open the result and pandas will need a few GB of RAM. Two options that keep the row count honest:

• Stream and append: read each file with chunksize=100000 and write to one output CSV in append mode, skipping the header after the first chunk. Memory stays flat.
• Merge into a real database instead. If you are merging CSVs this often, the files are a database that has not been normalized yet, and every merge is a manual re-derivation of the same table.

The tool

Excel Report Toolkit is the read → normalize → merge → render pipeline in one Python file: encoding fallback, header aliasing, provenance column, and a summary sheet on the other end, with a demo folder of deliberately messy files. 19 USDT, runs offline.