How to split a CSV into multiple files in Python

Splitting by line count (split -l) breaks CSVs whose quoted fields contain line breaks and drops the header from every part but the first. This script splits by records, every N rows or by a column's value, and repeats the header.

1. The script

Save as split_csv.py:

import csv, re, sys
from pathlib import Path

def split_rows(src, size, out):
    with open(src, newline='', encoding='utf-8-sig') as f:
        reader = csv.reader(f)
        header = next(reader)
        rows = [r for r in reader if any(c.strip() for c in r)]
    width = len(str((len(rows) + size - 1) // size))
    for i in range(0, len(rows), size):
        write(out / f'part-{i // size + 1:0{width}}.csv', header, rows[i:i + size])

def split_by(src, column, out):
    groups = {}
    with open(src, newline='', encoding='utf-8-sig') as f:
        reader = csv.DictReader(f)
        header = reader.fieldnames
        for r in reader:
            if any((v or '').strip() for v in r.values()):
                groups.setdefault((r[column] or '').strip(), []).append([r[h] for h in header])
    used = set()
    for value, rows in groups.items():
        base = re.sub(r'[\\/:*?"<>|\x00-\x1f]+', '_', value).strip('. ')[:60] or 'blank'
        name, n = base, 2
        while name.lower() in used:
            name, n = f'{base}_{n}', n + 1
        used.add(name.lower())
        write(out / f'{name}.csv', header, rows)

def write(path, header, rows):
    with open(path, 'w', newline='', encoding='utf-8') as f:
        w = csv.writer(f)
        w.writerow(header)
        w.writerows(rows)

src, how, out = sys.argv[1], sys.argv[2], Path(sys.argv[3] if len(sys.argv) > 3 else 'parts')
out.mkdir(exist_ok=True)
split_rows(src, int(how), out) if how.isdigit() else split_by(src, how, out)
print(f'{len(list(out.glob("*.csv")))} files in {out}/')

2. Run it

python3 split_csv.py big.csv 1000          # part-1.csv, part-2.csv, ... 1000 rows each
python3 split_csv.py big.csv Region        # one file per value of the Region column
python3 split_csv.py big.csv Region out    # write into ./out instead of ./parts

On the country-codes file (249 rows) 50 gave 5 files of 50/50/50/50/49 rows, Continent gave 7 files (AF, AN, AS, EU, NA, OC, SA) and "Region Name" gave 6, including blank.csv for the one row without a region (Antarctica).

3. What it does

• Header in every part: the first row is written at the top of each file.
• Records, not lines: csv.reader keeps a quoted value with a line break in one row, so no record is cut in half.
• Safe file names: characters Windows forbids (\ / : * ? " < > |) become _, an empty value becomes blank.csv, and values that would collide on a case-insensitive disk (N/S, n/s, N_S) get _2, _3.
• Excel files: utf-8-sig removes the BOM Excel adds, so the first column name matches. Blank rows are skipped.
• Codes stay text: NA (North America, Namibia) and ids like 02134 are written exactly as read. pandas would read NA as a missing value unless you pass keep_default_na=False.

4. Limits

• All rows are read into memory; for files of several GB, write each part while reading instead of collecting the rows first.
• Comma-separated only; pass delimiter=';' to csv.reader and csv.DictReader for European Excel files.

5. No code

The free split CSV tool does the same in your browser, every N rows or by column, and downloads all parts as one .zip. Nothing is uploaded.

Written with AI assistance. The script was run on 2026-10-01 with Python 3 standard library only, on the open country-codes dataset (datasets/country-codes on GitHub: 249 rows, 56 columns) split every 50 rows, by Continent and by Region Name, and on an edge-case file (quoted line breaks, blank values, file names that collide after cleaning). Every output file was compared cell for cell with the free split CSV tool on this site: 0 mismatches. PlainTables is our extension; the other methods are free and built into Excel, Google Sheets and pandas.