Splitting a CSV by Column Value Into Separate Files

Splitting one big file into one file per region, per customer or per month is a normal request, and the one-liner for it in every shell cheat sheet is missing its header. It also hands the contents of your data to the file system as file names, which is a different kind of risk. This article splits the 2,000,000 row orders file by country with awk, qsv, Miller and DuckDB, shows how to check that nothing was lost, and covers the two failure modes that matter: a missing header, and a value that is not a safe file name. It is part of the tour of working with CSV in the shell. The tools are documented in the qsv repository, the Miller reference and the DuckDB COPY page.

The one-liner and what it forgets

The usual answer is to let awk redirect each line to a file named for a column:

rm -rf /tmp/split1 && mkdir /tmp/split1 && cd /tmp/split1
awk -F, 'NR > 1 { print > ($7 ".csv") }' orders-2m.csv
ls; head -n 2 FR.csv; printf 'header present: '; head -n 1 FR.csv | grep -c order_id

BR.csv
DE.csv
FR.csv
GB.csv
IN.csv
JP.csv
US.csv
12,243905,07969,58.38,2025-04-21 23:17:25,pending,FR,gift
21,125088,90219,25.46,2025-03-01 22:23:15,shipped,FR,
header present: 0

It created seven files, BR.csv to US.csv. The last two lines of the output are the problem: head -n 2 FR.csv shows two data rows, and grep -c order_id on the first line of FR.csv returned 0. There is no header in any file. I skipped it with NR > 1 because otherwise the header would have gone to a file named country.csv, and nothing writes it back. Every file you hand to someone now needs its first line guessed.

Add the header

The fix is to remember the header and print it the first time each file is used:

rm -rf /tmp/split2 && mkdir /tmp/split2 && cd /tmp/split2
awk -F, 'NR == 1 { h = $0; next } { f = $7 ".csv"; if (!(f in s)) { print h > f; s[f] = 1 } print > f }' orders-2m.csv
ls
wc -l *.csv | tail -n 1
head -n 2 FR.csv

BR.csv
DE.csv
FR.csv
GB.csv
IN.csv
JP.csv
US.csv
  2000007 total
order_id,customer_id,zip,amount,created_at,status,country,note
12,243905,07969,58.38,2025-04-21 23:17:25,pending,FR,gift

NR == 1 { h = $0; next } saves the header line. For each row, f is the file name; the first time that name is seen, print h > f writes the header, and every row goes to the same file. In one awk run, > truncates the file only when it is first opened and then appends, so the data accumulates. The listing shows the seven files, wc -l reports 2,000,007 lines in total, and head -n 2 FR.csv starts with order_id,customer_id,.... The total is the check: 2,000,000 data rows plus one header for each of 7 files. If you get 2,000,000, there are no headers, and if it is 2,000,001, the header went somewhere unexpected.

qsv partition

qsv does this as one command, with the header in each file and a CSV parser behind it:

rm -rf /tmp/split3 && mkdir /tmp/split3
time qsv partition country /tmp/split3 orders-2m.csv
ls /tmp/split3
head -n 2 /tmp/split3/FR.csv
wc -l /tmp/split3/*.csv | tail -n 1

real	0m0.786s
user	0m0.576s
sys	0m0.108s
BR.csv
DE.csv
FR.csv
GB.csv
IN.csv
JP.csv
US.csv
order_id,customer_id,zip,amount,created_at,status,country,note
12,243905,07969,58.38,2025-04-21 23:17:25,pending,FR,gift
  2000007 total

qsv partition country /tmp/split3 orders-2m.csv took 0.79 s and made the same seven files. The first lines of FR.csv carry the header, and the total is again 2,000,007. The name pattern is configurable:

rm -rf /tmp/split4 && mkdir /tmp/split4
qsv partition --filename '{}-orders.csv' country /tmp/split4 orders-2m.csv
ls /tmp/split4 | head -n 3

BR-orders.csv
DE-orders.csv
FR-orders.csv

With --filename '{}-orders.csv' the files became BR-orders.csv, DE-orders.csv, FR-orders.csv. The column to split on comes first, then the output directory, then the input. The directory must exist.

Miller and DuckDB

Miller has a split verb:

rm -rf /tmp/split5 && mkdir /tmp/split5 && cd /tmp/split5
time mlr --csv split -g country orders-2m.csv
ls
head -n 2 split_FR.csv

real	0m2.062s
user	0m3.228s
sys	0m1.309s
split_BR.csv
split_DE.csv
split_FR.csv
split_GB.csv
split_IN.csv
split_JP.csv
split_US.csv
order_id,customer_id,zip,amount,created_at,status,country,note
12,243905,07969,58.38,2025-04-21 23:17:25,pending,FR,gift

mlr --csv split -g country file.csv wrote split_BR.csv through split_US.csv into the current directory in 2.06 s, each with the header. --prefix orders renames them to orders_BR.csv and so on:

rm -rf /tmp/split6 && mkdir /tmp/split6 && cd /tmp/split6
mlr --csv split -g country --prefix orders orders-2m.csv && ls | head -n 3

orders_BR.csv
orders_DE.csv
orders_FR.csv

DuckDB's COPY with PARTITION_BY, which the export article introduced, is the fastest and has a different layout:

rm -rf /tmp/split7 && mkdir /tmp/split7
time duckdb -c "COPY (SELECT * FROM 'orders-2m.csv') TO '/tmp/split7' (FORMAT csv, HEADER, PARTITION_BY (country))"
ls /tmp/split7 | head -n 3
head -n 2 /tmp/split7/country=FR/data_0.csv

real	0m0.616s
user	0m2.184s
sys	0m1.130s
country=BR
country=DE
country=FR
order_id,customer_id,zip,amount,created_at,status,note
1130234,77246,41234,27.51,2025-09-11 09:29:24,pending,gift

It took 0.62 s and created folders named country=BR, country=DE and so on, each holding data_0.csv. The first rows show the file: it starts order_id,customer_id,zip,amount,created_at,status,note, and the country column is not in the file. The value lives in the folder name. This is the Hive layout, and it is what you want when the consumer is DuckDB, Spark or another tool that reads partitioned data. It is not what you want when you are mailing FR.csv to a colleague, who will find a file with no country column.

Check that nothing was lost

Splitting should not change the data, only the packaging. Two checks:

for d in split2 split3; do
  printf '%s: ' $d; cat /tmp/$d/*.csv | grep -vc '^order_id' ; 
done
printf 'source rows: '; tail -n +2 orders-2m.csv | wc -l
printf 'md5 of sorted FR rows, awk vs qsv: '
for d in split2 split3; do tail -n +2 /tmp/$d/FR.csv | sort | md5sum | cut -c1-12; done | tr '\n' ' '; echo

split2: 2000000
split3: 2000000
source rows: 2000000
md5 of sorted FR rows, awk vs qsv: 8ce6e31dd5bc 8ce6e31dd5bc 

The count of non-header lines across all the files is 2,000,000 for both the awk output (split2) and the qsv output (split3), matching the 2,000,000 rows of the source. For a stronger check, the sorted data rows of FR.csv from the two tools hashed to the same value, 8ce6e31dd5bc, so the two programs produced the same set of rows for France. If the line counts match and one file's checksum matches across tools, the split is right. Do this once on any new splitting script. It costs a few seconds and finds the mistake that otherwise surfaces as a missing region in a report.

File names from data are a hazard

A split script turns values into file names. That is a trust decision you may not have meant to make. The awk one-liner builds a path from the data, and a value that contains a slash or .. controls where the file goes. I tested it on a two row file where one value is ../escaped:

rm -rf /tmp/trav && mkdir -p /tmp/trav/out && cd /tmp/trav/out
printf 'id,dest\n1,ok\n2,../escaped\n' > ../in.csv
awk -F, 'NR > 1 { print $1 > ($2 ".csv") }' ../in.csv
ls -R /tmp/trav | head -n 12

/tmp/trav:
escaped.csv
in.csv
out

/tmp/trav/out:
ok.csv

The script ran inside /tmp/trav/out and wrote ok.csv there, as expected. It also created /tmp/trav/escaped.csv, one directory up, outside the output folder. A value with enough ../ segments, or an absolute path like /etc/..., reaches anywhere the user running the script can write. If the column comes from anything an outsider can influence, such as a customer-supplied field, that is a path traversal bug in your script.

qsv sanitizes the names:

rm -rf /tmp/trav2 && mkdir -p /tmp/trav2/out
printf 'id,dest\n1,ok\n2,../escaped\n3,a/b\n' > /tmp/trav2/in.csv
qsv partition dest /tmp/trav2/out /tmp/trav2/in.csv 2>&1 | head -n 3
find /tmp/trav2 -type f | sort

/tmp/trav2/in.csv
/tmp/trav2/out/ab.csv
/tmp/trav2/out/escaped.csv
/tmp/trav2/out/ok.csv

For the same data plus a value a/b, qsv partition wrote ok.csv, escaped.csv and ab.csv, all inside the output folder: the slashes were removed. I did not test every character, so check the behavior for the values your data can contain. The safe pattern for an awk script is to allow only a known set of values: test $7 ~ /^[A-Z]{2}$/ before using it and send the rest to a rejects file. A split key should be a code you recognize, never free text.

Many values

The one-liners above split by a column with 7 values. A column with tens of thousands of values is a different job, because it means tens of thousands of files, and the tool has to open and close them as it goes. I tried awk on 40,000 rows split by customer_id, which gave about 37,000 files, and it took 90 seconds in one run and 122 seconds in another, where the 7-file splits of 2,000,000 rows above took between 0.6 and 2.1 seconds with qsv, DuckDB and Miller. I did not try the other tools at that size. A directory with tens of thousands of small files is also awkward for everything that touches it afterwards. If you find yourself splitting by an identifier, ask what the files are for. A single file sorted by that column, a database, or a partitioned Parquet dataset serves most purposes better.

How to tell it worked

Count: the sum of rows across the files equals the source rows, and the line total is that plus one header per file. Compare a checksum of one partition across two methods. Confirm every file begins with the header. List the output folder and check that no file landed outside it. And look at the smallest file, because a typo in the source ("Fance" for "France") appears as a tiny extra file.

When splitting makes the problem worse

When the pieces will be put back together, such as a split for parallel processing: keep the order information and write a manifest. When the consumer can read the whole file, a filter at read time is simpler than a folder of copies that drift apart. When the split key has many values, as above. And when the files are the product, for example one per customer for a mailing, put the file name rules in the script, validate the key, and keep the log of what was written.