A Cheat Sheet of CSV Edge Cases to Test Every Parser With

Every CSV parser works on the files its author tried. The test suite that tells you where it stops is a folder of tiny files, each one chosen to break an assumption, with the right answer written next to it. This is that folder as a reference: thirty six cases, a table for the valid ones, the notes for the files with no right answer, and the harness I used to run five readers through all of them. The conclusions in the format guide come from the same run.

The valid cases and what each reader did

For these files there is a correct answer, defined by the quoting rules in RFC 4180 and common practice. A cell says nothing when all five readers returned exactly the expected records. \n and \r below are line feed and carriage return, and <BOM> is the three bytes EF BB BF.

Nineteen of the twenty five rows say "none" in the last column, which is the headline. The ordinary things that a tutorial shows, quoted commas, doubled quotes and embedded line breaks, are handled by every reader. The misses sit in the rest: encodings and markers, sniffing, size limits, and unusual line endings. Four of those, the BOM, the long field, bare CR and mixed endings, deserve a closer look.

The entries people get wrong

The byte order mark. Three bytes at the start of the file, written by many Windows programs when they save "UTF-8". Python opened with encoding="utf-8" and PostgreSQL's COPY both keep it, so the first column is called a, which prints identically to a and does not equal it. Opening with encoding="utf-8-sig" strips it in Python, and HEADER match in PostgreSQL would report it as a mismatch. The article on the UTF-8 BOM covers when to write one.

The long field. Python's csv module rejects a field longer than 131,072 characters with field larger than field limit (131072). It is a deliberate guard, and a surprise the first time a log column or an embedded document passes through. Raising csv.field_size_limit fixes it; the dedicated article shows how far to raise it.

The bare carriage return. A file whose only line break is \r is unusual now, and it still comes from old Mac software and some mainframe exports. SQLite's shell did not split on it and imported the whole file as one record whose second cell was b\r1. The other four read it correctly. The line endings article has the same files and the MySQL equivalent.

Mixed line endings. A file whose header ends in CRLF and whose records end in LF comes from concatenating files produced on different systems. PostgreSQL rejects it with unquoted newline found in data, DuckDB's sniffer fails to detect a dialect, and the other three read it without complaint. When a pipeline concatenates files, check the seams.

Files with no right answer

Some inputs are not valid CSV, or are valid and ambiguous. The point of a case here is not a pass or fail but knowing what your reader does. These were the results, as the number of records returned, with a star for SQLite warning on stderr:

case                    python    pandas    duckdb    sqlite  postgres
empty-file                  ok     ERROR        ok     ERROR        ok
header-only                 ok        ok        ok        ok        ok
lone-newline            1 rows     ERROR    1 rows    1 rows    1 rows
trailing-blank-lines    4 rows    2 rows    2 rows   4 rows*     ERROR
single-col-blank        3 rows    2 rows    3 rows    3 rows    3 rows
cr-only                     ok        ok        ok      DIFF        ok
mixed-eol                   ok        ok     ERROR        ok     ERROR
comma-in-header             ok        ok        ok        ok        ok
only-a-quote                ok        ok        ok        ok        ok
tab-in-field                ok        ok        ok        ok        ok
emoji                       ok        ok        ok        ok        ok
latin1-bytes             ERROR     ERROR     ERROR    2 rows     ERROR
nul-byte                2 rows    2 rows    2 rows    2 rows    2 rows

And for the cases from the first set that have no right answer, here are the numbers from the guide's matrix:

case                  python    pandas    duckdb    sqlite  postgres
blank-line            4 rows    3 rows    3 rows   4 rows*     ERROR
ragged-long           2 rows     ERROR    1 rows   2 rows*     ERROR
ragged-short          2 rows    2 rows    2 rows   2 rows*     ERROR
bare-quote            2 rows    2 rows    2 rows    2 rows     ERROR
text-after-quote      2 rows    2 rows    2 rows   2 rows*    2 rows
unterminated          2 rows     ERROR    3 rows   2 rows*     ERROR

Reading the two together:

Blank lines. In the middle of a file (blank-line), Python returned an empty record, pandas and DuckDB skipped it, SQLite loaded a row of empty values with a warning, and PostgreSQL rejected the file with missing data for column. Trailing blank lines follow the same pattern. A single column file is the case where a blank line is a legitimate empty value: pandas dropped it, Python returned an empty record, and DuckDB, SQLite and PostgreSQL kept an empty cell. There is no correct reading, only a policy, and if you count rows you need to know yours.

Ragged rows. Too many fields or too few were accepted by Python, pandas (short rows only) and SQLite with a warning, rejected by PostgreSQL, and given a different shape each time by DuckDB. A reader that accepts them is no safer than one that rejects: it just moves the problem to whoever counts columns.

Quote problems. A bare quote inside an unquoted field and text after a closing quote were read leniently by almost everyone. An unterminated quote was tolerated by Python and DuckDB, warned about by SQLite, and rejected by pandas and PostgreSQL. The quoting article works through all three.

Latin 1 bytes. A file with a single byte E9 where UTF-8 expects a multibyte sequence failed in Python, pandas, DuckDB and PostgreSQL, each with a different message: PostgreSQL said invalid byte sequence for encoding "UTF8": 0xe9 0x0a. SQLite imported the byte without checking and returned a replacement character when my harness decoded it. The repair is in converting Latin 1 and Windows 1252 to UTF-8.

NUL bytes. A NUL inside a field was kept by Python, pandas and DuckDB as a real \u0000 character, and SQLite and PostgreSQL returned an empty field for it. I did not chase the mechanism for the last two. A NUL byte in a text column is a problem for most databases, and the article on removing null bytes and control characters is the one to use.

How to use the list

The harness is a few functions, one per reader, and a dictionary of cases. Adding a new parser means writing one function that takes a path and returns a list of rows. These are the three Python ones:

def t_python(path, w):
    with open(path, newline="", encoding="utf-8") as f:
        return [list(r) for r in csv.reader(f)]


def t_pandas(path, w):
    df = pd.read_csv(path, header=None, dtype=str, keep_default_na=False)
    return df.values.tolist()


def t_duckdb(path, w):
    con = duckdb.connect()
    rows = con.execute("SELECT * FROM read_csv(?, header=false, all_varchar=true)", [path]).fetchall()
    return [["" if v is None else v for v in r] for r in rows]

I run the full set when a library is upgraded or when I introduce a new tool into a pipeline. A parser that fails a valid case is not necessarily wrong for your data, but you now know the case exists. Keep the files themselves in version control with *.csv -text in .gitattributes, as the line ending article explains, or Git will quietly rewrite the CRLF cases.

If you are building your own parser, start from the first fourteen rows, because those are the cases a user reports first. If you are choosing between libraries, run the whole sheet and compare the misses with what your data contains. A library that fails on the BOM is a problem for files from Windows, and one that fails on a long field is a problem for logs.

What this list does not test

It covers the structure of the file and the bytes around it. It does not cover type inference, which is where most data loss in practice happens; the types article measures that. It does not cover encodings other than UTF-8 and Latin 1, so UTF-16 files, which Excel can export, need their own test. It does not test performance on large files or memory use. It does not test formula injection, the risk that a cell starting with = runs as a formula when opened in a spreadsheet, or anything about security.

Nor does it cover locale conventions, such as a decimal comma, a semicolon delimiter or a date format. If your producers are all in one place and use one tool, you may need only a handful of these cases. If you accept files from the public, you need all of them and more.