Split Large CSV Files with Python: Headers, Multiline Fields, and Completion Checks

A CSV export can exceed an import tool’s record limit or need to be processed in smaller batches. Splitting it by physical lines can cut a quoted multiline field in half and leave later files without headers.

This beginner-to-intermediate tutorial uses Python’s standard library to split a CSV by data records. Every part repeats the header, preserves record order, and receives a completion marker only after the whole run succeeds. It follows the streaming large CSV tutorial and requires no pandas installation.

1. Method and input rules

Input is comma-delimited UTF-8 CSV with double-quoted fields and an optional BOM. Its first record is the header.
Column names cannot be empty, whitespace-only, or duplicated. Every data record must have the same field count as the header.
--rows counts data records, excluding repeated headers. Header-only input creates zero parts and a success marker; completely empty input fails.
The output directory must be new and its parent must exist. Failure can leave partial files: do not consume a directory without _SUCCESS.

The official Python CSV documentation specifies newline="" for file handling. Fields can contain line breaks, so physical lines do not count records. This tool preserves parsed values; quoting, BOM, and record line endings can change. It does not preserve the original bytes.

These are structural checks, not checks of ID uniqueness, types, or valid labels. For required column names and order, run a CSV schema check first.

2. Setup and test data

Use Python 3.9 or newer, without third-party dependencies. These examples were tested on Linux with Python 3.11.4.

Create a fresh demo directory and keep subsequent commands in the same terminal. Use a different directory name when repeating the experiment:

python3 --version
mkdir csv-split-demo
cd csv-split-demo
python3 - <<'PY_DATA'
import csv

with open("input.csv", "w", encoding="utf-8-sig", newline="") as handle:
    writer = csv.writer(handle)
    writer.writerow(["id", "note"])
    writer.writerows([
        ["001", "plain"],
        ["002", "comma, inside"],
        ["003", "first line\nsecond line"],
        ["004", 'say "hello"'],
        ["005", "最后一条"],
    ])
PY_DATA

Save the next section as split_csv.py in the current directory. The five test records include a comma, an embedded newline, quotes, Chinese text, and a leading BOM.

3. Complete implementation

"""Split a UTF-8 CSV by data records, repeating its header. Python 3.9+."""
import argparse
import csv
import json
from pathlib import Path
import sys


def positive_int(value):
    number = int(value)
    if number < 1:
        raise argparse.ArgumentTypeError("must be greater than zero")
    return number


def split_csv(source, output, rows_per_part):
    records = chunks = 0
    handle = None
    with source.open(encoding="utf-8-sig", newline="") as src:
        reader = csv.reader(src, strict=True)
        try:
            header = next(reader, None)
            if not header or any(not name.strip() for name in header):
                raise ValueError("missing header or empty column name")
            if len(set(header)) != len(header):
                raise ValueError("duplicate column names")
            output.mkdir()  # Existing paths are rejected, including empty dirs.
            for row in reader:
                if len(row) != len(header):
                    raise ValueError(
                        f"physical line {reader.line_num}: "
                        f"expected {len(header)} fields, got {len(row)}"
                    )
                if records % rows_per_part == 0:
                    if handle is not None:
                        handle.close()
                    chunks += 1
                    handle = (output / f"part-{chunks:08d}.csv").open(
                        "x", encoding="utf-8", newline=""
                    )
                    writer = csv.writer(handle, lineterminator="\n")
                    writer.writerow(header)
                writer.writerow(row)
                records += 1
        except csv.Error as exc:
            raise ValueError(
                f"CSV error near physical line {reader.line_num}: {exc}"
            ) from exc
        finally:
            if handle is not None:
                handle.close()
    # Only create the marker after input and all part files close successfully.
    with (output / "_SUCCESS").open("x", encoding="utf-8"):
        pass
    return {"records": records, "chunks": chunks, "output": str(output)}


def main():
    parser = argparse.ArgumentParser(description=__doc__, allow_abbrev=False)
    parser.add_argument("input", type=Path)
    parser.add_argument("output", type=Path, help="new directory; parent must exist")
    parser.add_argument("--rows", type=positive_int, default=100000,
                        help="data records per part (default: 100000)")
    args = parser.parse_args()
    try:
        report = split_csv(args.input, args.output, args.rows)
    except (OSError, UnicodeError, ValueError) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 1
    print(json.dumps(report, ensure_ascii=False))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

Input uses utf-8-sig; output uses UTF-8 without a BOM. See the Python utf-8-sig documentation for optional BOM decoding. strict=True reports malformed CSV detected by the parser. The script separately checks field counts so missing columns cannot pass as ordinary data.

Path.mkdir() rejects existing directories by default. Each part is also created in "x" mode. Use directories you control and keep other programs from changing input or output during the run.

Only one part stays open at a time. Memory depends on the current record and header, rather than total record count. A huge individual record still needs memory and may hit the CSV field-size limit. The part limit counts records, not bytes, so it cannot guarantee an upload service’s size limit in MB.

4. Usage and exit codes

Show help, then split at two data records per part:

python3 split_csv.py --help
python3 split_csv.py input.csv parts --rows 2

{"records": 5, "chunks": 3, "output": "parts"}

parts/
  part-00000001.csv
  part-00000002.csv
  part-00000003.csv
  _SUCCESS

The three parts contain 2, 2, and 1 data records, each with an additional header. An exact multiple creates no extra empty part. _SUCCESS is an empty file created only after reading the input and closing every part.

Interpret exit codes as follows:

Exit code	Meaning
0	All parts closed successfully, the marker exists, and stdout contains a JSON summary.
1	Input format, encoding, I/O, or path error; stderr explains the failure.
2	Invalid arguments, such as `--rows 0`.

An existing directory makes the current invocation return 1. An old _SUCCESS inside it cannot prove that this invocation succeeded. Automation must check both the current exit code and the marker in this run’s new directory.

5. Validate records and failure behavior

For the small demo, parse and combine the parts to check headers, order, and values:

python3 - <<'PY_CHECK'
import csv
from pathlib import Path

with open("input.csv", encoding="utf-8-sig", newline="") as handle:
    original = list(csv.reader(handle))
assert Path("parts/_SUCCESS").is_file()
files = sorted(Path("parts").glob("part-*.csv"))
assert len(files) == 3
combined = []
sizes = []
for file in files:
    with file.open(encoding="utf-8", newline="") as handle:
        rows = list(csv.reader(handle))
    assert rows[0] == original[0], file
    sizes.append(len(rows) - 1)
    combined.extend(rows[1:])
assert sizes == [2, 2, 1], sizes
assert combined == original[1:]
assert combined[2][1] == "first line\nsecond line"
print("OK: headers, order, 2/2/1 records, multiline field")
PY_CHECK

This verification loads a small fixture into lists for inspection. The splitter does not. Do not copy this verification unchanged for files that would exhaust memory.

Create input whose third data record has a missing field. Earlier parts must not be mistaken for a successful run. Run this once in the current demo directory; use a fresh directory to repeat it:

python3 - <<'PY_FAILURE'
from pathlib import Path
import subprocess
import sys

Path("bad.csv").write_text("id,note\n001,ok\n002,ok\n003\n", encoding="utf-8")
result = subprocess.run(
    [sys.executable, "split_csv.py", "bad.csv", "bad-parts", "--rows", "2"],
    capture_output=True, text=True, check=False,
)
assert result.returncode == 1, result
assert result.stdout == "" and "expected 2 fields, got 1" in result.stderr
assert Path("bad-parts/part-00000001.csv").is_file()
assert not Path("bad-parts/_SUCCESS").exists()
print("OK: late failure returns 1 and leaves no success marker")
PY_FAILURE

The failure leaves bad-parts for inspection. Retry with a new output directory after fixing the source data. Do not manually create _SUCCESS.

6. Integrate and troubleshoot

Handle failures explicitly before allowing downstream work:

if python3 split_csv.py input.csv parts-next --rows 100000; then
  test -f parts-next/_SUCCESS && printf 'Ready for the next step\n'
else
  printf 'Split failed; do not consume partial files\n' >&2
fi

This interactive example only prints messages. In an automation script, also use exit 1 in the failure branch to propagate failure. The marker proves that the program completed its close operations; it is not a filesystem transaction or a power-loss durability guarantee. Verify contents again after copying the directory.

Address the reported error:

Symptom	Action
`File exists`	Choose a new output directory and keep the old result for comparison.
`expected ... fields`	Inspect the reported physical line for missing or extra columns, or a blank record.
`unexpected end of data`	Check for truncated exports or unclosed quotes, then export again.
UTF-8 decoding failure	Identify and strictly convert the encoding using the CSV encoding tutorial. Do not discard bytes with `errors="ignore"`.
`field larger than field limit`	Confirm that the large field is legitimate before explicitly adjusting `csv.field_size_limit()`.
Insufficient disk space	Splitting does not reduce total data. Use a disk-space budget check, allowing for repeated headers and filesystem overhead.

A success marker without parts can be a valid header-only input; check the JSON records value. Reject zero records separately if downstream work requires data.

7. Summary

Splitting through a CSV parser keeps multiline fields intact and gives every part its own header. Use a new output directory, the current exit code, and a completion marker to distinguish complete results from failed leftovers before starting downstream work.