How to Split a Large CSV File Into Smaller Ones

You need to split a CSV because something downstream has a limit: an import capped at 10,000 rows, an email tool that rejects files over 5 MB, or Excel refusing to open more than 1,048,576 rows.

Splitting is easy. Splitting correctly means every output file is still a valid CSV — which mostly comes down to the header row.

The command line: fast, with one flaw

split is built into macOS and Linux and handles enormous files effortlessly:

split -l 10000 big.csv part_

That produces part_aa, part_ab, and so on, at 10,000 lines each.

The flaw: only the first file has the header row. Every other part starts mid-data, so importing them fails or treats a real record as the header.

Fix it by re-attaching the header afterwards:

# Save the header, split the body, then prepend the header to each part
head -n 1 big.csv > header.txt
tail -n +2 big.csv | split -l 10000 - part_
for f in part_*; do
  cat header.txt "$f" > "$f.csv" && rm "$f"
done
rm header.txt

Add --additional-suffix=.csv on GNU split to skip the rename step.

The second flaw is more serious: split works on lines, not CSV records. A quoted field containing a newline —

id,notes
1,"line one
line two"

— is one record across two lines. split may cut it in half, corrupting both files. If your data contains multi-line fields, use a real CSV parser instead.

Python: correct for any CSV

import csv

ROWS_PER_FILE = 10000

with open("big.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    header = next(reader)

    part, count, writer, out = 1, 0, None, None
    for row in reader:
        if count % ROWS_PER_FILE == 0:
            if out:
                out.close()
            out = open(f"part_{part:03d}.csv", "w", newline="", encoding="utf-8")
            writer = csv.writer(out)
            writer.writerow(header)
            part += 1
        writer.writerow(row)
        count += 1
    if out:
        out.close()

This streams — memory use stays flat regardless of file size, so it handles a 10 GB file on a laptop. Note {part:03d}, which zero-pads the numbers so part_002 sorts before part_010. Without padding, any tool processing them alphabetically handles your files in the wrong order.

Splitting by column value

Often more useful than fixed sizes: one file per region, per department, per month.

import csv
from collections import defaultdict

groups = defaultdict(list)
with open("sales.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.DictReader(f)
    for row in reader:
        groups[row["region"]].append(row)
    header = reader.fieldnames

for value, rows in groups.items():
    safe = "".join(c if c.isalnum() or c in "-_" else "_" for c in value)
    with open(f"{safe or 'blank'}.csv", "w", newline="", encoding="utf-8") as f:
        w = csv.DictWriter(f, fieldnames=header)
        w.writeheader()
        w.writerows(rows)

Sanitizing the value into a filename matters — a region called North/South would otherwise try to write into a directory that doesn't exist, and an empty value produces a file named .csv that your file manager hides.

Picking a split size

Match the limit you are actually working around:

| Constraint | Practical size | |---|---| | Excel row limit | under 1,000,000 rows | | Most CRM imports | 5,000–10,000 rows | | Email attachment | check MB, not rows | | API batch endpoints | whatever the docs say, usually 500–1,000 |

For a size limit rather than a row limit, estimate from the whole file: divide total bytes by row count to get average row size, then divide your target size by that. Round down generously — one oversized part means redoing the whole split.

Verify before you delete the original

Row counts should reconcile exactly:

# Sum of all parts, minus one header per part, should equal the original body
wc -l part_*.csv
wc -l big.csv

If you produced 12 parts, the total line count should be the original plus 11 — the extra header rows.

Keep the original until the downstream import has actually succeeded. Splitting is cheap to redo from the source and impossible to undo from the parts if one went missing.

Checklist

• Every part needs the header row.
• Zero-pad part numbers so they sort correctly.
• Use a CSV parser, not line splitting, if any field may contain newlines.
• Sanitize values used as filenames.
• Reconcile row counts before deleting the original.