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, ormlr --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+splitloop 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:
- The rows add up. The data rows across all pieces equal the data rows
in the original.
qsv countormlr --icsv --opprint counton each gives the number without the header. - 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.
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