Split a large CSV into smaller files without losing the header

01Define what counts as a row

The script creates numbered CSV files containing at most a chosen number of data records. Each file starts with the same header. The header does not count toward the limit. With a limit of 3 and 7 data records, the files contain 3, 3, and 1 data records.

A CSV record is not necessarily a physical text line. A quoted field can contain a line break. The csv module reads records according to CSV quoting rules, so a multiline note stays attached to its record instead of becoming a separate row.

02Create the synthetic input

This dataset is synthetic and was written specifically for this article. Save it as sample.csv in your working folder. Copy the line break inside the quoted note exactly; it deliberately exercises a case that ordinary line splitting mishandles.

record_id,team,units,note
R001,Support,12,"starter, pack"
R002,Ops,7,repeat
R003,Support,9,"two
lines"
R004,Sales,5,normal
R005,Ops,11,normal
R006,Sales,6,normal
R007,Support,10,normal

There are 4 columns and 7 data records. The comma in starter, pack belongs inside one field. The note belonging to R003 spans 2 physical lines but remains one field. The units values sum to 60: 12 + 7 + 9 + 5 + 11 + 6 + 10.

03Prepare the working folder

1. Place sample.csv and a new script named split_large_csv.py in the same working folder.
2. Paste the complete Python code from the next section into the script.
3. Keep ROWS_PER_FILE at 3 for this example. The value must be a positive integer.
4. Open a terminal in the working folder and run the command below.

python split_large_csv.py

Relative paths are resolved from the terminal's working directory, not automatically from the script's location. The parent outputs folder may already exist, but outputs/sample_parts must not exist when this run starts.

04Split records and verify the written files

The splitter reads records incrementally and opens each part in exclusive creation mode. After writing, it rereads the source and parts to compare headers, field values, record order, and counts. Success messages appear only after that comparison finishes.

import csv
from pathlib import Path

SOURCE = Path("sample.csv")
OUTPUT_DIR = Path("outputs") / "sample_parts"
ROWS_PER_FILE = 3


def part_path(number: int) -> Path:
    return OUTPUT_DIR / f"part_{number:04d}.csv"


def split_csv() -> tuple[int, int]:
    if type(ROWS_PER_FILE) is not int or ROWS_PER_FILE < 1:
        raise ValueError("ROWS_PER_FILE must be a positive integer.")

    with SOURCE.open("r", encoding="utf-8-sig", newline="") as source:
        reader = csv.reader(source, strict=True)
        header = next(reader, None)
        if not header or any(not name.strip() for name in header):
            raise ValueError("Missing header or blank column name.")
        if len(set(header)) != len(header):
            raise ValueError("Duplicate column names.")

        OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
        OUTPUT_DIR.mkdir()  # Refuse to reuse an existing output path.
        total = 0
        parts = 0
        target = None
        try:
            for record_no, row in enumerate(reader, start=1):
                if len(row) != len(header):
                    raise ValueError(
                        f"Data record {record_no}: wrong number of fields."
                    )
                if total % ROWS_PER_FILE == 0:
                    if target is not None:
                        target.close()
                    parts += 1
                    target = part_path(parts).open(
                        "x", encoding="utf-8", newline=""
                    )
                    writer = csv.writer(target)
                    writer.writerow(header)
                writer.writerow(row)
                total += 1
        finally:
            if target is not None:
                target.close()

    return total, parts


def verify_csv(expected_rows: int, part_count: int) -> None:
    with SOURCE.open("r", encoding="utf-8-sig", newline="") as source:
        original = csv.reader(source, strict=True)
        header = next(original, None)
        seen = 0
        for number in range(1, part_count + 1):
            with part_path(number).open(
                "r", encoding="utf-8", newline=""
            ) as part:
                rows = csv.reader(part, strict=True)
                if next(rows, None) != header:
                    raise ValueError(f"Header mismatch in part {number}.")
                count = 0
                for row in rows:
                    if row != next(original, None):
                        raise ValueError(f"Data mismatch in part {number}.")
                    count += 1
                required = min(
                    ROWS_PER_FILE,
                    expected_rows - (number - 1) * ROWS_PER_FILE,
                )
                if count != required:
                    raise ValueError(f"Row count mismatch in part {number}.")
                seen += count
        if seen != expected_rows or next(original, None) is not None:
            raise ValueError("Overall row count mismatch.")


if __name__ == "__main__":
    total, parts = split_csv()
    verify_csv(total, parts)
    print(f"Verified {total} data records in {parts} files.")
    print(f"Output folder: {OUTPUT_DIR.as_posix()}")

Input decoding accepts UTF-8 with or without a leading byte-order mark. Output uses UTF-8 without that mark. The newline argument lets the csv module handle record endings and embedded line breaks.

05Check the expected result by hand

The expected files are listed below. Their units totals are manual cross-checks, not calculations performed by the splitter. Every file must repeat record_id,team,units,note as its header.

The expected console text is shown below. It was derived by hand and is not a captured execution log.

Verified 7 data records in 3 files.
Output folder: outputs/sample_parts

Check that 28 + 22 + 10 equals the original total of 60. Open the first part in a text editor to inspect the quoted comma and multiline note. Counting visible lines alone will give the wrong data-record count.

06Check before using a larger input

1. Confirm that all 3 files have the same 4 header fields and that the header appears only once in each file.
2. Read the files in numbered order. R001 through R007 should each appear once, with no missing or repeated record.
3. Confirm that the script reports verification success. Its comparison checks field strings, not only counts, so equal counts alone are insufficient.
4. Run the script again without changing the destination. It should stop with FileExistsError before writing another part.

Test boundaries in separate working folders: 6 records should produce 2 parts of 3, not an empty third part. A header-only input should leave an empty output folder. A completely empty input should be rejected. These expectations were inspected, not executed here.

07Recognize common errors

A late error can leave partial files. The script closes its current file but does not roll back the folder or automatically delete anything.

08Understand the limits before scaling

After the example passes on your machine, change SOURCE, choose a fresh OUTPUT_DIR beneath outputs, and increase ROWS_PER_FILE. A record limit is not a byte-size limit: long fields can make equally sized groups occupy very different amounts of disk space.

The script preserves parsed field strings, not original bytes, quoting style, or record-ending format. It does not load the entire dataset, but unusually large fields still require memory and may exceed the CSV parser's field-size limit. Verification adds another read of the source and outputs. Keep the source unchanged throughout; this is not a locked snapshot or transactional backup.

Execution and verification record

2026-09-20 · hand-checked example · target: Python 3.12 · standard library: csv, pathlib · no execution

• Manually counted 4 columns and 7 data records, treating the quoted multiline note as one field.
• Manually assigned records to groups of 3, 3, and 1.
• Manually calculated group totals of 28, 22, and 10, giving 60 overall.
• Inspected the output naming, exclusive creation, header repetition, and sequential comparison logic.
• Derived the expected console text from the example and code.

Site-wide writing and verification principles

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Official Python 3.12 documentation · csv ↗ (New tab)
• Official Python 3.12 documentation · pathlib ↗ (New tab)
• Official Python 3.12 documentation · open ↗ (New tab)