Working With CSV Files in the Shell Without Opening Excel

The shell is a good place to look at a CSV and a risky place to change one. The tools are quick, they are already installed, and they compose into one line what would take a script. They also come from a time when a text file was lines and delimiters, and a CSV is neither: a field can contain a comma, a quote or a line break. This guide is the one I would give a colleague before they pipe an export through cut. It sets out the one rule that decides which tools are safe, a triage script for the first minutes with an unknown file, a map of the tools with timings on 2,000,000 rows, the encoding and line-ending chores, how to make pipelines that fail loudly, and a table that sends each symptom to the article that fixes it.

Every command was run and every number is the number I got. The format that all of these tools are being measured against is RFC 4180, and the GNU tools are documented in the coreutils manual.

The one rule: is anything quoted?

CSV as RFC 4180 defines it allows a field to contain the delimiter, a double quote or a line break, if the field is wrapped in quotes. The classic Unix tools do not know that. cut, sort -t,, uniq, awk -F, and join treat every comma as a separator and every line break as the end of a record. On a file where no field is ever quoted, that is exactly right. On a file where one is, they get it wrong without saying so, and the results look like CSV.

So the first command on any file you did not write is a one-line test:

grep -c '"' file.csv

Zero means there are no quotes anywhere, so there are no quoted commas and no embedded newlines, and the line tools are exact. Anything else means you are in the other case. The orders file used in this series returns 0. The small file used for the demonstrations has two lines with quotes, and every line tool I ran on it gave a wrong answer for the same row.

The same rule from a different angle: how many fields does each line have? An awk histogram answers it, and a uniform answer is a good sign:

awk -F, '{print NF}' file.csv | sort -n | uniq -c

On the small file it printed 8 lines with 6 fields and 1 line with 7; the one with 7 is the "Smith, Jane" record. It does not prove the file is clean (a file with a quoted comma in every row has a uniform wrong width), which is why the grep is the real test. The articles on cut and awk show the failure in detail.

The first ten minutes with an unknown file

Before filtering or counting, look at the file's bytes. Five properties decide whether the rest of the work will behave: whether there is a byte order mark, whether the text is valid UTF-8, what the line endings are, whether there is quoting, and what shape the records have. This script prints all of them, plus the record count and header:

#!/usr/bin/env bash
# First look at an unknown CSV: encoding marks, line endings, quoting, shape, then a profile.
set -euo pipefail
f=$1
printf '== %s\n' "$(basename "$f")"
printf 'size            : %s bytes\n' "$(wc -c < "$f" | tr -d ' ')"
printf 'BOM             : '; if [ "$(head -c 3 "$f" | xxd -p)" = "efbbbf" ]; then echo "UTF-8 BOM"; else echo none; fi
printf 'valid UTF-8     : '; if iconv -f UTF-8 -t UTF-8 "$f" >/dev/null 2>&1; then echo yes; else echo NO; fi
printf 'CRLF lines      : %s\n' "$(grep -c $'\r$' "$f" || true)"
printf 'quote characters: %s lines\n' "$(grep -c '"' "$f" || true)"
printf 'lines / records : %s / %s\n' "$(wc -l < "$f" | tr -d ' ')" "$(qsv count "$f")"
printf 'field widths    : '; awk -F, '{print NF}' "$f" | sort -n | uniq -c | awk '{printf "%s lines x %s fields; ", $1, $2}'; echo
printf 'header          : '; qsv headers --just-names "$f" | paste -sd, -

wc -l counts lines and qsv count counts records; if they differ by more than the header, fields contain line breaks. The iconv call is a validity test: converting UTF-8 to UTF-8 fails on an invalid byte. Run on the big file:

/batches/csv-0001-0200/lab/sh/triage.sh orders-2m.csv

== orders-2m.csv
size            : 129919987 bytes
BOM             : none
valid UTF-8     : yes
CRLF lines      : 0
quote characters: 0 lines
lines / records : 2000001 / 2000000
field widths    : 2000001 lines x 8 fields; 
header          : order_id,customer_id,zip,amount,created_at,status,country,note

129.9 MB, no BOM, valid UTF-8, no CRLF, no quotes, 2,000,001 lines and 2,000,000 records, every line with 8 fields. This is the best case, and everything in the shell works on it. Run on the eight row orders file:

/batches/csv-0001-0200/lab/sh/triage.sh orders-small.csv

== orders-small.csv
size            : 287 bytes
BOM             : none
valid UTF-8     : yes
CRLF lines      : 0
quote characters: 2 lines
lines / records : 9 / 8
field widths    : 8 lines x 6 fields; 1 lines x 7 fields; 
header          : id,customer,city,qty,price,status

Two lines contain quotes. The widths are 8 lines x 6 fields; 1 lines x 7 fields, and there are 9 lines for 8 records and a header, so no line breaks inside fields. Every line tool is now suspect, for the one line with 7. The people file has a different problem:

/batches/csv-0001-0200/lab/sh/triage.sh people.csv

== people.csv
size            : 140 bytes
BOM             : none
valid UTF-8     : yes
CRLF lines      : 0
quote characters: 3 lines
lines / records : 6 / 4
field widths    : 1 lines x 1 fields; 4 lines x 4 fields; 1 lines x 5 fields; 
header          : id,name,city,note

lines / records : 6 / 4 is the signature of a field with a line break, and the widths 1 lines x 1 fields; 4 lines x 4 fields; 1 lines x 5 fields show how a line tool will see the file: a line with one field, which is the second half of a note, and a line with five. A Windows export with a byte order mark:

printf 'id,name\r\n1,Ana\r\n2,Bo\r\n' | sed '1s/^/\xef\xbb\xbf/' > /tmp/bom-crlf.csv
/batches/csv-0001-0200/lab/sh/triage.sh /tmp/bom-crlf.csv

== bom-crlf.csv
size            : 25 bytes
BOM             : UTF-8 BOM
valid UTF-8     : yes
CRLF lines      : 3
quote characters: 0 lines
lines / records : 3 / 2
field widths    : 3 lines x 2 fields; 
header          : id,name

BOM: UTF-8 BOM and CRLF lines: 3. Neither is visible in the preview, and both break things: the first header becomes id to a program that does not strip the BOM, and the last field of every record ends in a carriage return. And a Latin-1 file:

/batches/csv-0001-0200/lab/sh/triage.sh latin1.csv

== latin1.csv
size            : 74 bytes
BOM             : none
valid UTF-8     : NO
CRLF lines      : 0
quote characters: 0 lines
lines / records : 4 / 3
field widths    : 4 lines x 3 fields; 
header          : id,name,city

valid UTF-8: NO, so decoding as UTF-8 will fail or produce garbage, and the encoding has to be found before anything else, as the encoding detection article explains. Five files, five sets of facts, from one short script.

Looking at the file

A preview is the next step, and it is where the quoting rule shows up first. head -n 20 file.csv | column -t -s, is quick and correct on a clean file and wrong on a quoted one: it splits "Smith, Jane" in two and shifts the row. It also prints UTF-8 as escapes unless the locale is set, and wrapping at the screen width turns a wide table into noise, which less -S fixes. csvlook, qsv table and mlr --icsv --opprint parse quotes and are correct, at different speeds: csvlook needed 0.62 s for 1,000 lines, qsv table 0.40 s, and Miller's head stopped early and took 0.03 s on the full 2,000,000 row file. The details and the traps are in the preview article.

What each tool is good for

There are six tools in this series, and they overlap. I use them for different things.

• coreutils (cut, sort, uniq, wc, join, paste): the fastest to type and, on unquoted data, the fastest to run. wc -l counted 2,000,001 lines in 0.09 s. They have no idea what a header is.

• awk: for anything that needs arithmetic or a condition on a line. A grouped sum was one line, 1.01 s. The same quoting limits, and FPAT helps with commas inside quotes but not with line breaks or doubled quotes.

• csvkit: a full set of commands with a real parser, and the most complete format converter (in2csv). Slow: csvstat on one column of 200,000 rows took 59 s, csvsort 61 to 65 s. Type inference turned a ZIP code into 2134.0 in its JSON output. See the csvkit article.

• Miller: expressions, then chains and a good set of verbs. Moderate speed, and defaults to know about: an empty cell in arithmetic returned the other operand, and 0x1F became 31. See the Miller article.

• qsv: the fast CSV-aware toolkit. count, stats, frequency, slice, search, sort and partition all in a fraction of a second. It writes cache and index files beside the input. See the qsv article.

• DuckDB: not a CSV tool in the strict sense, but one duckdb -c "SELECT ..." command over the file answers most questions in the least time. See the DuckDB guide.

I ran the same five jobs with each tool that can do them, three times each, and took the best:

bash /batches/csv-0001-0200/lab/sh/bench_matrix.sh

job: count records
coreutils wc -l (lines)           0.09s
qsv       qsv count               0.21s
mlr       mlr count               1.06s
duckdb    duckdb count            0.20s
job: select two columns
coreutils cut                     0.26s
awk       awk                     1.00s
qsv       qsv select              0.27s
mlr       mlr cut                 1.15s
duckdb    duckdb copy             0.29s
job: filter status=refunded and amount>500, count
awk       awk                     0.75s
qsv       qsv search              0.32s
mlr       mlr filter              1.11s
duckdb    duckdb where            0.21s
job: sum amount by country
awk       awk                     1.01s
mlr       mlr stats1              1.10s
duckdb    duckdb group by         0.22s
job: top 3 by amount
coreutils sort | head             1.14s
qsv       qsv sort                1.47s
mlr       mlr sort                2.50s
duckdb    duckdb order by         0.27s

Reading the table:

• Counting. wc -l took 0.09 s and counts lines. qsv count took 0.21 s and counts records, which is the number you want when a field may contain a line break. DuckDB took 0.20 s and Miller 1.06 s.

• Selecting two columns. cut 0.26 s, qsv 0.27 s, DuckDB 0.29 s, awk 1.00 s and Miller 1.15 s. The quoting-aware qsv costs nothing here.

• Filtering to refunded orders over 500: awk 0.75 s, qsv 0.32 s as two chained search commands, Miller 1.11 s and DuckDB 0.21 s.

• Grouping the amount by country: awk 1.01 s, Miller 1.10 s and DuckDB 0.22 s.

• Sorting for the top three: sort 1.14 s, qsv 1.47 s, Miller 2.50 s and DuckDB 0.27 s.

Two patterns. DuckDB is the fastest on everything that is not a pure line count, which is why I now use it by default for questions. And the CSV-aware tools are not slower than coreutils in any way that matters at this size, so there is little reason to accept the quoting risk for speed alone. The reason to keep using cut and sort is that they are already there and take no thought on files you know.

A caution on the numbers: the same Miller sort took 6.54 s in a separate run for the sorting article, and the same qsv sort took 2.76 s and 3.4 s in the qsv article. Timings on a shared machine move by a factor of two or three. The ordering of the tools is more stable than the digits.

Counting, sorting, deduplicating, splitting

The four chores that come up on every file each have a shell answer and a trap:

• Counting values is cut -d, -fN | tail -n +2 | sort | uniq -c | sort -nr, or qsv frequency. The traps are the header (leave out tail -n +2 and the column name is counted as a value) and spelling: paid, Paid and paid counted as separate values, and four of six rows were the same word after lowercasing and trimming. See counting rows and unique values.

• Sorting needs the header kept out, -n for numbers, and a fixed locale: (head -n 1 f; tail -n +2 f | LC_ALL=C sort -t, -k4,4 -n -r). A text sort puts 100 before 2, and a quoted comma shifts the key. See sorting by column.

• Deduplicating exact rows is awk '!seen[$0]++', which keeps the header and the order; sort -u moves the header to the bottom. Keyed on a column with awk, it dropped 2,"Smith, John" because the quoted comma made its key "Smith. See removing duplicate rows.

• Splitting by a column value is qsv partition country outdir file.csv (0.79 s for 2,000,000 rows into 7 files, each with a header), and the awk one-liner writes no headers and trusts the data with file names. See splitting a CSV by column value.

Joining two files

join in coreutils matches sorted files on a key field, and the quoting rule applies again. Joining the orders to a small table of status labels on the status column:

join -t, -1 6 -2 1 <(tail -n +2 orders-small.csv | sort -t, -k6,6) <(tail -n +2 status.csv | sort -t, -k1,1) | cut -d, -f2 | sort | tr '\n' ' '
echo
mlr --icsv --ojson join -j status -f status.csv then cut -f id orders-small.csv | grep -c '"id"'

1 3 4 5 7 8 
7

The first line is the ids that the coreutils join matched: 1 3 4 5 7 8, six of them. The second is the number Miller's join produced: 7. Order 2, the "Smith, Jane" row, is missing from the coreutils result. Its status field was not field 6 after the split, so it sorted into the wrong place and never met its label. The other tools handle it:

mlr --icsv --opprint join -j status -f status.csv then cut -f id,customer,status,label orders-small.csv

status   label          id customer
paid     Paid in full   1  Ana Silva
refunded Money returned 2  Smith, Jane
paid     Paid in full   3  Bo Chen
pending  Waiting        4  Cy Dubois
paid     Paid in full   5  Doe "JD" John
paid     Paid in full   7  Flo
refunded Money returned 8  Gus

qsv join status orders-small.csv status status.csv | qsv select id,customer,status,label | qsv table

id  customer           status    label
1   Ana Silva          paid      Paid in full
2   Smith, Jane        refunded  Money returned
3   Bo Chen            paid      Paid in full
4   Cy Dubois          pending   Waiting
5   "Doe ""JD"" John"  paid      Paid in full
7   Flo                paid      Paid in full
8   Gus                refunded  Money returned

Miller and qsv both returned all seven matched orders with their labels, including Smith, Jane (qsv's table shows the doubled quotes for the other name in its CSV-escaped form). And the reconciliation question, which rows had no match:

mlr --icsv --opprint join --np --ur -j status -f status.csv then cut -f id,customer,status orders-small.csv

id customer  status
6  Ed Zürich failed

--np --ur printed only the unpaired rows from the input stream: order 6, status failed, which has no entry in the label table. In Miller the -f file is the left side, so --ur is the right (input) stream's unpaired records, a flag I got wrong on my first run and which printed nothing. For joins beyond a lookup, SQL is the better language, and the DuckDB join article shows it.

Putting several files together

Monthly exports and per-region files arrive as many CSVs with the same columns, and the shell tool for combining files is cat, which is wrong in two ways. Each file's header comes along:

cat monthly/sales-2025-0[1-3].csv | wc -l
cat monthly/sales-2025-0[1-3].csv | grep -c '^sale_id'

1503
3

Three files of 500 records made 1,503 lines, and grep -c '^sale_id' found three headers in the middle of the data. Strip the header from every file but the first with awk's per-file line counter, FNR, against the global one, NR:

awk 'FNR == 1 && NR != 1 { next } { print }' monthly/sales-2025-0[1-3].csv | wc -l

1501

FNR == 1 && NR != 1 { next } skips the first line of every file except the first. The result is 1,501 lines: 1,500 records and one header. qsv has a command for it, which parses the files properly and counts 1,500 records:

qsv cat rows monthly/sales-2025-0[1-3].csv | qsv count

1500

The second way cat goes wrong is the file without a final newline. Writers differ, and a file whose last line has no line feed is joined to the first line of the next:

printf 'sale_id,sku\nA1,x\nA2,y' > /tmp/nonl.csv
printf 'sale_id,sku\nB1,z\n' > /tmp/after.csv
echo "--- cat"; cat /tmp/nonl.csv /tmp/after.csv | tail -n +2
echo "--- awk"; awk 'FNR == 1 && NR != 1 { next } { print }' /tmp/nonl.csv /tmp/after.csv | tail -n +2

--- cat
A1,x
A2,ysale_id,sku
B1,z
--- awk
A1,x
A2,y
B1,z

With cat, the last record of the first file and the header of the second ran together as A2,ysale_id,sku, a corrupt line that nothing reports. The awk version printed A1,x, A2,y, B1,z correctly, because awk adds its own newline to every line it prints. The SQLite loading article shows the same bug from the database side, and why a loop that handles one file at a time avoids it.

Knowing which file a row came from is worth a column. Miller's FILENAME variable adds it:

mlr --icsv --opprint put '$source = FILENAME' then head -n 1 -g source then cut -f sale_id,source monthly/sales-2025-0[1-3].csv

sale_id      source
2025-01-0001 monthly/sales-2025-01.csv
2025-02-0001 monthly/sales-2025-02.csv
2025-03-0001 monthly/sales-2025-03.csv

The first record of each month with its source value. When a total looks wrong, that column tells you which month to open.

Files that do not have exactly the same columns are the hard case, and the tools differ in how badly they fail. I combined the January file (four columns) with the April file (the same four plus channel). With the wider file first, Miller filled the missing channel for January with an empty value, which is what you want. With the narrower file first:

mlr --icsv --ocsv cat monthly/sales-2025-01.csv monthly/sales-2025-04.csv > /tmp/merged.csv 2> /tmp/merged.err; echo "exit: $?"
echo "stderr: $(cat /tmp/merged.err)"
echo "lines: $(wc -l < /tmp/merged.csv)"
awk -F, '{print NF}' /tmp/merged.csv | sort -n | uniq -c
head -n 1 /tmp/merged.csv

exit: 0
stderr: 
lines: 503
    501 4
      2 5
sale_id,sku,qty,price

Miller exited 0 with nothing on standard error, and wrote 503 lines: the header with 4 fields, 501 lines with 4 fields and two lines with 5. It produced a ragged CSV, with the April rows carrying a field the header does not name. Nothing flagged it. The fix is to ask for the union of the columns:

mlr --icsv --ocsv cat monthly/sales-2025-01.csv monthly/sales-2025-04.csv 2>&1 | tail -n 6
echo "--- unsparsify"
mlr --icsv --ocsv unsparsify monthly/sales-2025-01.csv monthly/sales-2025-04.csv | tail -n 3

2025-01-0497,B-007,6,15.45
2025-01-0498,B-007,6,29.29
2025-01-0499,B-010,2,39.9
2025-01-0500,B-007,3,55.79
2025-04-0001,A-100,3,9.90,web
2025-04-0002,B-007,1,12.00,store
--- unsparsify
2025-01-0500,B-007,3,55.79,
2025-04-0001,A-100,3,9.90,web
2025-04-0002,B-007,1,12.00,store

The first part, plain cat, shows the January rows with four fields followed by April rows with five. After unsparsify, the same last rows read 2025-01-0500,B-007,3,55.79, with an empty fifth field and then the April rows with web and store. unsparsify has to read all the records before it can print the first, so it is not streaming, which is the price of knowing the full set of columns. DuckDB's union_by_name, in the folder article, does the same job in one option. Whatever you use, finish by checking the field-width histogram of the result: one line, one width.

Encodings, BOMs, line endings and compression

These chores are small and each one silently breaks something. Strip a byte order mark and carriage returns in one pass:

printf '\xef\xbb\xbfid,name\r\n1,Ana\r\n' > /tmp/b.csv
head -n 1 /tmp/b.csv | xxd | head -n 1
sed '1s/^\xef\xbb\xbf//' /tmp/b.csv | tr -d '\r' | head -n 1 | xxd | head -n 1

00000000: efbb bf69 642c 6e61 6d65 0d0a            ...id,name..
00000000: 6964 2c6e 616d 650a                      id,name.

The first line of the dump is ef bb bf 69 64 2c 6e 61 6d 65 0d 0a: BOM, id,name, CRLF. After sed '1s/^\xef\xbb\xbf//' and tr -d '\r' it is 69 64 2c 6e 61 6d 65 0a, plain id,name and a line feed. Do this before comparing, joining or counting distinct values on a file from Windows; the BOM article and the line endings article explain where each comes from.

Converting an encoding is iconv:

iconv -f CP1252 -t UTF-8 cp1252.csv | head -n 3

id,name,city,note
1,José Müller,São Paulo,price €4.50
2,Zoë Dupré,Zürich,“smart quotes”

iconv -f CP1252 -t UTF-8 turned a Windows file into readable José Müller, São Paulo, price €4.50 and curly quotes. The -f argument is the part you must get right: a wrong source encoding produces output that looks plausible and is not. The method for finding the right one is in the detection article and the conversion details in the Latin-1 and Windows article.

For compressed files, you do not need to unpack to disk. zcat streams the file to any tool that reads standard input:

cd /batches/csv-0001-0200/work
time (gzip -c orders-2m.csv > /tmp/orders-2m.csv.gz)
ls -l /tmp/orders-2m.csv.gz | awk '{printf "%.1f MB\n", $5/1e6}'
zcat /tmp/orders-2m.csv.gz | qsv frequency -s status -l 2

real	0m5.069s
user	0m4.714s
sys	0m0.151s
42.1 MB
field,value,count,percentage,rank
status,pending,400329,20.01645,1
status,refunded,400135,20.00675,2
status,Other (3),1199536,59.9768,0

Compressing the 2,000,000 row file took 5.1 s and made a 42.1 MB archive, and zcat | qsv frequency -s status -l 2 read it and printed the two most common statuses, pending 400,329 and refunded 400,135, plus Other (3). The pipe costs the decompression time, and it saves the disk space and the cleanup.

Validate before you trust

The triage script reports shape. A validator answers the question "is this a well-formed CSV" and exits non-zero when it is not, which is the form a script needs. qsv has one:

qsv validate py/ragged2.csv; echo "exit: $?"
echo ---
qsv validate sh/orders-small.csv; echo "exit: $?"

Validation error: CSV error: record 2 (line: 3, byte: 41): found record with 5 fields, but the previous record has 4 fields.
Use `qsv fixlengths` to fix record length issues.
exit: 1
---
Valid: 6 Columns: ("id", "customer", "city", "qty", "price", "status"); Records: 8; Delimiter: ,
exit: 0

On the ragged file from the Python column counts article, qsv validate stopped at the first problem with record 2 (line: 3, byte: 41): found record with 5 fields, but the previous record has 4 fields and exited 1. On the eight row orders file it printed Valid: 6 Columns and Records: 8 and exited 0. It checks structure, not content, so a well-formed file with nonsense in the columns passes, but it catches the broken quote and the short row before they reach a database. The message even names the line and the byte offset, which is what you need to open the file in the right place.

Miller does the same check as a side effect of reading, and stops:

mlr --icsv --ojson cat py/ragged2.csv > /dev/null; echo "exit: $?"

mlr: CSV header/data length mismatch 4 != 5 at filename py/ragged2.csv row 3
exit: 1

mlr: CSV header/data length mismatch 4 != 5 at filename py/ragged2.csv row 3, exit status 1. That is a strict default, and a good one, though as the combining section showed, its writer is less strict than its reader. In a pipeline, one of these on the input is a cheap guard: if the file is malformed, nothing downstream runs.

The second guard is to compare a result with an independent count. This script keeps the refunded orders with qsv and checks the row count against a DuckDB query on the original, and fails if they differ:

set -euo pipefail
in=orders-2m.csv
want=$(duckdb -list -noheader -c "SELECT count(*) FROM '$in' WHERE status = 'refunded'")
qsv search -s status '^refunded$' "$in" > /tmp/refunded.csv
got=$(qsv count /tmp/refunded.csv)
[ "$want" = "$got" ] && echo "ok: $got refunded rows, matches the independent count" || { echo "MISMATCH: want $want got $got"; exit 1; }

ok: 400135 refunded rows, matches the independent count

It printed ok: 400135 refunded rows, matches the independent count. The point is the independence: two tools that parse the file differently agreeing on a number is evidence that neither is silently dropping or splitting rows. Put a check like this at the end of every job that rewrites a file, and a mismatch will surface as a failed run instead of a wrong report.

Pipelines that fail loudly

A shell pipeline reports the exit status of its last command, and the first command can fail without anyone noticing:

bash -c 'set -o pipefail; cat /nonexistent.csv | wc -l; echo "exit: $?"'
bash -c 'cat /nonexistent.csv | wc -l; echo "exit: $?"'

cat: /nonexistent.csv: No such file or directory
0
exit: 1
cat: /nonexistent.csv: No such file or directory
0
exit: 0

The first block, with set -o pipefail, printed the error from cat, a count of 0, and exit: 1. The second, without it, printed the same error and count and exit: 0. A script that counts rows from a file that does not exist and exits successfully is how a monitoring job reports "0 errors" every night. The header of every script I write for CSV work is:

set -euo pipefail
export LC_ALL=C

-e stops at the first failing command, -u makes an unset variable an error, pipefail as above, and LC_ALL=C fixes the sort order and the byte semantics of the text tools so that the script gives the same result on every machine. Other habits that cost nothing: write results to a temporary file and mv it over the destination, never redirect a command's output onto its own input (sort f > f empties the file), quote every variable that holds a path, and print the record count at the start and end of every stage. If the numbers do not match what you expect, the pipeline tells you at the stage where it went wrong.

Versions and portability

Everything above was run with the versions in the callout, and several of the behaviors are version-specific. The gawk here rejected --csv; column here kept empty cells where other implementations are reported to merge them; csvclean in csvkit 2.2.0 did not accept the -n flag that older documentation shows; Miller's CSV writer wrote a ragged file where other releases may stop with an error. None of that is a complaint about the tools. It is the reason a script that matters should print the versions it ran with in its log (qsv --version, mlr --version, awk --version | head -n 1), and the reason a command copied from a blog post, including this one, is worth running once on a copy of your data before it touches the real file. If a script has to run on both Linux and macOS, test it on both, since the BSD sort, cut, column and sed differ from the GNU versions in the flags they accept, and install the GNU or CSV-aware tools explicitly instead of relying on what is on the machine.

Symptom, cause, fix

How to tell it worked

Compare record counts at the start and the end of each stage with a CSV-aware tool. Cross-check one aggregate with a second tool, for which DuckDB is quick. Look at the rows with a comma, a quote and a line break, because they are the rows the pipeline is most likely to damage. Read the output's first bytes for a BOM or CR. And keep the commands in a script, with the triage output in the log, so that the next run on next month's file starts with the same checks.

When the shell is the wrong place

When the logic has more than three steps, write a script in a language that parses CSV properly, as in the Python guide, and test it with the nasty values. When the job is a join across several tables, a window function or anything that wants SQL, say it in SQL: duckdb -c is a shell command too, and the first DuckDB article shows how little it takes. When the data is for people who will open it in a spreadsheet, the work is in the encoding and line endings, and the shell is where you check them. And when the same problems recur from the same source, the best tool is a conversation with whoever writes the file.