Writing CSV and TSV Output

Part II · Module 7 · Chapter 7.6 · Writing CSV and TSV Output

1. Why This Matters

awk is usually one stage of something longer. The stage after it is often not awk — a spreadsheet somebody opens, a Python script that plots a trend, a dashboard that accumulates a metrics row per night.

Those consumers want a delimited file, and they are unforgiving in a specific way: a row with the wrong number of fields does not raise an error, it shifts every value one column left and produces a plot of the wrong quantity. The whole chapter is about not emitting that row.

2. Why Not Just Align It

Chapter 7.2 produced beautifully aligned tables. They are the wrong output for a program:

u_alu 1820 -0.124
u_io 640 0.5

awk '{ printf "%-10s %6d %8.3f\n", $1, $2, $3 }' timing.rpt

u_alu        1820   -0.124
u_io          640    0.500

A consumer reading that has to split on runs of whitespace and hope no field ever contains a space. It also has to know the column widths, which Chapter 7.2 section 4 showed can change with the data.

A delimiter removes both problems — the field boundaries are explicit and do not depend on width.

3. Comma-Separated Output

Set OFS and let print do the work:

awk 'BEGIN { OFS = ","; print "path,area,slack" } { print $1, $2, $3 }' timing.rpt

path,area,slack
u_alu,1820,-0.124
u_io,640,0.5

The header is written once from BEGIN, which is Chapter 2.4's phase model doing the obvious thing. The rows come from print with commas, which Chapter 7.1 established are OFS positions.

OFS applies to computed values too, not just fields:

awk 'BEGIN { OFS = ","; print "path,area,status" } { print $1, $2 + 0, ($3 + 0 < 0 ? "VIOLATED" : "MET") }' timing.rpt

path,area,status
u_alu,1820,VIOLATED
u_io,640,MET

A derived column alongside the raw ones, which is usually the point of generating the file at all.

4. A Field Containing the Delimiter

Now the failure. One record whose third field contains a comma:

u_alu 1820 see,note

awk 'BEGIN { OFS = ","; print "path,area,note" } { print $1, $2, $3 }' bad.rpt

path,area,note
u_alu,1820,see,note

The header declares three columns. The row has four values.

A spreadsheet opens this without a murmur and puts note in a fourth column. A Python csv.reader returns a 4-element list where the code expects 3, and whether that raises depends entirely on how the code is written — a, b, c = row raises, row[2] silently returns see.

5. Quoting, the RFC 4180 Way

The CSV convention is: wrap a field in double quotes if it contains a comma, a quote or a newline, and double any quote inside it.

u_alu 1820 see,note
u_io 640 say_"hi"

awk 'function q(s) { if (s ~ /[",]/) { gsub(/"/, "\"\"", s); return "\"" s "\"" } return s } BEGIN { OFS = ","; print "path,area,note" } { print q($1), q($2), q($3) }' bad2.rpt

path,area,note
u_alu,1820,"see,note"
u_io,640,"say_""hi"""

Both rows are now three fields. "see,note" is one quoted field containing a comma; "say_""hi""" is one quoted field whose content is say_"hi" with each internal quote doubled.

The function is worth reading once:

• s ~ /[",]/ tests whether quoting is needed at all, so clean fields are left bare and the file stays readable.
• gsub(/"/, "\"\"", s) doubles every quote before the wrapping quotes are added — the order matters, and doing it afterwards would double those too.
• The return wraps the result.

gsub() is Module 8's function. The pattern to take away is that quoting belongs in a function applied to every field, not in an if sprinkled through the program — because the moment one field is written unquoted, the file is broken for that row only, which is the hardest kind of bug to find.

A field containing a newline is also supposed to be quoted, and this is where a hand-rolled writer starts to strain: an embedded newline inside quotes is legal CSV and means a logical row spans two physical lines. awk can write that, and Chapter 3.4's RS would be needed to read it. If your data can contain newlines in fields, a real CSV library is the right tool.

6. TSV, and Choosing a Delimiter

A tab-separated file avoids the comma problem entirely, provided the data contains no tabs:

awk 'BEGIN { OFS = "\t"; print "path\tarea\tslack" } { print $1, $2, $3 }' timing.rpt | cat -et

path^Iarea^Islack$
u_alu^I1820^I-0.124$
u_io^I640^I0.5$

cat -et shows the tabs as ^I and the line ends as $. The -t is essential — plain cat -e marks line ends only and leaves tabs as invisible whitespace, so it cannot tell you whether a file is tab-separated at all. This is the only reliable way to confirm a TSV really is tab-separated — a file with spaces where you expected tabs looks identical on screen and fails in the consumer.

TSV is the better default for engineering data, for three reasons. Tool output rarely contains literal tabs. IFS and most readers handle tabs cleanly. And a human can still read it, which a quoted CSV becomes hard to do.

Its weakness is that a tab is invisible, so a file corrupted by a stray space is undiagnosable by eye — hence cat -e.

When neither will do, pick something the data genuinely cannot hold:

awk 'BEGIN { OFS = "|"; print "path|area|note" } { print $1, $2, $3 }' bad2.rpt

path|area|note
u_alu|1820|see,note
u_io|640|say_"hi"

Three fields per row, no quoting needed, because neither record contains a |. That is a claim about the data, and it should be checked rather than assumed:

awk -v d="|" 'index($0, d) { print "WARNING: record " NR " contains the delimiter: " $0 > "/dev/stderr" } BEGIN { OFS = "|"; print "path|area|note" } { print $1, $2, $3 }' bad2.rpt

path|area|note
u_alu|1820|see,note
u_io|640|say_"hi"

Nothing on stderr, so the claim holds for this input. Pointing the same check at a comma shows it firing:

awk -v d="," 'index($0, d) { print "WARNING: record " NR " contains the delimiter: " $0 > "/dev/stderr" } BEGIN { OFS = "|"; print "path|area|note" } { print $1, $2, $3 }' bad2.rpt 2>&1 >/dev/null

WARNING: record 1 contains the delimiter: u_alu 1820 see,note

That is the difference between an assumption and a verified precondition.

7. awk Cannot Reliably Read CSV

The symmetric operation does not work, and this is the chapter's honest limit.

alu,ok
fifo,"needs, review"
uart,ok

awk -F, '{ printf "NF=%d: ", NF; for (i = 1; i <= NF; i++) printf "[%s]", $i; print "" }' quoted.csv

NF=2: [alu][ok]
NF=3: [fifo]["needs][ review"]
NF=2: [uart][ok]

Row two has three fields where the file says two. -F, splits on every comma, including the one inside the quotes, and the quote characters are left attached to the fragments.

8. A Writer Whose Shape Is Fixed

For a file another program depends on, printf is a better basis than print.

Chapter 7.1 section 5 established why: print takes its separator from OFS, which anyone can change from BEGIN or with -v OFS=… on the command line. A printf format is local to the statement.

awk 'function q(s) { if (s ~ /[",]/) { gsub(/"/, "\"\"", s); return "\"" s "\"" } return s } BEGIN { printf "%s,%s,%s\n", "path", "area", "note" } { printf "%s,%s,%s\n", q($1), q($2), q($3) }' bad2.rpt

path,area,note
u_alu,1820,"see,note"
u_io,640,"say_""hi"""

Identical output to section 5, and now the commas are in the format string where nothing outside the statement can alter them.

Demonstrating the difference:

awk -v OFS=' ' 'BEGIN { print "path,area,note" } { print $1, $2, $3 }' timing.rpt

path,area,note
u_alu 1820 -0.124
u_io 640 0.5

A single -v OFS=' ' turned a CSV writer into a space-separated one, with the header still claiming commas. The printf version is immune:

awk -v OFS=' ' 'BEGIN { printf "%s,%s,%s\n", "path", "area", "slack" } { printf "%s,%s,%s\n", $1, $2, $3 }' timing.rpt

path,area,slack
u_alu,1820,-0.124
u_io,640,0.5

Use print with OFS when the delimiter is a choice the caller should make. Use printf when the file format is a contract.

9. Common Mistakes

Emitting a field that contains the delimiter. The row gains a column and every later value shifts — section 4.

Quoting some fields and not others. One unquoted field breaks that row only.

Doubling quotes after wrapping rather than before. The wrapping quotes get doubled too.

Assuming the data contains no tabs or no pipes. Check it; the check is one rule — section 6.

Reading a quoted CSV with -F,. It is a character split; quoted commas split the field — section 7.

Trusting a -F, script because it works today. A quoted field appearing later breaks it silently.

Writing a CSV with print when the format is a contract. -v OFS=… can reconfigure it — section 8.

Writing a header that does not match the rows. Generate both from the same list where you can.

Checking a TSV by eye. A tab and a space look identical; use cat -et — plain cat -e does not show tabs.

Searching for a delimiter with $0 ~ d. A metacharacter delimiter is a fatal error or matches everything; use index() — section 6.

10. Exercises

Exercise 1 — Predict the output

awk 'BEGIN { OFS = ","; print "a", "b", "c" }'

a,b,c

The commas in the print list are OFS positions, and OFS is a comma.

Exercise 2 — Emit a CSV with a derived column

Produce a CSV of path, area and a verdict derived from the slack.

u_alu 1820 -0.124
u_io 640 0.5

awk 'BEGIN { OFS = ","; print "path,area,status" } { print $1, $2 + 0, ($3 + 0 < 0 ? "VIOLATED" : "MET") }' timing.rpt

path,area,status
u_alu,1820,VIOLATED
u_io,640,MET

Exercise 3 — Debug a row with too many columns

A CSV loads into a spreadsheet with one row shifted.

u_alu 1820 see,note

awk 'BEGIN { OFS = ","; print "path,area,note" } { print $1, $2, $3 }' bad.rpt

path,area,note
u_alu,1820,see,note

Three header columns, four values. $3 is see,note and the comma inside it became a field separator in the output.

awk 'function q(s) { if (s ~ /[",]/) { gsub(/"/, "\"\"", s); return "\"" s "\"" } return s } BEGIN { OFS = ","; print "path,area,note" } { print q($1), q($2), q($3) }' bad.rpt

path,area,note
u_alu,1820,"see,note"

One quoted field, three columns.

Exercise 4 — Quote a field containing quotes

Show the quoting function handling both a comma and an embedded quote.

u_alu 1820 see,note
u_io 640 say_"hi"

awk 'function q(s) { if (s ~ /[",]/) { gsub(/"/, "\"\"", s); return "\"" s "\"" } return s } BEGIN { OFS = ","; print "path,area,note" } { print q($1), q($2), q($3) }' bad2.rpt

path,area,note
u_alu,1820,"see,note"
u_io,640,"say_""hi"""

"say_""hi""" decodes as say_"hi" — the outer pair wraps, and each internal quote is doubled.

Exercise 5 — Verify a delimiter claim

Before using | as a delimiter, check that no record contains one.

awk -v d="|" 'index($0, d) { n++ } END { printf "%d of %d record(s) contain the delimiter\n", n + 0, NR }' bad2.rpt

0 of 2 record(s) contain the delimiter

Zero, so the claim holds for this input. Running the same check for a comma shows why | was chosen:

awk -v d="," 'index($0, d) { n++ } END { printf "%d of %d record(s) contain the delimiter\n", n + 0, NR }' bad2.rpt

1 of 2 record(s) contain the delimiter

Both use index() rather than $0 ~ d, because a | compiled as a regex is a fatal error — section 6.

Exercise 6 — Engineering application

Emit a metrics CSV another tool can load: a quoted writer, a header generated from the same field list as the rows, a row count, and a non-zero status if any field needed quoting — so the caller knows the file is not naively parseable.

awk 'function q(s) { if (s ~ /[",]/) { quoted++; gsub(/"/, "\"\"", s); return "\"" s "\"" } return s } BEGIN { printf "%s,%s,%s\n", "path", "area", "note" } { printf "%s,%s,%s\n", q($1), q($2), q($3); rows++ } END { printf "# %d row(s), %d field(s) quoted\n", rows, quoted + 0; if (quoted) exit 1 }' bad2.rpt
echo "status: $?"

path,area,note
u_alu,1820,"see,note"
u_io,640,"say_""hi"""
# 2 row(s), 2 field(s) quoted
status: 1

Status 1 says "this file contains quoted fields" — which tells a calling script that a naive -F, reader will not do, and is exactly the precondition section 7 said to establish rather than assume.

On data needing no quoting it exits 0:

awk 'function q(s) { if (s ~ /[",]/) { quoted++; gsub(/"/, "\"\"", s); return "\"" s "\"" } return s } BEGIN { printf "%s,%s,%s\n", "path", "area", "note" } { printf "%s,%s,%s\n", q($1), q($2), q($3); rows++ } END { printf "# %d row(s), %d field(s) quoted\n", rows, quoted + 0; if (quoted) exit 1 }' timing.rpt
echo "status: $?"

path,area,note
u_alu,1820,-0.124
u_io,640,0.5
# 2 row(s), 0 field(s) quoted
status: 0

The engineering caveat: the # trailer is a comment by convention only — CSV has no comment syntax, so a strict reader will treat it as a data row with one field. For a file consumed by a program, send that line to stderr instead; for one a human opens, a trailer is helpful. The two audiences want different files, which is worth deciding deliberately rather than by default.

Exercise 7 — Challenge: detect a file awk should not parse

Write a check that decides whether a given file is safe to read with -F,, and show it answering both ways.

awk '/"/ { q++ } END { if (q) printf "UNSAFE: %d of %d record(s) contain a quote\n", q, NR; else printf "safe: no quotes in %d record(s)\n", NR; exit (q ? 1 : 0) }' quoted.csv
echo "status: $?"

alu,ok
fifo,"needs, review"
uart,ok

UNSAFE: 1 of 3 record(s) contain a quote
status: 1

awk '/"/ { q++ } END { if (q) printf "UNSAFE: %d of %d record(s) contain a quote\n", q, NR; else printf "safe: no quotes in %d record(s)\n", NR; exit (q ? 1 : 0) }' timing.rpt
echo "status: $?"

safe: no quotes in 2 record(s)
status: 0

A cheap gate a script can run before trusting a -F, reader.

Its limit is worth naming, because a check whose blind spots are unknown is not much of a check. It detects a quote anywhere, so it is conservative — a file with a quote inside a field that contains no comma is flagged unsafe when -F, would in fact handle it. That is the right direction to err, and it is the same positive-guard reasoning from Chapter 5.3: accept only what you can recognise as safe, and treat everything else as suspect, because the alternative is a reader that works until the data changes.

What it cannot detect is a missing quote — a file whose fields contain commas and were never quoted at all. That file has the wrong number of columns on some rows and looks perfectly ordinary to this check. Catching it needs a field-count check instead:

awk -F, 'NR == 1 { want = NF; next } NF != want { printf "record %d has %d field(s), header has %d\n", NR, NF, want; bad++ } END { printf "%d malformed row(s) of %d\n", bad + 0, NR - 1; exit (bad ? 1 : 0) }' quoted.csv
echo "status: $?"

record 2 has 3 field(s), header has 2
1 malformed row(s) of 2
status: 1

Two independent checks, catching two different corruptions — one that the file is quoted, one that its rows disagree with its header. Neither finds the other's fault, which is why a real validator runs both.

11. Summary

• A delimited file removes the width dependence an aligned table has, which is why a program-readable report is delimited.
• OFS plus print is the short form; the header goes in BEGIN.
• A field containing the delimiter adds a column and shifts every later value — silently, and a spreadsheet loads it happily.
• Quote per RFC 4180: wrap if the field contains a delimiter, quote or newline, and double internal quotes before wrapping.
• Apply quoting in a function to every field, so no row can be written half-quoted.
• TSV is the better default for engineering data, and must be checked with cat -e because tabs are invisible.
• A claim that the data cannot contain the delimiter should be verified, not assumed.
• -F, is a character split, not a CSV parse. A quoted comma splits the field.
• Use printf when the file format is a contract — -v OFS=… can silently reconfigure a print-based writer.
• Check a file before trusting a -F, reader, and check field counts too; the two checks find different faults.

Your output is now consumable. One thing about it is still undefined: the order of the rows, because every per-key report in Module 6 iterated an array. Chapter 7.7 — Output Ordering is about imposing an order you can rely on — and about the demonstration that array iteration order cannot be guessed, since the same program gives three different orders for three different key names.