How to Extract Columns From CSV With Quoted Commas

CSV looks like text split by commas until a field contains a comma, a quote or a newline. Then cut and awk -F, quietly produce wrong columns, and the wrong data flows into the next step. The right tool depends on what is installed and what the file contains. The examples use csvkit 2.0, Miller 6.11, gawk 5.3 and Python 3.12; the simpler splitting tools are in cut and paste.

Why cut is wrong here

cat people.csv
cut -d, -f1,2 people.csv

name,city,age
"Smith, Jo",Paris,31
Lee,"New York",28
"Smith

The second row is split inside the quoted name, so column 2 becomes Jo" instead of the city. cut knows nothing about quotes, and a quoted field with an embedded newline spans two lines, so line tools cannot represent it at all. If the file is guaranteed to have no quoted commas, cut is fine and fast; otherwise use a parser.

csvkit

csvcut -c name,city people.csv
csvcut -c 1,3 people.csv
csvcut -n people.csv

name,city
"Smith, Jo",Paris
Lee,New York

csvcut selects columns by name or number and writes valid CSV, quoting where needed. -n lists the column names with their positions. csvkit is installed with pip install csvkit and is slower than the streaming tools on large files since it is written in Python, but its family of commands, csvgrep, csvsort and csvjson, is convenient; see csvkit and Miller.

Miller

mlr --icsv --ocsv cut -f name,city people.csv
mlr --icsv --opprint cut -o -f city,name people.csv
mlr --icsv --ojson cut -x -f age people.csv

Miller is a single binary that streams, so it handles files bigger than memory. cut -f selects fields by name, -o keeps the order you list, and -x excludes the named fields. Output formats are one flag: CSV, aligned text or JSON. It treats the first line as a header by default, and --implicit-csv-header turns that off for header-less files.

awk with FPAT

gawk -v FPAT='([^,]*)|("[^"]+")' -v OFS=, '{print $1, $2}' people.csv

In gawk, FPAT describes what a field looks like instead of what separates fields, so a quoted field including commas stays whole. It does not remove the quotes, and does not cope with escaped quotes ("") or newlines inside fields. It is a quick fix for well-behaved files, and is a gawk extension, absent from mawk and BSD awk, as in awk and CSV.

Python as the fallback

python3 -c '
import csv, sys
w = csv.writer(sys.stdout)
for row in csv.reader(sys.stdin):
    w.writerow([row[0], row[1]])
' < people.csv

The csv module handles quotes, doubled quotes and embedded newlines, and exists wherever Python does. Use csv.DictReader to select by header name. Mind encodings: a file from Excel may carry a byte order mark, so read it with encoding="utf-8-sig".

What the CSV format allows

RFC 4180 describes the common form: records are separated by line breaks, fields by commas, and a field that contains a comma, a double quote or a line break must be enclosed in double quotes, with any quote inside it doubled. Real files add variations. Many tools write semicolons as separators in locales where the comma is the decimal mark, tab-separated files use the same rules with a different delimiter, some exports use a backslash escape instead of doubling, and a byte order mark or a Windows line ending may start or end the file. A reader must know which variant it is looking at. A one-line shell command that suits a clean sample file will therefore fail on the first export from a different system, and the right first step is to look at the data with head and a hex viewer if anything seems off.

Embedded line breaks are the case that defeats every line-oriented tool. A quoted field may span several physical lines, so wc -l does not count records, head -n 5 may cut in the middle of one, and sort shuffles the pieces apart. Only a real CSV parser keeps the structure intact.

Tools that understand quoting

Several command-line tools parse CSV properly. csvkit provides csvcut, csvgrep, csvsort and csvlook, working by header names: csvcut -c name,age people.csv. Miller, installed as mlr, handles CSV, TSV and JSON with a rich verb set, and is fast: mlr --icsv --opprint cut -f name,age then sort -nr age people.csv. xsv and its fork qsv are written in Rust and fast on files of many gigabytes, with commands to select, search, join and compute statistics. GNU awk 5.3 has a --csv option that understands quoted fields, which makes quick extractions possible without extra software: gawk --csv '{print $1, $3}' people.csv. Check that your version has it before depending on it.

Using a scripting language for more

For logic beyond selecting columns, a few lines of Python with the csv module are clearer and safer than a shell pipeline. csv.DictReader gives each row as a dictionary keyed by header, handles quoting and line breaks, and works with generators so memory stays flat. The module also writes output with correct quoting, which matters when the result goes to another system. For data analysis, pandas or polars load the file and give columns, types and aggregation, at the cost of reading the data into memory unless streamed. Choose by the size and the job: a shell tool for a quick look, a CSV-aware command for repeated extraction, and Python for anything with conditions.

Cleaning problems before parsing

Many failures are caused by the file, not the tool. Remove a byte order mark if the first header looks odd, normalise line endings with dos2unix or tr -d '\r' when carriage returns appear inside values, and check the encoding with file -i, converting with iconv -f latin1 -t utf-8 if needed. Count fields per record with a parser, not with awk -F,, and look for rows with a different count, which signal unbalanced quotes. Keep the original file unchanged and write the cleaned copy elsewhere, so a mistake in the cleaning can be redone.

Writing CSV from the shell

Producing CSV has the same hazards in reverse. Building lines with echo "$name,$city" breaks the moment a value holds a comma or a quote. Quote each field by wrapping it in double quotes and doubling the inner quotes: q() { local s=${1//\"/\"\"}; printf '"%s"' "$s"; }, then join with commas. Better, generate the output with jq -r '[.name,.city]|@csv' from JSON, or with Miller, which writes valid CSV from any input. Guard against formula injection if the file will be opened in a spreadsheet: values that start with an equals sign, a plus, a minus or an at sign can be read as formulas, so prefix them with an apostrophe or a space when the data comes from users.

Where this does not apply

CSV has variants: semicolon separators in some locales, tabs, and different quoting rules. State the dialect to the tool, for example csvcut -d ';' or mlr --icsv --ifs ';'. When the data is going to a database, load it with the database's own importer instead of reshaping it in the shell. The format is described in RFC 4180, and the tools in the csvkit documentation and Miller documentation.