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 in separate folders: 6 records produce two parts of 3; a header-only input produces no parts; a completely empty file is rejected. The boundary tests and execution results are recorded in the verification section below.

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

ChatGPT Python tool · Python 3.13.5 · standard library csv/pathlib

• Main run printed exactly: 'Verified 7 data records in 3 files.' and 'Output folder: outputs/sample_parts'.
• Created part_0001.csv, part_0002.csv, and part_0003.csv with 3, 3, and 1 data records respectively.
• part_0001 contained R001-R003 and preserved 'starter, pack' as one field and 'two\nlines' as one multiline field; units total = 28.
• part_0002 contained R004-R006; units total = 22. part_0003 contained R007; units total = 10. Overall units total = 60.
• Every part repeated the same 4-field header and all 7 source records survived once in original order.
• The original sample.csv bytes/text were unchanged by the successful run.
• A second run with outputs/sample_parts already present stopped with FileExistsError before creating another part.
• A 6-record input produced exactly 2 parts of 3 records each.
• A header-only input succeeded with 0 records and 0 part files while leaving the output directory empty.
• A completely empty input was rejected with ValueError: 'Missing header or blank column name.'

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)