How CSV Quoting Works and Why Embedded Commas Break Things

Take a name like Smith, Jane and write it into a comma separated line without quotes, and you have just made a record with an extra column. That is the entire reason CSV has a quoting rule, and the rule is small enough to hold in your head once you see it run. This article walks through the mechanism with a tokenizer I wrote for the purpose, then shows how it fails, and how the surrounding tools disagree about the bits the rules do not cover. The wider picture is in the CSV format guide.

What it looks like when a comma is not quoted

Here is the file from the guide. The second column of the first record holds Smith, Jane, wrapped in quotes.

id,name,note,amount,zip
1,"Smith, Jane","said ""hi""",1200.50,02134
2,Ōtsuki Ren,"two
lines",,90210
3,Dupont,,0,00501
4,"",plain, 7 ,10001

A reader who ignores the quotes and splits on commas gets this:

awk -F, 'NR>1 {print NR": NF="NF}' orders-awkward.csv | head -3
echo ---
cut -d, -f2 orders-awkward.csv | head -3

2: NF=6
3: NF=3
4: NF=3
---
name
"Smith
Ōtsuki Ren

awk reports six fields for the first record, not five, because the comma inside Smith, Jane counted as a separator. The record with the quoted newline shows up as two lines of three fields each. cut -d, -f2 printed name, then "Smith, which is a fragment of the name, then Ōtsuki Ren. Nothing failed. That is the shape of the problem: unquoted splitting is wrong silently, on exactly the rows where the data is interesting.

The four states

The rule that fixes this is a tiny state machine. A reader is always in one of four states, and each character it sees moves it to the next.

The last row is the trick. When a reader sees a quote in the quoted state it cannot yet tell whether the field is ending or a literal quote is coming, so it waits one character. Another quote means a literal. A comma or a line break means the field is over. Anything else is a malformed file.

Here is the loop in Python. The full file also handles the start of a field and the end of input, and it is 70 lines; this is the part that decides everything.

def parse(text: str) -> list[list[str]]:
    rows, row, field, state = [], [], [], FIELD_START
    i = 0
    while i < len(text):
        c = text[i]
        if state == FIELD_START:
            if c == '"':
                state = QUOTED
            elif c == ",":
                row.append("")
            elif c == "\n":
                row.append("")
                rows.append(row)
                row = []
            elif c != "\r":
                field.append(c)
                state = UNQUOTED
        elif state == UNQUOTED:
            if c == ",":
                row.append("".join(field))
                field, state = [], FIELD_START
            elif c == "\n":
                row.append("".join(field))
                rows.append(row)
                row, field, state = [], [], FIELD_START
            elif c != "\r":
                field.append(c)
        elif state == QUOTED:
            if c == '"':
                state = QUOTE_IN_QUOTED
            else:
                field.append(c)
        else:  # QUOTE_IN_QUOTED: the quote was either a doubled quote or the closing one
            if c == '"':
                field.append('"')
                state = QUOTED
            elif c == ",":
                row.append("".join(field))
                field, state = [], FIELD_START
            elif c == "\n":
                row.append("".join(field))
                rows.append(row)
                row, field, state = [], [], FIELD_START
            elif c != "\r":
                raise ValueError(f"character {c!r} after a closing quote at offset {i}")
        i += 1
    if state == QUOTED:
        raise ValueError("end of file inside a quoted field")
    if state != FIELD_START or field or row:
        row.append("".join(field))
        rows.append(row)
    return rows

Running it on the awkward file and comparing with Python's own csv.reader is the check I care about. It produced five records, the quoted comma and the quoted newline came through as data, and the output matched the standard library exactly:

python csv_states.py orders-awkward.csv

rows: 5 same as csv module: True
['id', 'name', 'note', 'amount', 'zip']
['1', 'Smith, Jane', 'said "hi"', '1200.50', '02134']
['2', 'Ōtsuki Ren', 'two\nlines', '', '90210']
['3', 'Dupont', '', '0', '00501']
['4', '', 'plain', ' 7 ', '10001']

This tokenizer is a teaching aid. It rejects text after a closing quote, ignores bare carriage returns, treats a blank line as one empty field and does no error recovery. A production reader also tracks line numbers, field size limits and the encoding. If you need to parse CSV, use one, such as the csv module. The value of writing the loop is that you can see why each failure below happens.

Three ways a quote goes wrong

Real files break the rules in three recognizable ways, and I fed each to five readers. All three files have two lines, a header and one record.

Read down a column and you see each reader's policy. Python is lenient and returns something for everything. PostgreSQL is strict about the unclosed quote and lenient about text after a closing one. DuckDB keeps the quote characters as data when it cannot make sense of them. pandas refuses only the file that cannot be tokenized at all. Read across a row and you see why a file that loads cleanly into one tool can quietly produce a different table in another: for "x"y,z, three readers produce xy, which silently drops the quotes and glues the text, and a fourth keeps the quotes.

The unterminated quote is the most expensive of the three, because everything after it is swallowed. Python returned two rows from a five line file, with the whole tail of the file inside one cell. The first line of defense is a record count that you compare with what you expect; the validation guide covers it.

Backslash is not an escape

A lot of people arrive at CSV from code, where \" is how you put a quote in a string. In CSV the only escape is a doubled quote, and a backslash is an ordinary character. Most readers follow that, and one of the most common database loaders does not.

Take C:\temp\new in a quoted field. PostgreSQL's COPY in CSV mode and Python both keep it exactly as written. MySQL's LOAD DATA has a default of ESCAPED BY '\\', so it reads \t as a tab and \n as a newline. This is the result of loading the same line on MySQL 8.4, with the hex of the stored value beside it:

id	hex	path
1	433A09656D700A6577	C:	emp
ew

The stored bytes 43 3A 09 65 6D 70 0A 65 77 are C, a colon, a tab, emp, a newline and ew. The path is gone and nothing reported a problem. Adding ESCAPED BY '' to the statement, which switches the escape character off, gives the right answer:

id	path
1	C:\temp\new

PostgreSQL, whose COPY reference says the default escape character in CSV mode is the quote itself, needed no flag:

----+-------------
  1 | C:\temp\new
(1 row)

Some writers do emit backslash escapes, for example MySQL's SELECT ... INTO OUTFILE with its default settings. If you receive a file that uses \", the quote handling has to be told about it. Without that, Python's default reader turns the cell into He said \hi\" and left" with stray characters, and with doublequote=False and escapechar="\\" it returns the intended He said "hi" and left:

default       ['1', 'He said \\hi\\" and left"']
escapechar    ['1', 'He said "hi" and left']

DuckDB accepts the same convention through its escape option, and PostgreSQL through ESCAPE E'\\'. Both returned He said "hi" and left in my run. The moral is the same as for the delimiter: the escape convention belongs in the documentation of the file.

Where quoting stops protecting you

The quoting rule protects the reader who parses the whole file with a real CSV parser. It does nothing for anyone working line by line. A quoted field can contain line breaks, so a record is not a line, and every tool that treats lines as records can cut a field in half.

I split the awkward file into pieces of three lines with split -l 3. The first piece ended inside the quoted field and the strict Python reader refused it with unexpected end of data. The second piece started in the middle of the field, so it parsed, but the first cell of its first record is now lines", a stray fragment with a closing quote and no opening one:

== /tmp/part-aa
csv.Error: unexpected end of data
== /tmp/part-ab
[['lines"', '', '90210'], ['3', 'Dupont', '', '0', '00501'], ['4', '', 'plain', ' 7 ', '10001']]

The same trap applies to head -n, tail -n, sort, grep and wc -l, and to any tool that parallelizes by cutting the file at byte offsets. For the shell, use a program that understands the format (working with CSV in the shell covers qsv, Miller and csvkit), and for large files use a splitter that respects quotes. If your data has no embedded newlines and you can promise that, line tools are fine, and promises like that tend to last until the first multi-line address.

If you are the writer, the rule for what to wrap is short: any field that contains the delimiter, a double quote or a line break, with every quote inside doubled. The article on escaping quotes and newlines shows how Python, PostgreSQL and DuckDB write it, and why wrapping every field is harmless but not required.