CSV by the book: RFC 4180 and dialects

The name Rao, Vikram holds a comma, so a program that cuts the line 7,"Rao, Vikram",Pune at every comma finds 4 fields in a record of 3, and every column after the name moves one place. RFC 4180 settles how such a value travels: between double quotes, with every quote inside it doubled, so a reader that follows its rules gets Rao, Vikram back in one piece.

You will write and read CSV by those rules, trace a reader through the four states that make quoting work, set the delimiter, quote, escape and line terminator of a dialectdialect (CSV): The writing choices that a CSV reader must share with the writer: the delimiter, the quote character, how a quote inside a field is escaped, the line ending and when fields are quoted. Python names them in a csv.Dialect., and choose what an import does with the rows that break the format: ragged rowsragged row: A CSV record with more or fewer fields than the header. RFC 4180 says every line should have the same number of fields; readers differ in whether they refuse such a row, pad it or keep it., stray quotes and fields that never close.

RFC 4180 has seven rules, and quoting carries the hard ones

RFC 4180 is short. Fields are separated by commas, records end with CR LF, and a field that holds a comma, a double quote or a line break is written as a quoted fieldquoted field: A CSV field written between double quotes, so that commas, line breaks and quotes can be part of its value. RFC 4180 writes a quote inside it twice: "" stands for one quote of data., between double quotes, with every quote inside it doubled. Its seven rules, and where the program below meets each one:

This program first shows what cutting at commas does to the line from the start of the lesson. Then it writes a header and four records that need every rule with csv.writer and reads them back with csv.reader. The JavaScript tab writes the same text with two functions of one line each:

"""Records that need every quoting rule of RFC 4180, written by csv.writer and read back by csv.reader."""

import csv
import io

ROWS = [
    ["id", "name", "address"],
    ["7", "Rao, Vikram", "Pune"],
    ["8", 'Asha "AJ" Iyer', "Kochi"],
    ["9", "Meena", "12 FC Road\r\nPune"],
    ["10", " Ravi", ""],
]


def visible(text):
    """The text with every line break written out, so that CR LF shows as \\r\\n."""
    return text.replace("\r", "\\r").replace("\n", "\\n\n")


line = '7,"Rao, Vikram",Pune'
print("split at commas:", line.split(","))
print("csv.reader:     ", next(csv.reader([line])))
print()

out = io.StringIO()
csv.writer(out).writerows(ROWS)
text = out.getvalue()
print(visible(text), end="")
print()

back = list(csv.reader(io.StringIO(text, newline="")))
print("read back equal:", back == ROWS)
print("Meena's record:", back[3])

split at commas: ['7', '"Rao', ' Vikram"', 'Pune']
csv.reader:      ['7', 'Rao, Vikram', 'Pune']

id,name,address\r\n
7,"Rao, Vikram",Pune\r\n
8,"Asha ""AJ"" Iyer",Kochi\r\n
9,Meena,"12 FC Road\r\n
Pune"\r\n
10, Ravi,\r\n

read back equal: True
Meena's record: ['9', 'Meena', '12 FC Road\r\nPune']

// RFC 4180 by hand: quote a field that holds a comma, a quote, CR or LF, and double the quotes inside it.
const field = (value) => (/[",\r\n]/.test(value) ? `"${value.replaceAll('"', '""')}"` : value);
const record = (fields) => fields.map(field).join(",") + "\r\n";

const rows = [
  ["id", "name", "address"],
  ["7", "Rao, Vikram", "Pune"],
  ["8", 'Asha "AJ" Iyer', "Kochi"],
  ["9", "Meena", "12 FC Road\r\nPune"],
  ["10", " Ravi", ""],
];

// The text with every line break written out, so that CR LF shows as \r\n.
const visible = (text) => text.replaceAll("\r", "\\r").replaceAll("\n", "\\n\n").replace(/\n$/, "");

console.log("split at commas:", JSON.stringify('7,"Rao, Vikram",Pune'.split(",")));
console.log();
console.log(visible(rows.map(record).join("")));

split at commas: ["7","\"Rao"," Vikram\"","Pune"]

id,name,address\r\n
7,"Rao, Vikram",Pune\r\n
8,"Asha ""AJ"" Iyer",Kochi\r\n
9,Meena,"12 FC Road\r\n
Pune"\r\n
10, Ravi,\r\n

The cut gave four pieces, two of them with a stray quote; the reader gave the three fields. The writer quoted only the fields that needed it, which is Python’s default, QUOTE_MINIMAL. Meena’s address spans two lines of the file, because the CR LF inside its quotes is data, and the reader gave every record back exactly: read back equal: True. The JavaScript field() is the whole writing rule in one line: quote a value that holds ", ,, CR or LF, and double its quotes.

RFC 4180 is an Informational RFC, and its status section says plainly that it sets no Internet standard. What it does is write down the format most programs were already producing and register the media type text/csv, with two optional parameters: charset for the encoding, and header=present or header=absent. A revision draft, known as 4180bis, expired without becoming an RFC, so RFC 4180 is still the text to cite; RFC 7111 adds addresses for parts of a file, such as #row=4 and #cell=4,1.

A reader needs four states to follow the quotes

A comma means one thing in 7, and another in "Rao, . A reader tells them apart by remembering where it is: at the start of a field, inside an unquoted field, inside a quoted field, or just after a quote inside a quoted field, where it cannot yet tell a closing quote from the first half of a doubled one. That memory makes the reader a state machineA program that is always in one of a fixed set of states and moves between them by rules, one input at a time: here, one character of the text. with four states. Step through it on the record 7,"Rao, ""Vik""",Pune:

Every character either keeps the state or moves the reader along one arrow, and three moves are errors. This table is every rule the figure followed:

The third error has no row: the text ends while the reader is still inside a quoted field. By then the reader has gone past many line breaks, all of them data, so a useful message names the line and column where that field’s quote opened, not the end of the text. The exercise at the end of this lesson builds exactly this reader.

A dialect names every choice the RFC leaves open

RFC 4180 fixes the comma, the double quote, doubled quotes and CR LF, and real files differ on all four: some separate fields with semicolons, tabs or pipes, some escape a quote with a backslash instead of doubling it, and many end their lines with LF alone. A dialect names those choices, and a reader must use the dialect of the writer. This program writes the same rows in six dialects and reads each back with the dialect that wrote it, then reads two of them with the wrong one:

"""The same rows in six dialects, each read back with the dialect that wrote it, then two mismatches."""

import csv
import io

ROWS = [
    ["id", "name", "amount"],
    ["7", "Rao, Vikram", "1250.50"],
    ["8", "Asha|AJ", ""],
    ["9", 'Ravi "R" Nair', None],
]

DIALECTS = {
    "comma (excel)": {},
    "semicolon": {"delimiter": ";"},
    "tab (excel-tab)": {"dialect": "excel-tab"},
    "pipe": {"delimiter": "|"},
    "unix": {"dialect": "unix"},
    "backslash escapes": {"doublequote": False, "escapechar": "\\"},
}


def visible(text):
    """Tabs and line breaks written out: \\t, \\r\\n and \\n."""
    return text.replace("\t", "\\t").replace("\r", "\\r").replace("\n", "\\n\n")


def write(rows, **dialect):
    out = io.StringIO()
    csv.writer(out, **dialect).writerows(rows)
    return out.getvalue()


def read(text, **dialect):
    return list(csv.reader(io.StringIO(text, newline=""), **dialect))


expected = [["" if v is None else v for v in row] for row in ROWS]
for name, dialect in DIALECTS.items():
    text = write(ROWS, **dialect)
    print(f"{name}:")
    print(visible(text), end="")
    print("  read back equal:", read(text, **dialect) == expected)

print("QUOTE_NOTNULL keeps None and '' apart:")
text = write(ROWS, quoting=csv.QUOTE_NOTNULL)
print(visible(text), end="")
print("  read back:", read(text, quoting=csv.QUOTE_NOTNULL)[2:])

print("semicolon file read as comma:", read(write(ROWS, delimiter=";"))[1])
print("escaped file read as excel:  ", read(write(ROWS, **DIALECTS["backslash escapes"]))[3])

comma (excel):
id,name,amount\r\n
7,"Rao, Vikram",1250.50\r\n
8,Asha|AJ,\r\n
9,"Ravi ""R"" Nair",\r\n
  read back equal: True
semicolon:
id;name;amount\r\n
7;Rao, Vikram;1250.50\r\n
8;Asha|AJ;\r\n
9;"Ravi ""R"" Nair";\r\n
  read back equal: True
tab (excel-tab):
id\tname\tamount\r\n
7\tRao, Vikram\t1250.50\r\n
8\tAsha|AJ\t\r\n
9\t"Ravi ""R"" Nair"\t\r\n
  read back equal: True
pipe:
id|name|amount\r\n
7|Rao, Vikram|1250.50\r\n
8|"Asha|AJ"|\r\n
9|"Ravi ""R"" Nair"|\r\n
  read back equal: True
unix:
"id","name","amount"\n
"7","Rao, Vikram","1250.50"\n
"8","Asha|AJ",""\n
"9","Ravi ""R"" Nair",""\n
  read back equal: True
backslash escapes:
id,name,amount\r\n
7,"Rao, Vikram",1250.50\r\n
8,Asha|AJ,\r\n
9,Ravi \"R\" Nair,\r\n
  read back equal: True
QUOTE_NOTNULL keeps None and '' apart:
"id","name","amount"\r\n
"7","Rao, Vikram","1250.50"\r\n
"8","Asha|AJ",""\r\n
"9","Ravi ""R"" Nair",\r\n
  read back: [['8', 'Asha|AJ', ''], ['9', 'Ravi "R" Nair', None]]
semicolon file read as comma: ['7;Rao', ' Vikram;1250.50']
escaped file read as excel:   ['9', 'Ravi \\"R\\" Nair', '']

Every dialect read back equal, and the quoting followed the delimiter: Rao, Vikram needed quotes only where the comma separates fields, and Asha|AJ only in the pipe file. The unix dialect quotes every field and ends lines with LF. The backslash dialect wrote Ravi \"R\" Nair with no quotes around it at all: with doublequote=False and an escapechar, Python escapes the quote instead of quoting the field. The last two lines are what a mismatch does. The semicolon file read as commas split at the comma inside the name, and the escaped file read as excel kept its backslashes, and neither raised an error.

The same choices have names in every library. Python takes them as arguments or as a named dialect, excel, excel-tab or unix:

Two of Python’s reading rules surprise people. The reader ignores lineterminator and ends a record at \r or \n either way. And RFC 4180 has no way to write a missing value: an empty field is all it has. QUOTE_NOTNULL keeps the two apart, as the program showed: it writes None as an empty field and an empty string as "", and reads them back the same way.

Tab-separated values have a registration of their own, text/tab-separated-values, and it solves the quoting problem by forbidding tabs inside fields: TSV has no quote character at all. Python’s excel-tab dialect is Excel’s tab format, which quotes fields as the comma dialect does, so a file it writes is not always TSV by that registration.

Ragged rows and broken quotes need a rule you choose

Rule 4 asks for the same number of fields on every line, and the export below breaks it twice. Python’s DictReader does not refuse such a file. It fills missing fields with None and puts extra ones in a list under the key None:

"""An export with a short row, a long row and a blank line: what DictReader does, then two policies."""

import csv
import io

TEXT = (
    "id,name,city\r\n"
    "1,Asha,Pune\r\n"
    "2,Ravi\r\n"
    "3,Meena,Kochi,Ernakulam\r\n"
    "\r\n"
    "4,Kiran,Indore\r\n"
)

print("DictReader with its defaults:")
reader = csv.DictReader(io.StringIO(TEXT, newline=""))
for row in reader:
    print(f"  line {reader.line_num}: {row}")


def split_rows(text):
    """The header, the records that match it and the line numbers of those that do not."""
    reader = csv.reader(io.StringIO(text, newline=""))
    header = next(reader)
    good, bad = [], []
    for fields in reader:
        if not fields:  # a blank line
            continue
        if len(fields) == len(header):
            good.append(fields)
        else:
            bad.append((reader.line_num, len(fields)))
    return header, good, bad


header, good, bad = split_rows(TEXT)
line, count = bad[0]
print(f"Reject the file: line {line} has {count} fields, the header has {len(header)}")
print(f"Quarantine: load {len(good)} rows, set aside {[f'line {n} ({c} fields)' for n, c in bad]}")

DictReader with its defaults:
  line 2: {'id': '1', 'name': 'Asha', 'city': 'Pune'}
  line 3: {'id': '2', 'name': 'Ravi', 'city': None}
  line 4: {'id': '3', 'name': 'Meena', 'city': 'Kochi', None: ['Ernakulam']}
  line 6: {'id': '4', 'name': 'Kiran', 'city': 'Indore'}
Reject the file: line 3 has 2 fields, the header has 3
Quarantine: load 2 rows, set aside ['line 3 (2 fields)', 'line 4 (4 fields)']

Ravi’s city became None, Meena’s extra field went under the key None, the blank line vanished, and the loop carried on. A ragged row becomes wrong data unless the import counts fields against the header. The program’s split_rows() does that count, then shows two policies: reject the whole file at line 3, or load the 2 rows that fit and set lines 3 and 4 aside.

The right policy depends on what the data is for, so decide it for an import of your own:

Quotes break in more ways. This program gives csv.reader four broken quotes, once with its defaults and once with strict=True:

"""Four broken quotes, read by csv.reader with its defaults and with strict=True, then the field size limit."""

import csv
import io

CASES = {
    "text after a closing quote": 'a,"b"c,d\r\n',
    "a space before an opening quote": 'a, "b,c"\r\n',
    "a quote inside an unquoted field": 'a,5" disk,d\r\n',
    "a quote that never closes": 'a,"b\r\nc,d\r\ne,f\r\n',
}

for name, text in CASES.items():
    print(name)
    for strict in (False, True):
        reader = csv.reader(io.StringIO(text, newline=""), strict=strict)
        try:
            print(f"  strict={strict}: {list(reader)}")
        except csv.Error as e:
            print(f"  strict={strict}: csv.Error at line {reader.line_num}: {e}")

print("skipinitialspace=True:", next(csv.reader(['a, "b,c"'], skipinitialspace=True)))

print("field_size_limit():", csv.field_size_limit())
reader = csv.reader(io.StringIO('id,note\r\n1,"' + "x" * 200_000 + '"\r\n', newline=""))
try:
    for row in reader:
        pass
except csv.Error as e:
    print(f"csv.Error at line {reader.line_num}: {e}")

text after a closing quote
  strict=False: [['a', 'bc', 'd']]
  strict=True: csv.Error at line 1: ',' expected after '"'
a space before an opening quote
  strict=False: [['a', ' "b', 'c"']]
  strict=True: [['a', ' "b', 'c"']]
a quote inside an unquoted field
  strict=False: [['a', '5" disk', 'd']]
  strict=True: [['a', '5" disk', 'd']]
a quote that never closes
  strict=False: [['a', 'b\r\nc,d\r\ne,f\r\n']]
  strict=True: csv.Error at line 3: unexpected end of data
skipinitialspace=True: ['a', 'b,c']
field_size_limit(): 131072
csv.Error at line 2: field larger than field limit (131072)

By default the reader repairs what it can without a word: "b"c became bc, and a quote that never closed swallowed the rest of the text, so 3 records came back as one. With strict=True both raise csv.Error, but for the unclosed quote reader.line_num says line 3, where the text ended, not line 1, where the quote opened. Strict mode still lets two breaks of RFC 4180 through: a quote inside an unquoted field, 5" disk, and a space before an opening quote, which turns "b,c" into two broken fields unless you pass skipinitialspace=True.

The last lines show a guard the module gives you: a field longer than field_size_limit(), 131,072 characters by default, raises field larger than field limit (131072). In a file of short notes, that error is usually an unclosed quote that has started to swallow the rows after it.

Whichever policy you choose, write it down beside the import and test it with a file that breaks each rule once: a short row, a long row, text after a closing quote and a quote that never closes.

Practise: a reader that says where a quote went wrong

The exercise builds the four-state reader of this lesson, and makes it stricter than Python’s: it refuses a quote inside an unquoted field, and it reports each error with its line and column, for an unclosed quote the place where the quote opened:

class CsvError(ValueError):
    """A break of the rules of RFC 4180, with its line and column."""


def read_csv(text):
    """The records of `text` as lists of strings, by the rules of RFC 4180."""
    return [line.split(",") for line in text.splitlines()]

from reader import CsvError, read_csv


def error_of(text):
    """The message of the CsvError read_csv(text) raises, or None."""
    try:
        read_csv(text)
    except CsvError as e:
        return str(e)
    return None


def test_plain_records():
    """reads plain records ended by CR LF"""
    assert read_csv("id,name\r\n7,Asha\r\n") == [["id", "name"], ["7", "Asha"]]


def test_quoted_comma_and_doubled_quotes():
    """keeps a comma inside quotes and turns "" into one quote"""
    assert read_csv('7,"Rao, ""Vik""",Pune\r\n') == [["7", 'Rao, "Vik"', "Pune"]]


def test_line_break_inside_quotes():
    """keeps a CR LF inside a quoted field as data"""
    assert read_csv('9,"12 FC Road\r\nPune"\r\n10,Ravi\r\n') == [["9", "12 FC Road\r\nPune"], ["10", "Ravi"]]


def test_lf_and_no_final_break():
    """accepts LF alone and a last record without a line break"""
    assert read_csv("a,b\nc,d") == [["a", "b"], ["c", "d"]]


def test_empty_fields_and_lines():
    """keeps empty fields, quoted or not, and a blank line as one empty field"""
    assert read_csv('a,,""\r\n,\r\n\r\nb\r\n') == [["a", "", ""], ["", ""], [""], ["b"]]
    assert read_csv("") == []


def test_text_after_closing_quote():
    """reports text after a closing quote at its line and column"""
    message = error_of('id,name,city\r\n7,"Rao"x,Pune\r\n')
    assert message == "line 2, column 8: a closing quote must be followed by a comma or a line break"


def test_quote_inside_unquoted_field():
    """reports a quote in the middle of an unquoted field"""
    assert error_of('a,5" disk\r\n') == "line 1, column 4: a quote inside an unquoted field"


def test_quote_that_never_closes():
    """reports the opening quote of a field still open at the end"""
    message = error_of('id,note\r\n1,"never closed\r\n2,x\r\n')
    assert message == "line 2, column 3: the quoted field that starts here never closes"


def test_lines_inside_quotes_count():
    """counts the line breaks inside quoted fields"""
    message = error_of('a,"x\r\ny"\r\nb,"c"d\r\n')
    assert message == "line 3, column 6: a closing quote must be followed by a comma or a line break"

Then test yourself on the rules, the reader’s states, dialects and broken input. The session opens with whichever of the four you are least sure of, and each choice you make is explained, right or wrong:

Interview questions

Warm-up (fresher to 2–5 years): how does RFC 4180 write a value that contains a double quote, and why?

The whole field goes between double quotes, and each quote inside the value is written twice: Asha "AJ" Iyer becomes "Asha ""AJ"" Iyer". RFC 4180 needs both halves of that rule. A quote inside an unquoted field is not allowed at all, so the field must be quoted, and inside a quoted field a single quote would end the field, so the reader needs a way to tell a closing quote from a quote that belongs to the value. After a quote, it looks at the next character: another quote means one quote of data, and a comma or a line break means the field has ended. Libraries that use a backslash instead, such as Python with doublequote=False and an escapechar, write another dialect, and the reader must be told to use it.

Two harder questions follow, about columns that moved after a new supplier joined and about an import that reported success with rows missing. Decide what you would check first, then open each first step:

Key takeaways

• RFC 4180 separates fields with commas, ends records with CR LF and quotes a field that holds a comma, a quote or a line break, doubling each quote inside it; cutting lines at commas breaks the first quoted field.
• A reader follows quotes with four states: field start, unquoted field, quoted field and quote inside quotes, where the next character decides between a doubled quote and the end of the field.
• A dialect names the delimiter, quote character, escape rule, line ending and quoting policy; the reader must use the writer’s, because a mismatch gives wrong fields without an error.
• Python’s csv module repairs broken input in silence by default: DictReader fills ragged rows with None, and an unclosed quote swallows the rows after it, so count fields, read with strict=True and report the line.