Merging a folder of CSV files looks like a one-liner until encodings, drifting headers, or an old output file enter the picture. A successful write is not evidence that the result contains the intended records.

Here are four failure modes and a workflow that makes them easier to inspect.

Failure 1: encoding

CSV encoding depends on the exporting application and the selected export option, not just the machine's language. A file might be UTF-8, GBK, or something else. Start with the producer's documented encoding. A successful decode alone does not prove that the text is correct.

Use strict decoding. In pandas the option for handling decoding failures is encoding_errors; encoding_errors="ignore" can discard bytes. A fallback list is appropriate only when it represents the encodings your sources actually use. Stop if every candidate fails rather than quietly skipping a file.

Failure 2: the BOM

Some UTF-8 exports begin with the byte-order mark EF BB BF; not every Excel CSV export does. Python's utf-8-sig decoder consumes an initial UTF-8 BOM and also accepts UTF-8 without one. Plain Python utf-8 decoding retains that character, although CSV readers, including pandas in common cases, may handle it themselves. Do not assume pandas always creates a \ufeffid header.

See the Python codec documentation.

Failure 3: header drift

Revenue, revenue, revenue and Amount (USD) might mean the same thing, but only a source contract can establish that mapping. Concatenating these names does not itself drop rows: pandas normally creates separate columns. A later aggregation can then omit records with missing keys.

Normalize only approved aliases, and check for collisions. If one input contains both Revenue and sales, renaming both to revenue creates duplicate labels; stop and decide how those fields relate instead of silently combining them.

Failure 4: column order

A hand-written loop that appends values by position can put phone numbers in an email column when headers move. pandas concat aligns columns by label. sort=False avoids sorting the non-concatenation axis in this example; it does not enforce a required schema or repair duplicate names. See pandas.concat.

Read, normalize, then merge

This example assumes comma-delimited files with one header row. Set ENCODINGS from the source specifications. It rejects unexpected schema differences rather than assuming they are harmless.

from pathlib import Path import pandas as pd folder = Path("exports") out = folder / "merged.csv" ENCODINGS = ("utf-8-sig", "gbk") # example source-specific candidates RENAME = {"amount (usd)": "revenue", "sales": "revenue"} files = sorted( p for p in folder.iterdir() if p.is_file() and p.suffix.lower() == ".csv" and not p.name.startswith("~$") and p.resolve() != out.resolve() # never re-ingest a previous result ) if not files: raise ValueError("No input CSV files") def read_source(path): last_error = None for enc in ENCODINGS: try: return pd.read_csv( path, dtype=str, encoding=enc, encoding_errors="strict", keep_default_na=False, ) except UnicodeDecodeError as exc: last_error = exc raise ValueError(f"No configured encoding decoded {path}") from last_error def normalize(df): names = [str(c).strip().lower() for c in df.columns] df.columns = [RENAME.get(c, c) for c in names] if not df.columns.is_unique: raise ValueError("Header normalization produced duplicate columns") if "source_file" in df.columns: raise ValueError("source_file is reserved for provenance") return df frames = [] counts = {} expected_columns = None for path in files: df = normalize(read_source(path)) if expected_columns is None: expected_columns = df.columns.tolist() elif set(df.columns) != set(expected_columns): raise ValueError(f"Unexpected columns in {path.name}") df = df.reindex(columns=expected_columns) counts[path.name] = len(df) df["source_file"] = path.name frames.append(df) merged = pd.concat(frames, ignore_index=True, sort=False) if len(merged) != sum(counts.values()): raise AssertionError("Concatenated row count differs from input counts") merged.to_csv(out, index=False, encoding="utf-8-sig") print("files merged:", len(files)) print("rows per input:", counts) # includes header-only inputs with zero rows print("rows merged:", len(merged))

dtype=str preserves values such as 007; ordinary read_csv does not automatically parse dates unless requested. keep_default_na=False also preserves text tokens such as NA and empty fields rather than interpreting them as missing values. Choose a different missing-value policy explicitly when needed. CSV parsing can still reject malformed input, and blank physical lines are skipped by default: these counts describe parsed records, not raw lines. See pandas.read_csv.

The code excludes an existing output and guards against an empty input list. The filename filter is a local convention, not a guarantee that every ~$ file is an invalid CSV. If an input already has duplicate raw headers, audit those too: reader-generated disambiguated names are not proof of a clean schema.

Verify before you trust it

Reconcile parsed counts with the producer's manifest or expected totals, inspect representative non-ASCII text, and check required fields and business totals. The concatenation check alone cannot detect records already absent from an input.

An entirely empty column is a reason to investigate, not proof of a header mismatch. It may be a legitimate optional field. A source-file frequency count also omits zero-row inputs, which is why the example keeps a separate count dictionary.

When the result is too big for a worksheet

An Excel worksheet supports 1,048,576 rows, including a header if present; this is not a limit on CSV itself or every Excel import route. Memory usage in pandas depends on the data, types, and intermediate copies, not a fixed number of gigabytes per million rows. See Microsoft's worksheet limits.

For larger jobs, use chunked reads with the same verified encoding, normalization, and column order for every chunk. Open the output once with the chosen encoding, write a header once, and retain input counts. Appending inconsistent schemas or restarting a failed encoding attempt after some chunks have been written can corrupt the result. Chunking bounds the working set more effectively than retaining every input frame; it is not a promise of constant memory use.

A database is another option for repeated imports, but it still needs an explicit schema, encoding policy, and reconciliation checks.

The short version

Use known source encodings, normalize approved header aliases, reject collisions, exclude prior output, retain provenance, and reconcile records and totals before relying on the result.

Related guide: Merging CSV files into one file.