Splitting a Large CSV Into Smaller Files Without Losing Rows

A file too large for the tool that has to read it gets split. Excel stops at 1,048,576 rows, an upload form takes 100 MB, a loader is faster with parallel files. The splitting itself is one command, and the command most people reach for gets three things wrong: it leaves the header in only one file, it counts lines instead of records, and it does not check its work. This article splits a 20,000,000 record file five ways, verifies each result against the original, and shows what happens when a cut lands in the middle of a row.

split: fast, and the header stays behind

split -l cuts a file into pieces of N lines:

time split -l 1000000 -d -a 2 --additional-suffix=.csv work/big/orders-20m.csv work/big/split/a/part_
ls work/big/split/a | wc -l
head -n 1 work/big/split/a/part_00.csv; head -n 1 work/big/split/a/part_01.csv
python lab/big/verify_parts.py 'work/big/split/a/part_*.csv' work/big/orders-20m.csv

real	0m3.638s
user	0m0.265s
sys	0m0.834s
21
order_id,customer_id,zip,amount,created_at,status,country,note
999485,218617,09587,162.36,2025-11-12 21:14:18,shipped,DE,""
21 parts, 19,999,980 records, parts with problems: [('work/big/split/a/part_01.csv', 'no header'), ('work/big/split/a/part_02.csv', 'no header'), ('work/big/split/a/part_03.csv', 'no header'), ('work/big/split/a/part_04.csv', 'no header'), ('work/big/split/a/part_05.csv', 'no header'), ('work/big/split/a/part_06.csv', 'no header'), ('work/big/split/a/part_07.csv', 'no header'), ('work/big/split/a/part_08.csv', 'no header'), ('work/big/split/a/part_09.csv', 'no header'), ('work/big/split/a/part_10.csv', 'no header'), ('work/big/split/a/part_11.csv', 'no header'), ('work/big/split/a/part_12.csv', 'no header'), ('work/big/split/a/part_13.csv', 'no header'), ('work/big/split/a/part_14.csv', 'no header'), ('work/big/split/a/part_15.csv', 'no header'), ('work/big/split/a/part_16.csv', 'no header'), ('work/big/split/a/part_17.csv', 'no header'), ('work/big/split/a/part_18.csv', 'no header'), ('work/big/split/a/part_19.csv', 'no header'), ('work/big/split/a/part_20.csv', 'no header')]
bodies concatenated equal the original body: True

It made 21 parts of a million lines in 3.64 s. Look at the first lines of two of the parts: part_00.csv starts with order_id,customer_id,..., and part_01.csv starts with 999485,218617,..., a data row. Only the first part has the header. My verification script says so for each of the other 20: no header. It counted 19,999,980 records, 20 short of the true count, because it read the first row of each headerless part as a header. The bodies, concatenated, are identical to the original body (True), which is the one property split guarantees: it is lossless and mechanical.

A part without a header is a problem for any tool that expects one. Adding it afterwards means copying the header into each file, a rewrite of each file in full. Better to produce it with the parts.

The header, repeated

awk does that in one pass:

time awk -v n=1000000 'NR==1 {h=$0; next} (NR-2) % n == 0 {close(f); f=sprintf("work/big/split/b/part_%02d.csv", ++k); print h > f} {print > f}' work/big/orders-20m.csv
ls work/big/split/b | wc -l
python lab/big/verify_parts.py 'work/big/split/b/part_*.csv' work/big/orders-20m.csv

real	0m22.831s
user	0m6.542s
sys	0m3.178s
21
21 parts, 20,000,000 records, parts with problems: none
bodies concatenated equal the original body: True

awk writes the header at the start of each part and counts data lines (NR). It took 22.83 s, six times as long as split, for 21 parts with 20,000,000 records and no problems, and the concatenated bodies equal the original. The cost is awk's per-line processing. If the headerless parts are acceptable, split is the quick way; if not, a purpose-built tool is faster.

qsv, Miller and DuckDB

qsv, Miller and DuckDB parse the CSV, so the parts hold complete records and every part has a header:

time qsv split --size 1000000 work/big/split/c work/big/orders-20m.csv
ls work/big/split/c | head -n 3; ls work/big/split/c | wc -l
python lab/big/verify_parts.py 'work/big/split/c/*.csv' work/big/orders-20m.csv

Wrote 20 chunk/s to '/batches/csv-0001-0200/work/big/split/c'. Rows/chunk: 1000000 Num records: 20000000

real	0m6.787s
user	0m3.042s
sys	0m0.966s
0.csv
1000000.csv
10000000.csv
20
20 parts, 20,000,000 records, parts with problems: none
bodies concatenated equal the original body: False

qsv split --size 1000000 took 6.79 s and wrote 20 chunks, named by the row offset at which each starts: 0.csv, 1000000.csv, 10000000.csv. That naming sorts badly as text (10000000.csv comes before 2000000.csv), so a shell glob will process the parts out of order; rename them or sort numerically.

cd work/big/split/e && time mlr --csv split -n 1000000 --prefix part ../../orders-20m.csv
ls | head -n 3; ls | wc -l
cd /batches/csv-0001-0200
python lab/big/verify_parts.py 'work/big/split/e/part_*.csv' work/big/orders-20m.csv

real	0m35.478s
user	0m27.296s
sys	0m8.769s
part_1.csv
part_10.csv
part_11.csv
20
20 parts, 20,000,000 records, parts with problems: none
bodies concatenated equal the original body: False

mlr --csv split -n 1000000 wrote part_1.csv to part_20.csv in 35.48 s, the slowest, and the same sorting problem (part_10.csv before part_2.csv). Both tools produced 20 parts with 20,000,000 records and no parse problems. Both also report bodies concatenated equal the original body: False, and the reason is worth knowing. They parse and re-write the CSV, and they write an empty field as nothing, where the original writer had "". The last block of the check shows it:

for d in c e; do
  printf '%s: ' $d
  duckdb -noheader -list -c "SELECT (SELECT count(*) FROM (SELECT * FROM read_csv('work/big/orders-20m.csv', all_varchar=true) EXCEPT SELECT * FROM read_csv('work/big/split/$d/*.csv', all_varchar=true))) || ' rows only in the original, ' || (SELECT count(*) FROM (SELECT * FROM read_csv('work/big/split/$d/*.csv', all_varchar=true) EXCEPT SELECT * FROM read_csv('work/big/orders-20m.csv', all_varchar=true))) || ' rows only in the parts'"
done
head -n 3 work/big/split/c/0.csv | tail -n 1; head -n 3 work/big/orders-20m.csv | tail -n 1

c: 0 rows only in the original, 0 rows only in the parts
e: 0 rows only in the original, 0 rows only in the parts
2,17957,16017,200.75,2025-04-24 14:46:24,paid,BR,
2,17957,16017,200.75,2025-04-24 14:46:24,paid,BR,""

An EXCEPT between the original and the parts returned 0 rows in each direction for both qsv (c) and Miller (e): same records. The bytes differ, as the last two lines show: the original row ends ,BR,"" and the qsv part ends ,BR,. For most uses that is irrelevant. If the next step compares checksums of the files, or the consumer distinguishes "" from an empty field, it is not, and you want the byte-exact split or awk route.

time duckdb -c "COPY (SELECT * FROM read_csv('work/big/orders-20m.csv', header=true, all_varchar=true)) TO 'work/big/split/d' (FORMAT csv, HEADER, FILE_SIZE_BYTES '100MB')"
ls work/big/split/d | head -n 3; ls work/big/split/d | wc -l
du -sh work/big/split/d | cut -f1

real	0m4.736s
user	0m10.202s
sys	0m7.060s
data_0.csv
data_1.csv
data_10.csv
13
1.3G

DuckDB can write by size, not by rows (its CSV writing options include FILE_SIZE_BYTES and PARTITION_BY). FILE_SIZE_BYTES '100MB' produced 13 files in 4.74 s, from data_0.csv to data_12.csv, for a total of 1.3 GB; each file has its own header row, and the sizes were between 94,024,519 and 100,100,530 bytes, since the writer is multi-threaded and cuts at its own boundaries. That is the setting to use when the limit is in bytes, as for an upload.

Splitting by a key

Sometimes the parts should mean something: one file per country, per month, per customer. DuckDB writes them with PARTITION_BY:

mkdir -p work/big/split/g
time duckdb -c "COPY (SELECT * FROM read_csv('work/big/orders-20m.csv', header=true, all_varchar=true)) TO 'work/big/split/g' (FORMAT csv, HEADER, PARTITION_BY (country))"
ls work/big/split/g; ls work/big/split/g/country=BR | head -n 2
duckdb -noheader -list -c "SELECT count(*) FROM read_csv('work/big/split/g/country=BR/*.csv', all_varchar=true)"

real	0m12.167s
user	0m26.861s
sys	0m25.607s
country=BR
country=DE
country=FR
country=GB
country=IT
country=JP
country=US
data_0.csv
2856917

Seven directories, country=BR to country=US, in 12.17 s. country=BR holds 2,856,917 rows, in a file named data_0.csv. The directory name country=BR is the Hive convention, which DuckDB, Spark and others read back as a partition column; the country column itself is not repeated in the files (their header is order_id,...,status,note). Partitioning by a key with many values makes many small files, so choose one with a small, stable set of values.

The cut that lands inside a record

split -l counts lines, and a quoted field can contain a line break. A cut at the wrong line splits one record across two files. In this file, the first record with a line break starts on line 1429:

N=$(grep -n -m1 '"two$' work/big/orders-20m.csv | cut -d: -f1)
echo "the first record with a line break in a field starts on line $N"
mkdir -p work/big/split/f
rm -f work/big/split/f/*; head -n 4000 work/big/orders-20m.csv | split -l $N -d -a 2 --additional-suffix=.csv - work/big/split/f/part_
tail -n 1 work/big/split/f/part_00.csv
head -n 1 work/big/split/f/part_01.csv
python - <<'PY'
import csv
try:
    sum(1 for _ in csv.reader(open('work/big/split/f/part_00.csv', newline='', encoding='utf-8'), strict=True))
except csv.Error as e:
    print('part_00 with strict csv parsing -> csv.Error:', e)
PY
duckdb -noheader -list -c "SELECT 'last row of part_00: ' || order_id || ' | ' || note FROM read_csv('work/big/split/f/part_00.csv', header=true, all_varchar=true) ORDER BY order_id::INT DESC LIMIT 1"
duckdb -noheader -list -c "SELECT 'first row of part_01: ' || column0 || ' | ' || column7 FROM read_csv('work/big/split/f/part_01.csv', header=false, all_varchar=true) LIMIT 1"

the first record with a line break in a field starts on line 1429
1428,119439,74173,292.60,2025-02-12 12:27:14,paid,JP,"two
lines"
part_00 with strict csv parsing -> csv.Error: unexpected end of data
last row of part_00: 1428 | "two
first row of part_01: 1429 | leave at door

I cut the first 4,000 lines at a multiple of 1,429. part_00 ends with 1428,119439,...,JP,"two, a record with an open quote, and part_01 begins with lines". Python's csv module in strict mode raised unexpected end of data on the first part. DuckDB did not complain: it read the broken record as a row with the note "two, and in the second part the stray lines" was not in the table at all, the first row being 1429. No error, and a row that now says something different from the source. The 20 million-line file split without such a cut by chance (about 20 boundaries, each with a 0.05% chance of landing in a multi-line record), and the probability is not zero. A file with more multi-line fields, say long free-text notes, would break most splits.

Rules that follow: split with a tool that parses records (qsv, Miller, DuckDB, or your own csv loop) whenever the data can contain quoted newlines; or check the result with a parser (my script does) before trusting it; or confirm first that the file has no multi-line fields, by comparing the line count with the record count, as in the line counting article.

How to tell it worked

Run a verification, not an ls. Check that every part has the header, that every part parses with a strict parser, that the total number of records equals the original's, and that the set of records is the same (EXCEPT in both directions, or a checksum over the bodies when you used a byte-exact tool). Check the part sizes against the limit you split for, in rows or bytes. Check that the file names sort the way you will read them. And keep the manifest: the number of parts, the records in each and the source checksum.

When the parts must be bytes of the original

If a downstream check compares the checksum of the concatenated parts with the original, use split or awk and put the header in yourself. If the consumer is Excel, 1,000,000 records per part fits under its 1,048,576-row limit (header included), but the workbook article also covers the string-length limit and a library that drops rows past the limit without an error. And if you are splitting only to make a file fit into memory, consider not splitting: reading in chunks or a DuckDB query over the whole file does the same job without producing 20 files to manage. The large-file hub compares the approaches.