How to Escape Quotes and Newlines Inside CSV Fields

The writing side of CSV has one rule, and it is easy to follow and easy to forget. Any field that contains the delimiter, a double quote or a line break gets wrapped in double quotes, and each double quote inside it is written twice. A program that forgets the second half produces "He said "hi" and left", which is the line that makes the reader's parser hesitate. This article covers writing correctly from Python, PostgreSQL and DuckDB, testing the output by reading it back, and repairing a file that was written wrong. The reading side, with the same rule seen from the parser, is in how CSV quoting works.

What you need

A writer that knows the format and a second reader that does not share its code. Most bugs in this area are not in the writer you chose, but in assuming that a file which opens in one program will open in every program.

The rule, applied by Python's writer

Python's csv.writer has four quoting modes. I wrote the same three records in each, one with a comma and a quote, one with a line break and one plain:

python - <<'PY'
import csv, sys
rows = [["id", "note"], [1, 'He said "hi", twice'], [2, "two\nlines"], [3, "plain"]]
for name in ("QUOTE_MINIMAL", "QUOTE_ALL", "QUOTE_NONNUMERIC"):
    print(f"--- {name}")
    csv.writer(sys.stdout, quoting=getattr(csv, name), lineterminator="\n").writerows(rows)
print("--- QUOTE_NONE")
try:
    csv.writer(sys.stdout, quoting=csv.QUOTE_NONE, lineterminator="\n").writerows(rows)
except csv.Error as e:
    print("Error:", e)
PY

--- QUOTE_MINIMAL
id,note
1,"He said ""hi"", twice"
2,"two
lines"
3,plain
--- QUOTE_ALL
"id","note"
"1","He said ""hi"", twice"
"2","two
lines"
"3","plain"
--- QUOTE_NONNUMERIC
"id","note"
1,"He said ""hi"", twice"
2,"two
lines"
3,"plain"
--- QUOTE_NONE
id,note
Error: need to escape, but no escapechar set

QUOTE_MINIMAL, the default, wrapped only the two fields that needed it, and the quote inside the first became two. QUOTE_ALL wrapped everything, including the header and the numbers. QUOTE_NONNUMERIC wrapped every text field and left the numbers bare. QUOTE_NONE wrote the header and then stopped at the first record, because that record contained characters it could not escape without an escape character:

--- QUOTE_NONE
id,note
Error: need to escape, but no escapechar set

All three of the working modes are correct CSV. They differ in how much they write and in what a downstream reader does with them, and I come back to that below.

Three things about the writer are worth knowing. The default lineterminator is \r\n, not \n, as the module follows the RFC; set it explicitly when you want LF. Open the output file with newline="", otherwise Python on Windows adds a second carriage return. And the writer has no idea what a NULL is. It writes None as an empty field, exactly like an empty string:

a,,
b,,

Both None and "" produced an empty field in that output, so the difference between "no value" and "an empty value" was gone before the file was closed. If that distinction matters, use a writer that makes it.

The same rule in PostgreSQL and DuckDB

PostgreSQL's COPY in CSV mode and DuckDB's COPY ... TO both apply the rule by default. I exported four rows with a comma and quote, a line break, a NULL and an empty string:

psql -qAt <<'SQL'
\copy (SELECT 1 AS id, 'He said "hi", twice' AS note UNION ALL SELECT 2, E'two\nlines' UNION ALL SELECT 3, NULL UNION ALL SELECT 4, '') TO STDOUT WITH (FORMAT csv, HEADER true)
SQL

id,note
1,"He said ""hi"", twice"
2,"two
lines"
3,
4,""

duckdb -c "COPY (SELECT 1 AS id, 'He said \"hi\", twice' AS note UNION ALL SELECT 2, E'two\nlines' UNION ALL SELECT 3, NULL UNION ALL SELECT 4, '') TO '/dev/stdout' (FORMAT csv, HEADER)"

id,note
1,"He said ""hi"", twice"
2,"two
lines"
3,
4,""

The two outputs are identical, byte for byte. The line break is kept inside a wrapped field. The NULL is written as nothing, and the empty string is written as "", which is how a CSV can carry the difference between them. The COPY reference for PostgreSQL documents this: NULL defaults to an unquoted empty string in CSV, and the quoted version is a real empty string. Whether the reader keeps the distinction is up to the reader.

Test the file by reading it back

The most useful habit in this area is a round trip. Write a set of nasty values, read the file back with other readers, and compare. I used eleven values: plain text, a comma, an embedded quote, a line break, an empty string, padded spaces, accented letters, a tab, a trailing quote, a leading quote and a lone hyphen. The writer was Python's, with default quoting, and the readers were Python, pandas, DuckDB and PostgreSQL:

python roundtrip.py minimal

writer quoting: minimal
python csv                       identical
pandas                           identical
duckdb defaults                  1 differ: [('', '<NULL>')]
duckdb allow_quoted_nulls=false  identical
postgres COPY                    1 differ: [('', '<NULL>')]

Ten of eleven values came through in every reader. The exception is the empty string, which two readers turned into NULL: PostgreSQL did, because an unquoted empty field is NULL in its CSV mode, and DuckDB did the same under its defaults. With QUOTE_ALL the empty string is written as "", and PostgreSQL then keeps it. DuckDB still returns NULL under defaults, because its allow_quoted_nulls option treats a quoted value that matches the null string as NULL, and turning that option off made it identical:

writer quoting: all
python csv                       identical
pandas                           identical
duckdb defaults                  1 differ: [('', '<NULL>')]
duckdb allow_quoted_nulls=false  identical
postgres COPY                    identical

The lesson is not that one reader is right. A quoted empty string only means "empty string, not NULL" if the reader is configured to honor it, and writers cannot control that. If your data has both, decide on a convention and document it, for example "empty means NULL, there are no empty strings". The format guide has a table of how each reader treats the two forms.

Quote everything or only what needs it

The round trip says both modes are safe. Quoting every field makes files bigger and slightly slower to parse, and it hides nothing. It helps in two cases: a downstream tool has a quirky parser that mishandles unquoted fields, and a spreadsheet user needs numbers kept as text, though quoting does not do that in Excel, as the article on opening CSV in Excel explains. Quoting minimally is what most tools do and what I do when I write.

There is one trap in the numeric mode when you read. QUOTE_NONNUMERIC also changes the reader: it converts every unquoted field to a float. A file with a bare 5 came back as 5.0:

QUOTE_MINIMAL     [['id', 'qty'], ['a', '5'], ['b', '7']]
QUOTE_NONNUMERIC  [['a', 5.0], ['b', 7.0]]

Do not pass the same quoting argument to a reader that you used on the writer unless you want that conversion. Read with the default, and convert types deliberately.

Line breaks inside a field

A field with a line break is correct CSV, and it breaks every tool that reads by line. The record count and the line count differ, which a check that compares wc -l with an expected row count will misreport. Here a file with two data records and one line break inside the first has four lines, and three records including the header:

python - <<'PY'
import csv
with open("/tmp/q.csv", "w", newline="") as f:
    csv.writer(f, lineterminator="\n").writerows([["id", "note"], [1, "two\nlines"], [2, "x"]])
PY
wc -l /tmp/q.csv; python -c "import csv; print(sum(1 for _ in csv.reader(open('/tmp/q.csv', newline=''))), 'records')"

4 /tmp/q.csv
3 records

If a consumer cannot cope with embedded line breaks, replace them at the source with a space, or with a visible marker like \n that you document. Do not strip them silently, since the downstream data is then changed in a way that nobody told its owner. And if you need to remove them anyway, use a CSV aware step, not a tr on the whole file, which would also remove the line break at the end of every record.

Repairing a file that was written wrong

Sometimes the file already exists and the quotes were not doubled. This one has a text field that contains quotes inside quotes:

id,quote
1,"He said "hi" and left"
2,"fine"

A strict reader refuses it with ',' expected after '"'. The default Python reader accepts it and returns He said hi" and left": the inner quotes were treated as the end of the field and the text after was glued on, with one quote dropped and one kept. No error, wrong text:

strict=True -> ',' expected after '"'
default      -> ['1', 'He said hi" and left"']

There is no general repair, because the damage depends on the structure. When the structure is known, there is a safe one. This file has an integer id, one comma and then a single quoted text field that runs to the end of the line. A pattern that only matches that shape, fed into a proper writer, produces valid CSV, and it refuses to guess on any line that does not match:

"""Repair a two column file whose second column was wrapped in quotes without doubling the quotes inside it.

Only safe because the structure is known: an integer id, a comma, then one quoted text field to the end of the line.
Anything that does not match is reported, never guessed at.
"""
import csv
import re
import sys

LINE = re.compile(r'^(\d+),"(.*)"$')

reader = open(sys.argv[1], newline="").read().splitlines()
writer = csv.writer(sys.stdout, lineterminator="\n")
writer.writerow(reader[0].split(","))
for n, line in enumerate(reader[1:], start=2):
    m = LINE.match(line)
    if not m:
        sys.exit(f"line {n} does not match the expected shape: {line!r}")
    writer.writerow([m.group(1), m.group(2)])

The repaired file reads back correctly with the strict reader:

python repair_quotes.py broken-quotes.csv | tee fixed-quotes.csv
python -c "
import csv
print(list(csv.reader(open('fixed-quotes.csv', newline=''), strict=True)))"

id,quote
1,"He said ""hi"" and left"
2,fine
[['id', 'quote'], ['1', 'He said "hi" and left'], ['2', 'fine']]

The second command in that run used strict=True, and the result is the check that matters. Never trust a repair that you have not read back with the strictest reader you have.

When you cannot repair a file by hand

If the broken file has several free text columns, the quotes inside them cannot be separated from the structure, because a quote followed by a comma is ambiguous when it appears inside text. A regular expression that works on one file will mangle the next. The right fix is at the source: ask the sender to export with a library that doubles quotes, or switch to a delimiter that does not appear in the text, as the delimiter article describes. If the sender cannot change, treat the file as unreliable and add the checks from the validation guide so a bad day shows up at the door, not downstream.

The same applies to very large files. A repair script that works line by line cannot handle fields with line breaks, and a streaming approach needs the state machine that the quoting article builds. For files that fit on screen, fix the writer. For files that do not, fix the contract.