mygems

How to split a large CSV file into smaller files

split -l loses the header and cuts quoted rows in half. Here is how to split a CSV by row count or by a column's value, keeping a header on every piece.

By mygems ·

Splitting a CSV is easy to do badly. The obvious command, split -l, cuts the file into pieces of so many lines, and leaves you with one piece that has a header and nineteen that don't, plus a few rows broken in half wherever a field held a line break. The tools below avoid both, and some of them split by a column's value rather than by count, which is often what you wanted in the first place.

The fast answer

  • Into pieces of N rows, header on each: qsv split -s 100000 out/ big.csv, or mlr --csv split -n 100000 big.csv.
  • One file per value of a column (per region, per month): mlr --csv split -g region big.csv.
  • No tools installed: the tail + split loop below, as long as no field contains a line break.
  • Only to get it under Excel's limit: you may not need to split at all. See the end of this page.

Before you split: why?

The right method depends on what the pieces are for.

  • A spreadsheet won't open it. Excel stops at 1,048,576 rows. Splitting works, but every question that spans the whole file now needs all the pieces. Opening the whole file is usually better.
  • An upload or email limit. Split by size. Rows don't matter, as long as each piece has a header.
  • Different people need different parts. Split by a column: one file per team, customer or month. This is the case where splitting is clearly right.
  • A batch import that takes 10,000 rows at a time. Split by count.

Split by row count, keeping the header

qsv (or its predecessor xsv) is a single binary built for this:

qsv split -s 100000 out/ big.csv

That writes out/0.csv, out/100000.csv, and so on, each with the header and up to 100,000 data rows. It parses the CSV properly, so a quoted field containing a line break stays in one piece.

Miller does the same, and names the files split_1.csv, split_2.csv:

mlr --csv split -n 100000 big.csv

Split by a column's value

This is the more useful split, and the one plain split can't do:

mlr --csv split -g region big.csv

You get split_East.csv, split_West.csv and so on, one per distinct value, each with the header. Group by two columns with -g region,year.

DuckDB writes the same thing as a folder per value, which suits very large files because it streams rather than loading the whole thing:

duckdb -c "COPY (SELECT * FROM 'big.csv') TO 'by_region' (FORMAT csv, PARTITION_BY (region))"

The output is by_region/region=East/data_0.csv and so on. Note that the partition column is left out of the files by default, since it's in the folder name; add WRITE_PARTITION_COLUMNS true to keep it.

With no tools installed

On a Mac or Linux machine, plain shell commands do it, with one condition: no field in the file may contain a line break. Check by opening it in something that parses CSV and comparing the row count with wc -l.

tail -n +2 big.csv | split -l 100000 - part_
for f in part_*; do (head -n 1 big.csv; cat "$f") > "$f.csv"; rm "$f"; done

The first line strips the header and splits the rest. The second puts the header back on each piece. If your rows have quoted multi-line fields, this will cut some in half: use qsv or Miller.

On Windows, PowerShell can do it without installing anything, but for a large file a CSV tool is far faster. The same qsv command works there.

In Python

import pandas as pd

reader = pd.read_csv("big.csv", chunksize=100_000, dtype=str, keep_default_na=False)
for i, chunk in enumerate(reader):
    chunk.to_csv(f"part_{i}.csv", index=False)

The two extra arguments matter. Without dtype=str, pandas guesses types and quietly turns 00742 into 742 and long IDs into floats; without keep_default_na=False, the text NA becomes an empty cell. Splitting should never change a value.

Split to a target size

No common tool splits a CSV at exactly 10 MB, because a cut has to fall between rows. Estimate instead: divide the file size by the row count to get bytes per row, then choose a row count that lands under the limit.

A 900 MB file with 3,000,000 rows is about 300 bytes a row, so 30,000 rows is roughly 9 MB per piece. Allow some margin: rows aren't all the same length.

Check the result

Two checks catch nearly every splitting bug:

  1. The rows add up. The data rows across all pieces equal the data rows in the original. qsv count or mlr --icsv --opprint count on each gives the number without the header.
  2. Every piece has the same header, in the same order. Open two pieces at random and look at the first line.

To put them back together later, merging CSV files is the reverse operation, and has its own traps around repeated headers.

When you didn't need to split

If the reason was "Excel won't open it", the pieces will make every later question harder: a total across the year now means adding up twelve files. A tool that opens the whole file is usually the better fix. On a desktop that means DuckDB, or Power Query, as covered here.

On a phone, it means an app that doesn't stop at a row limit, and that lets you narrow the file down to the part you need without producing pieces at all.

CSV File Viewer - Smart CSViPhone & iPad · Android

Opens the whole file on iPhone or Android, then filters it down with a visual filter or SQL, so you can read one region or one month without splitting anything.

What it does

Questions

How do I split a large CSV file into smaller files?
Use a CSV-aware tool that copies the header to each piece: qsv split -s 100000 out/ big.csv, or mlr --csv split -n 100000 big.csv. Plain split -l works only if you add the header back yourself and no field contains a line break.
How do I split a CSV file and keep the header on each file?
qsv split and Miller's split verb both repeat the header automatically. With plain shell tools, strip the header with tail -n +2, split the rest, then prepend the first line of the original to each piece.
Can I split a CSV by the values in a column?
Yes. mlr --csv split -g region big.csv writes one file per distinct value of the region column. DuckDB's COPY with PARTITION_BY does the same into one folder per value.
Why are some rows broken after splitting my CSV?
A field contained a line break inside quotes, and a line-based tool like split -l cut the row in two. Use a tool that parses CSV, such as qsv or Miller, so quoted line breaks stay inside their row.
How many rows can Excel open from a CSV?
1,048,576 rows including the header, per worksheet. Rows beyond that are not loaded. Splitting the file works, but opening it in a tool without that limit is usually more useful.

mygems — Builds CSV Editor and Smart CSV Viewer, two apps that exist because spreadsheets give up on big files.