Selecting CSV Columns With cut and Why Quotes Break It

cut is the first tool most of us reach for to pull a column out of a CSV. It is fast, it is everywhere, and on the right file it is perfect. On the wrong file it is a quiet source of bad data: it never errors and never warns, and what it returns looks like a CSV. This article shows the mechanism of the failure on a small file, how to detect it before you trust a result, and what to use instead. It is part of the tour in working with CSV in the shell.

What cut does

cut -d, -f2 reads each line, finds the commas and prints the second piece (the GNU manual lists every option). That is the entire algorithm. It does not know what a quote is. A CSV file, as RFC 4180 defines it, lets a field contain a comma if the field is quoted, and cut has no way to tell a comma that separates fields from a comma that is data.

A small file with a quoted comma and a doubled quote:

id,customer,city,qty,price,status
1,Ana Silva,Lisbon,3,9.50,paid
2,"Smith, Jane",Boston,10,2.25,refunded
3,Bo Chen,Oslo,2,100.00,paid
4,Cy Dubois,Lyon,,15.75,pending
5,"Doe ""JD"" John",Rome,7,3.10,paid
6,Ed Zürich,Bern,1,8.00,failed
7,Flo,Kyiv,12,1.05,paid
8,Gus,Riga,5,20.00,refunded

Selecting two columns by number:

cut -d, -f1,3 orders-small.csv

id,city
1,Lisbon
2, Jane"
3,Oslo
4,Lyon
5,Rome
6,Bern
7,Kyiv
8,Riga

The header and every row but one is right. Row 2 should be 2,Boston, and it came out as 2, Jane". The field list asked for fields 1 and 3, and for this line field 3 is the second half of the name. The row has seven comma-separated pieces, not six, because "Smith, Jane" contains one. The same thing selecting the name column on its own:

cut -d, -f2 orders-small.csv

customer
Ana Silva
"Smith
Bo Chen
Cy Dubois
"Doe ""JD"" John"
Ed Zürich
Flo
Gus

"Smith on its own line. The quote that opens the field is there, the quote that closes it is not, and the output is no longer valid CSV. The row with "Doe ""JD"" John" came through intact, and only because it has no comma inside the quotes: doubled quotes are not a problem for cut, since it never looks at them.

The fields shift, and so does everything after

The more dangerous version is not the name column. It is every column to the right of the quoted field, which are all off by one for that row. A report summing price with cut -d, -f5 would be adding 10 (the quantity) for row 2 where it should add 2.25, and nothing would flag it. A file with multi-line fields is worse:

cut -d, -f2,4 people.csv

name,note
Ana Silva,likes tea
"Smith,Boston
Bo Chen,"line one
line two"
Cy Dubois,

For the people file I asked for fields 2 and 4, the name and the note. The second row gave "Smith,Boston: field 2 is "Smith, field 4 is Boston (the city), where the note should be. The row with the line break printed Bo Chen,"line one and then line two" on a line of its own, and neither has the right shape. cut works on lines, and a record with an embedded newline is two lines to it.

Detect the damage first

The check that costs one command is a histogram of how many fields each line has. awk -F, counts them the way cut does, so if the histogram is uniform, cut and awk agree with the file's true structure:

awk -F, '{print NF}' orders-small.csv | sort | uniq -c

      8 6
      1 7

Eight lines have 6 fields and one has 7. The header and seven rows are fine, and the line with the quoted comma is the seven. A uniform histogram does not prove the file is safe (a file with a quoted comma in every row has a uniform wrong count), so add the second check, the cheapest of all: grep -c '"' file.csv. A count of zero means there is no quoting at all, which means no quoted commas and no embedded newlines, and cut is safe. The 2,000,000 row orders file passes that test, so on it cut is exact.

Two smaller traps

CRLF. A file with Windows line endings leaves a carriage return on the last field:

cut -d, -f3 crlf.csv | od -c | head -n 3

0000000   q   t   y  \r  \n   3  \r  \n   5  \r  \n
0000013

The dump shows q t y \r \n 3 \r \n 5 \r \n: the third column's values are qty\r, 3\r and 5\r. In a terminal it looks fine, because the \r just moves the cursor back; in a comparison or a join it makes 3 not equal 3. Strip the CR first with tr -d '\r' or sed 's/\r$//'. The longer story is in the line endings article.

Characters versus bytes. GNU cut -c selects by position, and in this version the position is in bytes:

printf 'name,city\nZoë,Zürich\n' | cut -c1-6
printf 'name,city\nZoë,Zürich\n' | cut -d, -f1 | od -c | head -n 3

name,c
Zoë,Z
0000000   n   a   m   e  \n   Z   o 303 253  \n
0000012

cut -c1-6 of Zoë,Zürich returned Zoë,Z, which is six bytes, since ë takes two. And a single byte:

printf 'Zoë\n' | cut -c3
printf 'Zoë\n' | cut -c3 | od -c | head -n 2

�
0000000 303  \n
0000002

cut -c3 of Zoë printed a lone byte (octal 303), the first half of ë, which is invalid UTF-8 and shows as a replacement character. For anything with accents or non-Latin text, select by field, never by character position, and be aware that a width-based cut is a byte-based cut.

What to use instead

Three tools read CSV properly and select columns by name or position. All three returned "Smith, Jane" intact, with its quotes restored on output:

csvcut -c id,customer,price orders-small.csv | head -n 4

id,customer,price
1,Ana Silva,9.50
2,"Smith, Jane",2.25
3,Bo Chen,100.00

csvcut -c id,customer,price is csvkit's. qsv's:

qsv select customer,city orders-small.csv | head -n 4

customer,city
Ana Silva,Lisbon
"Smith, Jane",Boston
Bo Chen,Oslo

qsv select customer,city. qsv selects by name, by position, by range, and by exclusion, and it can reorder:

qsv select 'status,id' orders-small.csv | head -n 3; qsv select '!qty,price' orders-small.csv | head -n 3

status,id
paid,1
refunded,2
id,customer,city,status
1,Ana Silva,Lisbon,paid
2,"Smith, Jane",Boston,refunded

qsv select 'status,id' put status first. qsv select '!qty,price' kept everything except those two. And Miller:

mlr --icsv --ocsv cut -o -f customer,id orders-small.csv | head -n 4

customer,id
Ana Silva,1
"Smith, Jane",2
Bo Chen,3

mlr --icsv --ocsv cut -o -f customer,id kept the order given (-o), where plain cut -f keeps the file's order. The people file, with a line break in a field, also survives (for more on the other tools see the csvkit article and the qsv article):

qsv select name,note people.csv

name,note
Ana Silva,likes tea
"Smith, Jane","said ""hello"""
Bo Chen,"line one
line two"
Cy Dubois,

The note with the embedded newline comes through as one record, still quoted, and Smith, Jane and the doubled-quote note are preserved.

Speed

On the 2,000,000 row orders file, selecting two columns:

cd /batches/csv-0001-0200/work
/usr/bin/time -f "cut  %es" bash -c "cut -d, -f4,7 orders-2m.csv > /dev/null" 2>&1 | tail -1
/usr/bin/time -f "qsv  %es" bash -c "qsv select amount,country orders-2m.csv > /dev/null" 2>&1 | tail -1
/usr/bin/time -f "mlr  %es" bash -c "mlr --icsv --ocsv cut -o -f amount,country orders-2m.csv > /dev/null" 2>&1 | tail -1
/usr/bin/time -f "csvcut %es" bash -c "csvcut -c amount,country orders-2m.csv > /dev/null" 2>&1 | tail -1

cut  0.26s
qsv  0.25s
mlr  1.14s
csvcut 3.02s

cut took 0.26 s, qsv select 0.25 s, Miller 1.14 s and csvcut 3.02 s. So the argument for cut is not speed. qsv matches it while understanding CSV, and the choice is whether you have qsv installed. Miller and csvkit are slower and are worth it for what else they do. For a file with no quoting where nothing else is installed, cut is exactly right and I use it without apology.

How to tell it worked

Count the columns in the output with the same awk histogram and confirm they are all the number you asked for. Compare a total, for example awk -F, 'NR>1{s+=$1}END{print s}' over the selected numeric column, with the sum from a CSV-aware tool. Look at the rows you know have quotes in them. And check that the number of output lines equals the number of records, which for a file with embedded newlines it will not.

When cut is the right tool

When grep -c '"' file.csv is zero, the file has Unix line endings, and the content is ASCII or you select by field and not by character. A log-style export, a TSV, a file you generated yourself with a plain writer, the orders file in these articles. In those cases it is the fastest thing on the machine to type and it is as correct as anything else. For everything else, the short list above costs one more word on the command line.