Writing a CSV File With Python and Keeping Quotes Correct

Writing a CSV looks like the easy half. You have a list of rows and you join them with commas. That works until a name contains a comma, a note contains a line break or a number needs to be exactly what the database held, and then a hand-rolled version produces a file that parses into the wrong columns. The standard library's csv.writer handles the quoting rules for you, and has a handful of defaults you should know before you ship a file to someone else. This article goes through the writer on small examples, shows the bytes it produces, and lists the places where what you wrote is not what you meant. For the reading side, see the csv module article, and for the whole subject the guide to CSV in Python.

The basic write

Rows go in as lists. newline="" goes on the open() call, for the reason the reader article gives, and the writer handles the line endings itself:

import csv

rows = [
    ["id", "name", "note"],
    [1, "Ana Silva", "likes tea"],
    [2, "Smith, Jane", 'said "hello"'],
    [3, "Bo Chen", "line one\nline two"],
    [4, "Cy Dubois", None],
]
with open("out-basic.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(rows)

I then printed the file's bytes with the carriage returns and line feeds made visible:

python /batches/csv-0001-0200/lab/py/write_basic.py && python /batches/csv-0001-0200/lab/py/write_check.py

id,name,note<CR><LF>
1,Ana Silva,likes tea<CR><LF>
2,"Smith, Jane","said ""hello"""<CR><LF>
3,Bo Chen,"line one<LF>
line two"<CR><LF>
4,Cy Dubois,<CR><LF>

Read it line by line. Smith, Jane is quoted because it contains the delimiter. said "hello" is quoted and each inner quote doubled, the convention RFC 4180 describes. The record for Bo Chen contains a line feed inside quotes, so the physical line breaks but the record does not. The last row has None for the note, and the writer wrote nothing: the line ends Cy Dubois,.

Every record ends in <CR><LF>, even though this ran on Linux. The writer's default lineterminator is \r\n, which is what RFC 4180 specifies, and it does not depend on the platform. That is correct for most consumers and surprising to people who diff the file or grep it expecting \n. To get LF line endings pass lineterminator="\n". It is the writer's setting, not the file's, and newline="" on open() is what keeps the file object from changing it a second time.

Quote everything, or nothing

By default the writer quotes only when it must (QUOTE_MINIMAL). The quoting parameter changes that, and different modes suit different consumers. I wrote the same seven values, an integer, a float, three kinds of string, None and an empty string, in every mode:

import csv, io

row = [1, 2.5, "plain", "a,b", 'q"q', None, ""]
for name in ["QUOTE_MINIMAL", "QUOTE_ALL", "QUOTE_NONNUMERIC", "QUOTE_NONE", "QUOTE_STRINGS", "QUOTE_NOTNULL"]:
    q = getattr(csv, name)
    buf = io.StringIO()
    kw = {"escapechar": "\\"} if name == "QUOTE_NONE" else {}
    csv.writer(buf, quoting=q, **kw).writerow(row)
    print(f"{name:17s} {buf.getvalue()!r}")

python /batches/csv-0001-0200/lab/py/write_quoting.py

QUOTE_MINIMAL     '1,2.5,plain,"a,b","q""q",,\r\n'
QUOTE_ALL         '"1","2.5","plain","a,b","q""q","",""\r\n'
QUOTE_NONNUMERIC  '1,2.5,"plain","a,b","q""q","",""\r\n'
QUOTE_NONE        '1,2.5,plain,a\\,b,q\\"q,,\r\n'
QUOTE_STRINGS     '1,2.5,"plain","a,b","q""q",,""\r\n'
QUOTE_NOTNULL     '"1","2.5","plain","a,b","q""q",,""\r\n'

• QUOTE_MINIMAL: 1,2.5,plain,"a,b","q""q",,. Only the two fields that need it are quoted; None and the empty string are both nothing.

• QUOTE_ALL: every field, numbers included, and None as "".

• QUOTE_NONNUMERIC: numbers bare, every non-number quoted, None as "".

• QUOTE_NONE: no quoting at all; it needs an escapechar, and the comma and the quote came out as a\,b and q\"q.

• QUOTE_STRINGS: like non-numeric, but None stays empty and the empty string is "", so the two can be told apart.

• QUOTE_NOTNULL: quotes everything except None, so the null is the one unquoted empty field.

If a downstream importer needs to tell NULL from an empty string, QUOTE_STRINGS is the mode that carries the difference, and it only helps if the reader honors it; DuckDB's reader, for one, did not distinguish the two when I tested it for the export article. The default is right for most files. Reach for QUOTE_ALL when a legacy importer demands quoted fields, and QUOTE_NONE almost never, since a missing escape produces a silently broken file.

DictWriter and the unexpected key

When your rows are dictionaries, DictWriter takes the column order from fieldnames and looks each value up by key. Its two failure modes are worth seeing:

import csv

people = [
    {"id": 1, "name": "Ana Silva", "city": "Lisbon"},
    {"id": 2, "name": "Smith, Jane", "city": "Boston", "vip": True},
]
try:
    with open("out-dict.csv", "w", newline="", encoding="utf-8") as f:
        w = csv.DictWriter(f, fieldnames=["id", "name", "city"])
        w.writeheader()
        w.writerows(people)
except ValueError as e:
    print("ValueError:", e)
with open("out-dict.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=["id", "name", "city"], extrasaction="ignore", restval="")
    w.writeheader()
    w.writerows(people)
    w.writerow({"id": 3})
print(open("out-dict.csv", newline="").read().replace("\r\n", "<CRLF>\n"))

python /batches/csv-0001-0200/lab/py/write_dict.py

ValueError: dict contains fields not in fieldnames: 'vip'
id,name,city<CRLF>
1,Ana Silva,Lisbon<CRLF>
2,"Smith, Jane",Boston<CRLF>
3,,<CRLF>

The first attempt raised ValueError: dict contains fields not in fieldnames: 'vip' because the second dictionary has a key that is not in the column list. That is a deliberate guard and I would leave it on, since an extra key usually means the data changed shape. Pass extrasaction="ignore" when dropping the extra is what you want. The opposite case, a missing key, is filled with restval, which defaults to an empty string: writerow({"id": 3}) produced the line 3,,. In that run the file contains the header, two rows with vip dropped, and the sparse row.

What gets written for each type

The writer converts every value with str(), except None, which becomes empty. That is simple and it has consequences:

import csv, io
from datetime import date, datetime
from decimal import Decimal

buf = io.StringIO()
csv.writer(buf, lineterminator="\n").writerow([0.1 + 0.2, Decimal("0.30"), date(2025, 3, 14), datetime(2025, 3, 14, 10, 15), True, 10**20, float("nan"), b"bytes"])
print(buf.getvalue(), end="")

python /batches/csv-0001-0200/lab/py/write_values.py

0.30000000000000004,0.30,2025-03-14,2025-03-14 10:15:00,True,100000000000000000000,nan,b'bytes'

The line is 0.30000000000000004,0.30,2025-03-14,2025-03-14 10:15:00,True,100000000000000000000,nan,b'bytes'. Going through them:

• The float 0.1 + 0.2 printed all seventeen significant digits. Round or format floats on the way out if people will read the file, and use Decimal for money, which printed 0.30.

• A date is written ISO style, which is what you want. A datetime is written with a space, 2025-03-14 10:15:00, not a T; some consumers expect the T, so call .isoformat() yourself if it matters.

• A bool is True and False, capitalized, which not every consumer reads as a boolean.

• An integer of any size is written in full.

• nan is written as nan, which a reader may not recognize as missing.

• bytes became the text b'bytes', the repr, with no error. If you see b' in a column of a file you wrote, something passed bytes where it should have decoded them.

The pattern is the same for each: the writer does not know what the value means, and every consumer has its own idea of the right spelling. Convert in your code, in one function that you can test, and treat the writer as a way of getting quoting right.

Writing for Excel

Excel on Windows is widely reported to read a UTF-8 CSV as a legacy code page unless the file begins with a byte order mark. I could not run Excel on this bench, so that part is reported behavior; what I can show is that Python writes the mark when asked:

import csv

with open("out-excel.csv", "w", newline="", encoding="utf-8-sig") as f:
    csv.writer(f).writerow(["name", "city"])
    csv.writer(f).writerow(["José", "Zürich"])
print(open("out-excel.csv", "rb").read()[:12])

python /batches/csv-0001-0200/lab/py/write_excel.py

b'\xef\xbb\xbfname,city'

The file begins \xef\xbb\xbf and then name,city. encoding="utf-8-sig" writes the BOM once, at the start of the stream, and the rest is ordinary UTF-8, so create the writer once and write all rows through it. The trade-off is covered in the BOM article and the Excel side in the UTF-8 in Excel article: the BOM helps Excel and can confuse other tools, so add it only for files that people will open in Excel.

Writing quoted numbers back as numbers

Quoting modes are also the reading side's contract. If you write with QUOTE_NONNUMERIC, you can read with it and get numbers back, which is convenient and has a catch:

import csv, io

buf = io.StringIO()
w = csv.writer(buf, quoting=csv.QUOTE_NONNUMERIC)
w.writerow(["id", "name", "zip"])
w.writerow([1, "Ana", "02134"])
text = buf.getvalue()
print(repr(text))
print(list(csv.reader(io.StringIO(text), quoting=csv.QUOTE_NONNUMERIC))[1:])

python /batches/csv-0001-0200/lab/py/write_roundtrip.py

'"id","name","zip"\r\n1,"Ana","02134"\r\n'
[[1.0, 'Ana', '02134']]

The file is "id","name","zip" then 1,"Ana","02134". Read back with QUOTE_NONNUMERIC, the row is [1.0, 'Ana', '02134']: the unquoted 1 became the float 1.0 (every unquoted field is converted to float, so an integer id turns into a float), and the quoted ZIP code stayed a string with its leading zero. That keeps the ZIP safe, which is the point of quoting it, but a consumer that is not aware of the convention will read "02134" as text and 1 as a number, and one that ignores quotes entirely will see no difference. Decide the contract before you rely on it.

How to tell it worked

Read the file back with a different tool: DuckDB's read_csv or csv.reader with strict=True. Compare the number of records, not lines. Look at the raw bytes of the first few lines with head -c 200 file | xxd for the BOM and the line endings. Search for a row you know contains a comma, a quote and a line break, and look at how it was written. And open the result in the tool the recipient will use, since the only test that counts is theirs.

What the writer will not check for you

It does not validate that every row has the same number of fields; writerow([1, 2]) after a three-column header happily produces a short line. It does not guard against a cell that begins with =, +, - or @, which a spreadsheet may treat as a formula. It does not write atomically: a crash halfway through leaves a truncated file at the final path, so write to a temporary name and rename when you finish. And it does not make a large export fast. For millions of rows, pandas.DataFrame.to_csv or a DuckDB COPY will be faster than a Python loop, and the export article shows the latter.