Writing CSV Other Tools Will Consume

Part II · Module 21 · Chapter 21.3 · Writing CSV Other Tools Will Consume

1. The Column Order Is the Contract

import csv
import io

FIELDS = ["test", "seed", "status", "runtime_s"]

ROWS = [
    {"status": "FAIL", "test": "axi_smoke", "seed": 1839274, "runtime_s": 143.2},
    {"test": "axi_reset", "seed": 771002, "status": "PASS", "runtime_s": None},
]

buffer = io.StringIO()
writer = csv.DictWriter(buffer, fieldnames=FIELDS)
writer.writeheader()
writer.writerows(ROWS)

print(buffer.getvalue())
print(repr(buffer.getvalue()))

test,seed,status,runtime_s
axi_smoke,1839274,FAIL,143.2
axi_reset,771002,PASS,

'test,seed,status,runtime_s\r\naxi_smoke,1839274,FAIL,143.2\r\naxi_reset,771002,PASS,\r\n'

The first row's dict has status first and the output has it third. fieldnames decides the column order and the dict's own order is irrelevant — which is the single most useful property of DictWriter and the reason it is worth preferring over csv.writer with a list.

That matters because of what a consumer does with the file. A script reading by name does not care; a spreadsheet, a cut -f3, a diff against last night's table, and a human scanning a column all care enormously. Column order is part of the interface, exactly like the field names, and fieldnames is the one place it is written down.

writeheader() is a separate call and omitting it is legal, which produces a headerless table that DictReader will then read with the first data row as its field names. Always write it.

And None wrote an empty field, as Chapter 21.1 Section 6 showed. The round trip gives '' back.

2. Pick the Order Deliberately

import csv
import io

ROW = {"test": "axi_smoke", "seed": 1839274, "status": "FAIL", "runtime_s": 143.2,
       "errors": 2, "first_failure": "bad handshake",
       "log_path": "runs/a/sim.log", "run_dir": "runs/a"}

ORDERS = {
    "identity first": ["test", "seed", "status", "runtime_s", "errors",
                       "first_failure", "log_path", "run_dir"],
    "alphabetical  ": sorted(ROW),
}

for label, fields in ORDERS.items():
    buffer = io.StringIO()
    writer = csv.DictWriter(buffer, fieldnames=fields)
    writer.writeheader()
    writer.writerow(ROW)
    print(label, "->", buffer.getvalue().splitlines()[0])

identity first -> test,seed,status,runtime_s,errors,first_failure,log_path,run_dir
alphabetical   -> errors,first_failure,log_path,run_dir,runtime_s,seed,status,test

Both are stable. Only one is readable: test is what a person looks for first and alphabetical order buries it last. Chapter 21.1's eight fields are listed in a deliberate order — identity, then verdict, then detail, then where to look — and reusing that list as fieldnames is how the order survives.

Define it once, as a module constant, and let the reader and the writer share it. Two copies of a column list in one repository will disagree within a quarter.

3. Extra Keys Are Loud, Missing Keys Are Silent

import csv
import io

FIELDS = ["test", "seed", "status"]


def write_one(row, **kwargs):
    buffer = io.StringIO()
    writer = csv.DictWriter(buffer, fieldnames=FIELDS, **kwargs)
    writer.writerow(row)
    return buffer.getvalue()


try:
    write_one({"test": "a", "seed": 1, "status": "PASS", "host": "farm_07"})
except ValueError as exc:
    print("extra key  :", type(exc).__name__, exc)

print("missing key:", repr(write_one({"test": "a"})))
print("with restval:", repr(write_one({"test": "a"}, restval="NA")))
print("ignore extra:", repr(write_one({"test": "a", "seed": 1, "status": "P", "host": "x"},
                                      extrasaction="ignore")))

extra key  : ValueError dict contains fields not in fieldnames: 'host'
missing key: 'a,,\r\n'
with restval: 'a,NA,NA\r\n'
ignore extra: 'a,1,P\r\n'

Two behaviours that are not symmetric, and the asymmetry is backwards from what you would want.

An extra key raises. That is a good failure: somebody added a field to the record and did not add it to the table, and the write stops until they decide which. This is also the rarer mistake.

A missing key writes an empty cell and says nothing. That is the common mistake — a producer that failed to fill a field, exactly Chapter 21.1 Section 7's unowned column — and it produces a table that looks complete.

restval does not fix it so much as make it visible: NA in a cell is something a reader notices and an empty cell is not. Whether NA or '' is right depends on the consumer — a spreadsheet user wants NA, a script doing float(x or 0) wants the empty — and the decision belongs with the table's format, written down.

extrasaction="ignore" is the dangerous one. It silences the only loud check in this API. Reach for it when you are deliberately writing a projection of a richer record — which is Chapter 21.1's to_result — and even then, prefer selecting the fields explicitly so the projection is visible in the code rather than implied by a flag.

4. Quoting Is Handled, and That Is the Point

import csv
import io

VALUES = ["plain", "has, comma", 'has "quote"', "has\nnewline", "", None]

buffer = io.StringIO()
writer = csv.DictWriter(buffer, fieldnames=["note"])
for value in VALUES:
    writer.writerow({"note": value})

print(repr(buffer.getvalue()))
print()
back = [row["note"] for row in csv.DictReader(io.StringIO("note\r\n" + buffer.getvalue()))]
for original, returned in zip(VALUES, back):
    print(f"{original!r:<16} -> {returned!r:<18} same={original == returned}")

'plain\r\n"has, comma"\r\n"has ""quote"""\r\n"has\nnewline"\r\n""\r\n""\r\n'

'plain'          -> 'plain'            same=True
'has, comma'     -> 'has, comma'       same=True
'has "quote"'    -> 'has "quote"'      same=True
'has\nnewline'   -> 'has\nnewline'     same=True
''               -> ''                 same=True
None             -> ''                 same=False

Five of six survive exactly, including a value containing the separator, a value containing the quote character (doubled, which is the CSV escape), and a value containing a newline. The sixth is None, which Chapter 21.1 Section 6 already established.

Note the "" at the end of the written text, twice. In a single-column table an empty field must be quoted, because a bare empty line would be indistinguishable from the blank line Chapter 21.2 Section 3 skips. In the multi-column row of Section 1 the same None wrote a bare empty field, because the commas around it already make it unambiguous. The writer quotes what it must and no more, which is what QUOTE_MINIMAL means.

This is the thing a hand-rolled writer gets wrong. ",".join(str(v) for v in row.values()) works until the first first_failure message contains a comma, and then it produces a row with one extra column — which DictReader reports as a long row at the far end, days later, as somebody else's problem.

The rule is simply: never build a CSV line yourself. The module's default QUOTE_MINIMAL quotes exactly what needs quoting, which also keeps the file readable. QUOTE_ALL exists and is worth considering only when a downstream tool is known to mis-handle bare fields:

import csv
import io

for quoting, label in [(csv.QUOTE_MINIMAL, "MINIMAL"), (csv.QUOTE_ALL, "ALL   ")]:
    buffer = io.StringIO()
    writer = csv.DictWriter(buffer, fieldnames=["a", "b"], quoting=quoting)
    writer.writerow({"a": "plain", "b": 42})
    print(label, repr(buffer.getvalue()))

MINIMAL 'plain,42\r\n'
ALL    '"plain","42"\r\n'

Note what QUOTE_ALL does to the integer: it becomes "42". The reader returns a string either way — Chapter 21.2 Section 5 — so nothing is lost, but a human diffing two tables sees quotes everywhere and a tool expecting bare numerics may object.

5. newline="" on the Write Side

Chapter 21.2 Section 7 showed what omitting it does when reading. On writing, the mechanism is the reverse: the csv module emits \r\n itself, and if the file object is also translating line endings, the \n in that pair gets translated too.

import csv
import os

for newline, label in [("", "newline=''    "), ("\r\n", "newline='\\r\\n'")]:
    with open("out.csv", "w", encoding="utf-8", newline=newline) as handle:
        writer = csv.DictWriter(handle, fieldnames=["a", "b"])
        writer.writeheader()
        writer.writerow({"a": 1, "b": 2})
    with open("out.csv", "rb") as handle:
        print(label, "->", handle.read())

print()
print("os.linesep on this platform:", repr(os.linesep))

newline=''     -> b'a,b\r\n1,2\r\n'
newline='\r\n' -> b'a,b\r\r\n1,2\r\r\n'

os.linesep on this platform: '\n'

\r\r\n is the corruption, and it shows up in a spreadsheet as a blank line between every row.

Two honest notes about that demonstration. The second case here is forced with an explicit newline="\r\n", because on this platform os.linesep is \n and the default does no translation at all — so the two defaults are identical here and the bug cannot occur. On Windows, os.linesep is \r\n and the default does exactly what the forced case shows.

So: the file written by a Linux build server is fine either way, and the same script run on an engineer's Windows laptop writes a table with a blank line between every row. Specify newline="" and the question never arises — which is why it is in every open() in this module, including the ones where it changes nothing.

6. The Values Have to Be Stable Too

Columns and quoting are the library's job. This one is yours:

import csv
import io

MEASURED = 0.1 + 0.2

print("the value       :", MEASURED)
print("its repr        :", repr(MEASURED))

buffer = io.StringIO()
writer = csv.DictWriter(buffer, fieldnames=["runtime_s"])
writer.writerow({"runtime_s": MEASURED})
print("written as      :", repr(buffer.getvalue()))

formatted = io.StringIO()
writer = csv.DictWriter(formatted, fieldnames=["runtime_s"])
writer.writerow({"runtime_s": f"{MEASURED:.3f}"})
print("formatted as    :", repr(formatted.getvalue()))

the value       : 0.30000000000000004
its repr        : 0.30000000000000004
written as      : '0.30000000000000004\r\n'
formatted as    : '0.300\r\n'

csv writes whatever str() gives it, and for a float that is the full repr. Chapter 2.6's floating-point arithmetic, now reaching a file.

Why that matters here specifically: a nightly regression writes a table, and somebody diffs today's against yesterday's to find what changed. A runtime of 0.30000000000000004 against 0.3 is a diff on a line where nothing changed, and a report that produces spurious diffs stops being read — the same fate Chapter 20.2's checker faced for the same reason.

Formatting at the point of writing fixes it, and the decision is per column:

import csv
import io

FIELDS = ["test", "seed", "status", "runtime_s", "coverage_pct"]

FORMATS = {"runtime_s": "{:.3f}", "coverage_pct": "{:.2f}"}

ROWS = [
    {"test": "axi_smoke", "seed": 1839274, "status": "FAIL",
     "runtime_s": 0.1 + 0.2, "coverage_pct": 87.33333333},
    {"test": "axi_reset", "seed": 771002, "status": "PASS",
     "runtime_s": 61.4, "coverage_pct": 91.0},
]


def formatted(row):
    """Apply the column formats; leave everything else to str()."""
    out = {}
    for key, value in row.items():
        if value is None:
            out[key] = ""
        elif key in FORMATS:
            out[key] = FORMATS[key].format(value)
        else:
            out[key] = value
    return out


buffer = io.StringIO()
writer = csv.DictWriter(buffer, fieldnames=FIELDS)
writer.writeheader()
for row in ROWS:
    writer.writerow(formatted(row))

print(buffer.getvalue())

test,seed,status,runtime_s,coverage_pct
axi_smoke,1839274,FAIL,0.300,87.33
axi_reset,771002,PASS,61.400,91.00

Fixed precision per column, so the same measurement always produces the same characters.

Note that this is a presentation decision landing in a data file, and it has a cost: 0.300 has lost precision that 0.30000000000000004 had. For a runtime in seconds that precision was never real; for a slack value in nanoseconds it might be. The right number of digits is a question about the measurement, and the wrong answer is to not decide — which is what str(float) does.

7. Appending Without Repeating the Header

A sweep that writes a row per log as it goes has to answer one question: has the header been written yet?

import csv
import os

FIELDS = ["test", "status"]
PATH = "results.csv"


def append_result(path, row):
    """Append one row, writing the header only when creating the file."""
    is_new = not os.path.exists(path)
    with open(path, "a", newline="", encoding="utf-8") as handle:
        writer = csv.DictWriter(handle, fieldnames=FIELDS)
        if is_new:
            writer.writeheader()
        writer.writerow(row)


append_result(PATH, {"test": "axi_smoke", "status": "FAIL"})
append_result(PATH, {"test": "axi_reset", "status": "PASS"})

print(open(PATH, encoding="utf-8").read())

print("what a repeated header looks like to a reader:")
with open(PATH, "a", newline="", encoding="utf-8") as handle:
    csv.DictWriter(handle, fieldnames=FIELDS).writeheader()
    csv.DictWriter(handle, fieldnames=FIELDS).writerow({"test": "axi_burst", "status": "PASS"})

with open(PATH, newline="", encoding="utf-8") as handle:
    for row in csv.DictReader(handle):
        print("  ", row)

test,status
axi_smoke,FAIL
axi_reset,PASS

what a repeated header looks like to a reader:
   {'test': 'axi_smoke', 'status': 'FAIL'}
   {'test': 'axi_reset', 'status': 'PASS'}
   {'test': 'test', 'status': 'status'}
   {'test': 'axi_burst', 'status': 'PASS'}

The repeated header becomes a data row whose values are the column names — {'test': 'test', 'status': 'status'}. DictReader does not skip it, because nothing tells it to; a header is only special at the top of a file.

In a results table that row has status of "status", which is not one of Chapter 20.11's four values, so a summary counting statuses gets a category nobody defined. It is the kind of row that produces a bug report saying "the dashboard shows a test called test".

Two ways to avoid it, and the choice is about how the sweep fails. Append with the existence check above — simple, and leaves a partial file behind if the sweep dies halfway. Collect and write once at the end — a complete file or no file, and it holds every row in memory, which Chapter 20.14 Section 4 said is the right call for a few hundred summary dicts and the wrong one for raw data.

8. Predict, Run, Observe

import csv
import io

FIELDS = ["b", "a"]


def write(row, **kwargs):
    buffer = io.StringIO()
    writer = csv.DictWriter(buffer, fieldnames=FIELDS, **kwargs)
    writer.writerow(row)
    return buffer.getvalue()


# A
print("A:", repr(write({"a": 1, "b": 2})))

# B
print("B:", repr(write({"b": 2})))

# C
print("C:", repr(write({"b": 2}, restval="NA")))

# D
try:
    write({"a": 1, "b": 2, "c": 3})
except ValueError as exc:
    print("D:", type(exc).__name__)

# E
print("E:", repr(write({"a": "x,y", "b": 'he said "hi"'})))

# F
print("F:", repr(write({"a": None, "b": 0.1 + 0.2})))

# G
buffer = io.StringIO()
csv.DictWriter(buffer, fieldnames=FIELDS).writeheader()
print("G:", repr(buffer.getvalue()))

The verified output:

A: '2,1\r\n'
B: '2,\r\n'
C: '2,NA\r\n'
D: ValueError
E: '"he said ""hi""","x,y"\r\n'
F: '0.30000000000000004,\r\n'
G: 'b,a\r\n'

A — fieldnames put b first, whatever the dict said.

B — the missing a wrote an empty cell with no complaint.

C — restval makes the same hole visible.

D — an extra key raises. The opposite treatment to B, for the less likely mistake.

E — the quote was doubled and the comma-bearing value was quoted. Both survive a round trip.

F — None wrote empty and the float wrote its full repr. The second one is a diff tomorrow.

G — writeheader writes fieldnames in order, which is where the contract is recorded.

9. Exercises

Exercise 1 — order from fieldnames. Write rows whose dicts are in a different order from fieldnames and confirm the columns follow fieldnames.

Exercise 2 — one definition. Put your column list in a module constant and use it in both your reader and your writer. Say what would go wrong with two copies.

Exercise 3 — choose an order. Write the same row with your chosen order and alphabetically. Say which a person scans better and why.

Exercise 4 — the asymmetry. Trigger the extra-key ValueError and the silent missing-key write. Say which mistake is more common in your flow.

Exercise 5 — restval. Write a row with a missing field using restval="NA". Say which of NA and '' your consumer wants and why.

Exercise 6 — never hand-roll. Build a CSV line with ",".join(...) for a value containing a comma. Read it back with DictReader and report what you get.

Exercise 7 — the four awkward values. Round-trip a comma, a quote, a newline and an empty string. Confirm all four survive.

Exercise 8 — quote all. Write the same row with QUOTE_MINIMAL and QUOTE_ALL. Say what changes for an integer column.

Exercise 9 — the line endings. Write a file with newline="" and with newline="\r\n" and print the raw bytes of each. Say which one your platform's default matches and how you found out.

Exercise 10 — the float diff. Write 0.1 + 0.2 unformatted, then formatted to three decimal places. Say what a nightly diff would show in each case.

Exercise 11 — digits are a decision. For one numeric column in your flow, choose the number of digits and justify it from the measurement rather than from taste.

Exercise 12 — stable rows. Write a table from an unsorted sweep twice. Say what changes between the two files and fix it.

Exercise 13 — the repeated header. Append a header to an existing table and read the file back. Report the row you get and what a status summary would do with it.

Exercise 14 — changed columns. Append to a table written with a different column list. Describe the result in Chapter 21.2's vocabulary.

10. Summary

• fieldnames decides the column order, and the dict's own order is irrelevant — which is why DictWriter beats csv.writer.
• Column order is part of the interface, so define the list once as a constant shared by the reader and the writer.
• Always writeheader(). Without it, DictReader takes the first data row as the field names.
• An extra key raises ValueError; a missing key writes an empty cell silently. The loud failure is the rarer mistake.
• restval makes a missing field visible, and whether NA or '' is right belongs to the table's documented format.
• extrasaction="ignore" silences the only loud check in the API — prefer an explicit projection.
• Never build a CSV line yourself. The module quotes commas, doubles quote characters and handles embedded newlines, all of which survive a round trip.
• QUOTE_ALL quotes integers too, which a reader does not mind and some downstream tools do.
• newline="" on writes stops a double translation that produces \r\r\n and a blank line between every row — the default is harmless where os.linesep is \n and not where it is \r\n.
• Stability means three things: columns, quoting, and values.
• csv writes str() of a float, which is its full repr, so 0.1 + 0.2 lands as 0.30000000000000004 and tomorrow's diff shows a change that never happened.
• Format per column at the point of writing, and choose the digits from the measurement rather than by taste.
• Sort the rows too — an unsorted sweep produces a different file every night with no result changing.
• When appending, write the header only when creating the file. A repeated header reads back as a data row whose values are the column names.
• The existence check does not verify the columns — compare the first line against FIELDS if a table is appended to across sessions.

The next chapter is the format that needs none of this care for nested data — and is the right default for anything one script writes and another reads.