Using awk on CSV Files and Where It Gets Dangerous

awk is the right size of tool for a lot of CSV questions: sum a column, count by group, filter rows on a value. It starts instantly, it is installed everywhere and one line is enough. It is also built on the assumption that fields are separated by a character and nothing else, which CSV is not. This article shows where that assumption holds and awk is exact, where it breaks and awk returns wrong answers without a word, and what the usual fix does and does not cover. The surrounding tour is in working with CSV in the shell.

When awk is exact

A CSV with no quoted fields is a delimited text file, and awk was built for delimited text. Check that you are in that case before anything else:

cd /batches/csv-0001-0200/work
grep -c '"' orders-2m.csv
time awk -F, 'NR > 1 { s += $4 } END { printf "%.2f\n", s }' orders-2m.csv

0
160174646.36

real	0m0.846s
user	0m0.683s
sys	0m0.049s

grep -c '"' printed 0, so the orders file has no quotes at all: no quoted commas, no doubled quotes and no embedded newlines. The sum of column 4, the amount, skipping the header with NR > 1, came to 160174646.36, in 0.85 s of wall time in this run, and 0.69 s in the timing table below. That agrees with every other tool in this series for the same file.

A detail that matters: I used printf "%.2f\n". awk keeps numbers as doubles and prints them with a default format of six significant digits:

cd /batches/csv-0001-0200/work
awk -F, 'NR > 1 { s += $4 } END { print s }' orders-2m.csv
awk -F, 'NR > 1 { s += $4 } END { printf "%.2f\n", s }' orders-2m.csv

1.60175e+08
160174646.36

print s gave 1.60175e+08, a number that has lost its cents, and printf "%.2f" gave 160174646.36. If you print a total with print and a number looks round, that is the format and not the data. Always format money explicitly.

Grouping is awk's best trick, an associative array keyed by the column:

cd /batches/csv-0001-0200/work
awk -F, 'NR > 1 { t[$7] += $4; n[$7]++ } END { for (c in t) printf "%s %d %.2f\n", c, n[c], t[c] }' orders-2m.csv | sort -k3 -nr | head -n 3

FR 286777 22969870.61
IN 286243 22934328.00
BR 285957 22933345.89

France had 286,777 orders worth 22969870.61, then India, then Brazil. The figures match the DuckDB query in the first DuckDB article. The command is one line and ran in under a second on 2,000,000 rows.

Where it silently fails

Add a quoted comma and the field numbers shift. On the eight row file with "Smith, Jane":

awk -F, '{print $2 " | " $3}' orders-small.csv

customer | city
Ana Silva | Lisbon
"Smith |  Jane"
Bo Chen | Oslo
Cy Dubois | Lyon
"Doe ""JD"" John" | Rome
Ed Zürich | Bern
Flo | Kyiv
Gus | Riga

I printed columns 2 and 3, the customer and the city. Row 2 came out as "Smith | Jane": awk considers the name two fields. Every row that does not contain a quoted comma is right, so a quick look at the first screen shows nothing wrong. This is the same failure that cut has, and with awk it is more dangerous because you can do something with the result. Rewriting a column shows it:

awk -F, -v OFS=, 'NR > 1 {$6 = toupper($6)} {print}' orders-small.csv | sed -n 2,4p

1,Ana Silva,Lisbon,3,9.50,PAID
2,"Smith, Jane",Boston,10,2.25,refunded
3,Bo Chen,Oslo,2,100.00,PAID

The command uppercases column 6, the status, and prints each line with the comma as output separator. Rows 1 and 3 became PAID. Row 2 is unchanged, refunded in lowercase, because for that row column 6 is the price 2.25 and the status is column 7. awk modified the wrong field, did it without comment, and wrote out a file that mixes two states. If this had been a $5 = $5 * 1.2 price increase, one row would have had its quantity changed, and no error would have said so.

Header lookup by name has the same problem. Building a map from names to positions is a sound pattern:

awk -F, 'NR == 1 { for (i = 1; i <= NF; i++) col[$i] = i; next } { print $col["price"], $col["status"] }' orders-small.csv | head -n 3

9.50 paid
10 2.25
100.00 paid

It printed the price and status for each row, and the third line, row 2, is 10 2.25 where it should be 2.25 refunded: the position the header gave for price is correct for the header and wrong for a row with a quoted comma. Lookup by name makes the code resilient to reordered columns, and cannot help with a shifted row.

What FPAT fixes

gawk has a second way to define fields. Instead of saying what separates them, FPAT is a regular expression for what a field looks like. The commonly used one is ([^,]*)|("[^"]+"): either any run of non-commas, or a double-quoted string:

awk -v FPAT='([^,]*)|("[^"]+")' '{print $2 " | " $3}' orders-small.csv

customer | city
Ana Silva | Lisbon
"Smith, Jane" | Boston
Bo Chen | Oslo
Cy Dubois | Lyon
"Doe ""JD"" John" | Rome
Ed Zürich | Bern
Flo | Kyiv
Gus | Riga

Row 2 is now "Smith, Jane" | Boston. The field is whole, with its quotes still attached, which you strip with gsub(/^"|"$/, "", $2) if you need the value. For files whose only complication is commas inside quoted fields, this works and is what I would use in a quick script.

It has two limits. The first is line breaks:

awk -v FPAT='([^,]*)|("[^"]+")' '{print NR ": " $2 " | " $4}' people.csv

1: name | note
2: Ana Silva | likes tea
3: "Smith, Jane" | "said ""hello"""
4: Bo Chen | "line one
5:  | 
6: Cy Dubois | 

The note with an embedded newline became two records. Record 4 is Bo Chen | "line one and record 5 has nothing in either field, because awk reads one line at a time and FPAT cannot reach across it. The second limit is doubled quotes next to a comma. A field written "say ""hi, there""" contains a comma, an escaped quote and an escaped quote at the end:

printf 'id,note\n1,"say ""hi, there"""\n2,plain\n' > dq.csv
awk -v FPAT='([^,]*)|("[^"]+")' '{print NF ": " $2}' dq.csv

2: note
3: "say ""hi
2: plain

The first record has 3 fields where the file has 2, and $2 is "say ""hi. The pattern "[^"]+" stops at the first inner quote. A regular expression cannot track the quoting rules of CSV, which need state, and a longer expression only moves the failure to a rarer input.

The csv option, and the CR

Newer gawk releases are documented to have a --csv option that parses CSV properly. The version here, 5.2.1, does not:

awk --csv '{print $2}' orders-small.csv 2>&1 | head -n 2

Usage: awk [POSIX or GNU style options] -f progfile [--] file ...
Usage: awk [POSIX or GNU style options] [--] 'program' file ...

It rejected the option and printed its usage. I could not test the newer option, so I cannot say how it handles embedded newlines or what its output looks like. If you rely on it, check the version on every machine the script runs on.

A CRLF file has its own small trap, the same one as in cut: the last field ends in a carriage return.

awk -F, 'NR > 1 {print $3}' crlf.csv | od -c | head -n 2
awk -F, 'NR > 1 {sub(/\r$/, ""); print $3}' crlf.csv | od -c | head -n 2

0000000   3  \r  \n   5  \r  \n
0000006
0000000   3  \n   5  \n
0000004

The first dump is 3 \r \n 5 \r \n; after sub(/\r$/, "") it is 3 \n 5 \n. A comparison like $3 == 3 is false for the first, and a printf of the last field puts the CR in the middle of a line you build. Strip it as the first action on every line of a file that might come from Windows.

Choosing

For the same sum, here is how long each tool takes on the 2,000,000 row file:

cd /batches/csv-0001-0200/work
for c in "awk -F, 'NR>1{s+=\$4} END{print s}' orders-2m.csv" "qsv stats --select amount orders-2m.csv" "mlr --icsv --ojson stats1 -a sum -f amount orders-2m.csv" "duckdb -c \"select sum(amount) from 'orders-2m.csv'\""; do
  /usr/bin/time -f "%es" bash -c "$c > /dev/null" 2>&1 | tail -1 | tr '\n' ' '; echo "$c" | cut -c1-70
done

0.69s awk -F, 'NR>1{s+=$4} END{print s}' orders-2m.csv
0.22s qsv stats --select amount orders-2m.csv
1.13s mlr --icsv --ojson stats1 -a sum -f amount orders-2m.csv
0.20s duckdb -c "select sum(amount) from 'orders-2m.csv'"

awk 0.69 s, qsv stats on one column 0.22 s, Miller 1.13 s and DuckDB 0.20 s. awk is not the fastest, and it is not far off. What it has that the others do not is that you can do anything on a line: a computation that the specialist tools do not have a verb for is a few lines of awk. What the specialist tools have is a parser that knows what a CSV is.

How to tell it worked

Run grep -c '"' file.csv before you start. If it is not 0, assume awk is wrong until proven otherwise. Compute the field-count histogram, awk -F, '{print NF}' file | sort | uniq -c, and make sure it has one entry. Cross-check the answer with a CSV-aware tool on the same file, one aggregate is enough. Check that the number of output lines matches what you expect for the input.

When awk is the safe choice

When the data is yours, generated by a program that never quotes, with fields that cannot contain commas: ids, numbers, enumerated codes, timestamps. When you control the producer and have written down that fields cannot contain the delimiter, awk is as safe as anything. It is also good for the non-CSV parts of the file: a footer, a title line, a pipe separated feed. For data from outside, assume quotes will arrive one day, and the one-line check at the start is worth its cost. When it fails, the CSV-aware tools in the csvkit, Miller and qsv articles cover the same jobs without the risk. The gawk manual documents FPAT and the other ways awk splits records, and RFC 4180 is the format that these tools are being measured against.