Everything a CSV File Does and Does Not Promise You

A CSV file makes four promises and breaks none of them: records end at a line break, fields are separated by a delimiter, a double quote can wrap a field, and a doubled quote inside a wrapped field means one literal quote. Everything else you rely on, from the encoding to whether row one is a header, is a habit shared by the program that wrote the file and the one reading it. I fed one six-line file to six readers and got four different answers, which is why loading a CSV into PostgreSQL without losing rows starts with declaring things instead of trusting them.

This guide is the map for the rest of the CSV section. It explains what the format really guarantees, shows where common readers disagree with measured output, and ends with the reading recipe I use when the file matters. Each part links to the article that goes deeper.

One file, six readers, four different answers

Here is the file. It is only six lines, and every line of it is legal CSV. The second record has a quoted newline, the third has an empty note, the fourth has an empty quoted name, the zip column has leading zeros and the amount column has an empty value and one with padding.

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

file and a hex dump show what is actually on disk. There is no BOM, the line endings are plain LF, and wc -l counts six lines for what is really five records, because a quoted field swallowed one line break.

orders-awkward.csv: CSV text
00000000: 6964 2c6e 616d 652c 6e6f 7465 2c61 6d6f  id,name,note,amo
00000010: 756e 742c 7a69 700a 312c 2253 6d69 7468  unt,zip.1,"Smith
00000020: 2c20 4a61 6e65 222c 2273 6169 6420 2222  , Jane","said ""
00000030: 6869 2222 222c 3132 3030 2e35 302c 3032  hi""",1200.50,02
6 orders-awkward.csv

Python's csv module returns every cell as text, which is the most honest answer any reader gave. The empty values are empty strings, the leading zeros survive, and the padded " 7 " keeps its spaces.

python - <<'PY'
import csv
with open("orders-awkward.csv", newline="", encoding="utf-8") as f:
    for row in csv.reader(f):
        print(row)
PY

['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']

pandas answers differently. It treats row one as the header whether you asked or not, turns both kinds of empty into NaN, and decides that zip is an integer. The ZIP code 02134 becomes 2134 and 00501 becomes 501. It also trimmed the padded amount into the number 7.0. The str column type is new in pandas 3.

   id         name        note  amount    zip
0   1  Smith, Jane   said "hi"  1200.5   2134
1   2   Ōtsuki Ren  two\nlines     NaN  90210
2   3       Dupont         NaN     0.0    501
3   4          NaN       plain     7.0  10001
id          int64
name          str
note          str
amount    float64
zip         int64

DuckDB's CSV reader sniffed the dialect correctly, but it kept amount as text because of the padded value, and it turned the empty quoted name into NULL. That is a choice, and a reasonable one, but it is not the choice PostgreSQL made.

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ BIGINT      │ YES  │ NULL │ NULL    │ NULL  │
│ name        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ note        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ amount      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ zip         │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘
┌────┬─────────────┬────────────┬─────────┬───────┐
│ id │    name     │    note    │ amount  │  zip  │
├────┼─────────────┼────────────┼─────────┼───────┤
│ 1  │ Smith, Jane │ said "hi"  │ 1200.50 │ 02134 │
│ 2  │ Ōtsuki Ren  │ two\nlines │ NULL    │ 90210 │
│ 3  │ Dupont      │ NULL       │ 0       │ 00501 │
│ 4  │ NULL        │ plain      │  7      │ 10001 │
└────┴─────────────┴────────────┴─────────┴───────┘

SQLite's command line shell imports everything as text, because the .import command creates columns without types. The leading zeros survive, nothing becomes NULL, and an empty field is an empty string.

╭────┬─────────────┬──────────────┬─────────┬────────────────┬───────╮
│ id │    name     │ typeof(name) │ amount  │ typeof(amount) │  zip  │
╞════╪═════════════╪══════════════╪═════════╪════════════════╪═══════╡
│ 1  │ Smith, Jane │ text         │ 1200.50 │ text           │ 02134 │
│ 2  │ Ōtsuki Ren  │ text         │         │ text           │ 90210 │
│ 3  │ Dupont      │ text         │ 0       │ text           │ 00501 │
│ 4  │             │ text         │  7      │ text           │ 10001 │
╰────┴─────────────┴──────────────┴─────────┴────────────────┴───────╯

PostgreSQL's \copy tells two kinds of empty apart. The unquoted empty amount on row two is NULL. The quoted "" in the name column of row four is an empty string, and name = '' is true while name IS NULL is false. That is the documented behavior: the COPY reference for PostgreSQL 18 says the NULL option defaults to an unquoted empty string in CSV format. It is the only reader here that preserves the difference.

DROP TABLE
CREATE TABLE
COPY 4
 id | name_null | name_empty | amount  |  zip  
----+-----------+------------+---------+-------
  1 | f         | f          | 1200.50 | 02134
  2 | f         | f          |         | 90210
  3 | f         | f          |       0 | 00501
  4 | f         | t          |       7 | 10001
(4 rows)

Miller infers types per value, so its JSON output mixes numbers and strings in one column: "zip": "02134" next to "zip": 90210. That is faithful to the text but awkward for anything downstream that expects one type per column.

[
{
  "id": 1,
  "name": "Smith, Jane",
  "note": "said \"hi\"",
  "amount": 1200.50,
  "zip": "02134"
},
{
  "id": 2,
  "name": "Ōtsuki Ren",
  "note": "two\nlines",
  "amount": "",
  "zip": 90210
},
{
  "id": 3,
  "name": "Dupont",
  "note": "",
  "amount": 0,
  "zip": "00501"
},
{
  "id": 4,
  "name": "",
  "note": "plain",
  "amount": " 7 ",
  "zip": 10001
}
]

The point of the exercise is not that one tool is wrong. Each is applying a different default to a file that does not say what it means. When two teams load the same export with different tools, they get different tables.

What the format actually guarantees

Strip away what readers assume and a short list remains.

A file is a sequence of records, separated by line breaks. A record is a sequence of fields, separated by a delimiter, which is a comma in the name and often something else in practice. A field may be wrapped in double quotes, and inside a wrapped field a delimiter or a line break is data, not structure. A literal double quote inside a wrapped field is written twice.

That is the whole mechanism. RFC 4180 wrote it down in October 2005, and it describes itself as an Informational memo that "does not specify an Internet standard of any kind". It also registers the text/csv media type with the IANA media types registry, which is why web servers can label the file. The article on what the RFC actually says goes through the grammar line by line, including the surprising rule that its definition of unquoted text excludes tabs and every non-ASCII character.

What the format leaves to the reader

Every row of this table is something a program has to decide, and every row has a spoke in this cluster.

Everything in the lower half of that table is where production incidents come from. Everything in the upper half is where the first afternoon goes.

The delimiter is a convention, not a rule

The name says comma, and plenty of files use a comma. Plenty of others use a semicolon, because in many European locales the comma is the decimal separator and spreadsheet software picks a semicolon for exports. A pipe is common in banking and telecom feeds, and a tab produces what people call TSV.

There are two ways to get the delimiter wrong, and they fail differently. If you assume a comma and the file uses semicolons, the parse "succeeds" with one wide column per line, and you notice only when a later step cannot find the column it expects. If you let a sniffer guess, it gets the common cases right and a few edge cases silently wrong. On the one-column file in the delimiter detection tests, Python's csv.Sniffer raised an error, DuckDB guessed a comma and qsv sniff reported no header, which is three different outcomes for a file with no delimiter in it at all.

My rule is to treat the delimiter as an input to the job, written down next to the file name, the way you would a character set. When the choice is yours, the question to answer is how often each candidate appears inside real text. In the 389 sentences I pulled from three RFCs, 48.6% contained a comma, 3.3% a semicolon and none a pipe or a tab. That is why the comma, semicolon, tab or pipe comparison leans toward a character that never appears in your data, and why TSV versus CSV turns on whether you can promise that tabs never occur inside a field.

Quoting is the one mechanism that matters

If you learn one thing about the format, learn the quote. It is a small state machine with four states, and a tokenizer for it fits on a page. This is the core of the one I wrote to compare with Python's reader; the full file handles the field start and the row ends too.

        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}")

Run on the awkward file, it produces the same five records as the csv module, which is the check I care about:

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']

Two failure shapes come from this machine and nothing else. A quote that opens and never closes turns the rest of the file into one giant field. A quote that appears in the middle of an unquoted field, or a character after a closing quote, is not allowed, and readers disagree on how to forgive it. The quoting article shows both, and the escaping article covers the writing side, where the rule is to double the quote and wrap any field that contains a delimiter, a quote or a line break.

One more thing about quoting: backslash is not an escape character in CSV. Python and PostgreSQL treat C:\temp\new as literal text. MySQL's LOAD DATA treats its default ESCAPED BY '\\' as an escape and turned the same cell into C:, a tab, emp, a newline and ew, which I verified on MySQL 8.4 and wrote up in the quoting article.

Line endings and the record count that lies

RFC 4180 specifies CRLF, and most files written on Linux and macOS use LF. Files written by Windows tools use CRLF, and very old Mac files use a bare CR. A CSV reader that handles quoting correctly handles all three. A shell loop does not: reading the last column of a CRLF file with while IFS=, read leaves a carriage return glued to every value, and an equality test against Ana fails without any visible reason. The line endings article shows the exact bytes, and shows the PostgreSQL error you get when a file mixes CRLF and LF.

The more insidious version is the line count. wc -l reported six lines for the awkward file and Python reported five records. For a file with multi-line fields, the line count is a guess, not a record count. If your pipeline checks wc -l against a row count in a control file, a legitimate export with one embedded newline fails the check, and a broken export with an unterminated quote can pass it.

Encoding and the byte order mark

CSV has no way to say which encoding it uses. A file is bytes, and the reader chooses how to decode them. UTF-8 is the right default today, and it is still wrong often enough that the encoding problems get their own cluster, starting with fixing garbled text in CSV files.

The most common surprise is the byte order mark, three bytes at the start of the file that some Windows tools write in front of UTF-8. Of the five readers in the matrix below, pandas, DuckDB and SQLite strip it silently. Python's open with encoding="utf-8" and PostgreSQL's COPY keep it, so your first column is named \ufeffa instead of a. Anything that looks up that column by name then fails with a "missing column" error that looks unrelated to encoding.

Whether row one is a header

The format has an optional header parameter in the media type registration, and nothing in the file itself. So every reader guesses, and guessing a header is harder than it looks. Python's csv.Sniffer.has_header returned False for all four of my test files, including the two that plainly had headers. DuckDB's sniffer got three of four right and decided that a headerless file of names and cities started with a header, because every column was text and the first row looked like text too.

pandas does not guess. It treats row one as the header unless you pass header=None, which means a headerless file loses its first data row without any error. The header row article has the four test files and the PostgreSQL option, HEADER MATCH, that makes the load fail when the header does not match the table.

Types: there are none

A CSV cell is text. Anything else is inference, and inference is a cost you pay twice, once in time and once in correctness.

On a 2,000,000 row, 130 MB file with eight columns, I measured the cost of hints. Reading with no hints took 0.18 s in pandas and used 198 MB. Declaring a dtype map took 0.48 s and 110 MB, and adding date parsing took 0.87 s and 72 MB. The default was fastest because it skipped the work of deciding that zip is a string and created_at is a date. It also decided zip was an integer, and 190,451 ZIP codes lost their leading zeros. The types article has the full table, the integer precision loss that appears when a column of 64-bit ids gets one empty cell, and the date ambiguity of 03/04/2025.

I read this as: the inference is not unreasonable, the file is unclear. If a column is an identifier, it is text, whatever it looks like. Say so.

Every loader ships with a different default

The same file, a plain comma separated one with a header, goes wrong in three different ways depending on which loader you hand it to without options. This is the file:

id,name,qty
1,Ana,3
2,Bo,5

SQLite's .import splits on | until you switch to .mode csv, so the whole line lands in one column whose name is the header text:

sqlite3 :memory: ".import d-comma.csv t" ".schema t" "SELECT * FROM t;"

CREATE TABLE "t"(
"id,name,qty");
1,Ana,3
2,Bo,5

PostgreSQL's \copy without FORMAT csv reads its own text format, which is tab delimited, and refuses the first line because id,name,qty is not an integer:

CONTEXT:  COPY t3, line 1, column id: "id,name,qty"

MySQL's LOAD DATA is the quiet one. Its default field terminator is a tab, so the whole line went into the first column and the other two were filled with NULL, without an error:

id	name	qty
1,Ana,3	NULL	NULL
2,Bo,5	NULL	NULL

Two of those three failed loudly, which is the better outcome. The MySQL one produces a table that looks populated, and the first sign of trouble is a report that sums a column of NULL. The takeaway is mundane and I follow it every time: name the format in the command. FORMAT csv in PostgreSQL, .mode csv in SQLite, FIELDS TERMINATED BY ',' in MySQL. Never rely on a default you did not choose.

Empty means NULL, or an empty string, or nothing

A field with nothing in it can mean "unknown", "not applicable", "zero length" or "the person left it blank". CSV has two ways to write it, ,, and ,"",, and the specification gives them the same meaning, which is none at all.

If your downstream system distinguishes NULL from '', the choice of reader decides which one you store. PostgreSQL can preserve the distinction, which is useful when you control the writer and unhelpful when a spreadsheet wrote the file and quoted nothing. Decide the convention once and write it in the file's documentation: "empty means NULL, there are no empty strings" is a perfectly good contract.

The first five minutes with an unknown file

When someone hands me a CSV I have not seen, I run the same few commands before opening it in anything. They answer the questions in the table above without loading a single row, and they take under a second on the 2,000,000 row, 130 MB test file.

f=orders-2m.csv
file $f
printf 'encoding guess: '; uchardet $f
printf 'delimiter and header: '; qsv sniff $f | grep -E '^(Delimiter|Header Row)' | tr '\n' ' '; echo
printf 'records: '; qsv count $f
python - <<'PY'
import csv, collections
widths = collections.Counter(len(r) for r in csv.reader(open("orders-2m.csv", newline="")))
print("field counts per record:", dict(widths))
PY
qsv headers $f

orders-2m.csv: CSV text
encoding guess: ASCII
delimiter and header: Delimiter: , Header Row: true 
records: 2000000
field counts per record: {8: 2000001}
1   order_id
2   customer_id
3   zip
4   amount
5   created_at
6   status
7   country
8   note

Reading from the top: file says it is text, uchardet says ASCII (so no encoding trouble to expect), qsv sniff says comma and a header, qsv count says 2,000,000 records, and the width counter says every one of them has exactly eight fields. That last line is the one that earns its keep. A file whose field counts are all equal is very likely well formed, and a file with a second number in that dictionary has a ragged row that some reader will treat differently from another. The width check is a Python one-liner because Python's reader is the strictest about quoting while remaining forgiving about width, which is the combination you want for diagnosis.

If the width check reports more than one count, I stop and find the first offending line number before going anywhere near a loader. The validation guide turns this into a repeatable check.

Where readers disagree: the conformance matrix

The six-line file is a sample. To see the general pattern, I wrote 23 tiny files, one per edge case, with the expected result for each valid one, and ran them through Python, pandas, DuckDB, SQLite and PostgreSQL with default settings. Cells say ok when the reader returned exactly the expected rows. For the invalid files there is no right answer, so the cell shows the number of rows the reader returned, a star when SQLite warned, or ERROR.

case                  python    pandas    duckdb    sqlite  postgres
plain                     ok        ok        ok        ok        ok
crlf                      ok        ok        ok        ok        ok
no-final-newline          ok        ok        ok        ok        ok
quoted-comma              ok        ok        ok        ok        ok
doubled-quote             ok        ok        ok        ok        ok
embedded-newline          ok        ok        ok        ok        ok
embedded-crlf             ok        ok        ok        ok        ok
empty-fields              ok        ok        ok        ok        ok
quoted-empty              ok        ok        ok        ok        ok
trailing-comma            ok        ok        ok        ok        ok
leading-zeros             ok        ok        ok        ok        ok
spaces-kept               ok        ok        ok        ok        ok
accents                   ok        ok        ok        ok        ok
bom                     DIFF        ok        ok        ok      DIFF
semicolon-file            ok        ok      DIFF        ok        ok
quoted-numbers            ok        ok        ok        ok        ok
long-field             ERROR        ok        ok        ok        ok
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

Three things stand out. All 13 cases that are valid and ordinary, from quoted commas to embedded CRLF, pass everywhere, so the readers agree on the part the RFC describes. The disagreements are all in the BOM, in sniffing, in size limits and in malformed input. And the invalid files are where behavior diverges most: for a bare quote inside an unquoted field, four readers returned two rows and PostgreSQL refused the file.

The detail lines show the exact messages, which are worth reading because they are the strings people search for.

bom                python    expected [["a", "b"], ["1", "2"]]
                             got      [["a", "b"], ["1", "2"]]
bom                postgres  expected [["a", "b"], ["1", "2"]]
                             got      [["a", "b"], ["1", "2"]]
semicolon-file     duckdb    expected [["a;b"], ["1;2"]]
                             got      [["a", "b"], ["1", "2"]]
long-field         python    Error: field larger than field limit (131072)
blank-line         python    [["a", "b"], ["1", "2"], [], ["3", "4"]]
blank-line         pandas    [["a", "b"], ["1", "2"], ["3", "4"]]
blank-line         duckdb    [["a", "b"], ["1", "2"], ["3", "4"]]
blank-line         sqlite    [["a", "b"], ["1", "2"], ["", ""], ["3", "4"]]   [warning: expected 2 columns but found 1 - filling the rest with NULL]
blank-line         postgres  RuntimeError: ERROR:  missing data for column "c1"
ragged-long        python    [["a", "b"], ["1", "2", "3"]]
ragged-long        pandas    ParserError: Error tokenizing data. C error: Expected 2 fields in line 2, saw 3 
ragged-long        duckdb    [["1", "2", "3"]]
ragged-long        sqlite    [["a", "b"], ["1", "2"]]   [warning: expected 2 columns but found 3 - extras ignored]
ragged-long        postgres  RuntimeError: ERROR:  extra data after last expected column
ragged-short       python    [["a", "b", "c"], ["1", "2"]]
ragged-short       pandas    [["a", "b", "c"], ["1", "2", ""]]
ragged-short       duckdb    [["a,b,c"], ["1,2"]]
ragged-short       sqlite    [["a", "b", "c"], ["1", "2", ""]]   [warning: expected 3 columns but found 2 - filling the rest with NULL]
ragged-short       postgres  RuntimeError: ERROR:  missing data for column "c2"
bare-quote         python    [["a", "b"], ["x\"y", "z"]]
bare-quote         pandas    [["a", "b"], ["x\"y", "z"]]
bare-quote         duckdb    [["a", "b"], ["x\"y", "z"]]
bare-quote         sqlite    [["a", "b"], ["x\"y", "z"]]
bare-quote         postgres  RuntimeError: ERROR:  unterminated CSV quoted field
text-after-quote   python    [["a", "b"], ["xy", "z"]]
text-after-quote   pandas    [["a", "b"], ["xy", "z"]]
text-after-quote   duckdb    [["a", "b"], ["\"x\"y", "z"]]
text-after-quote   sqlite    [["a", "b"], ["x\"y,z\n", ""]]   [warning: unescaped " character]
text-after-quote   postgres  [["a", "b"], ["xy", "z"]]
unterminated       python    [["a", "b"], ["x,y\n1,2\n"]]
unterminated       pandas    ParserError: Error tokenizing data. C error: EOF inside string starting at row 1
unterminated       duckdb    [["a", "b"], ["\"x", "y"], ["1", "2"]]
unterminated       sqlite    [["a", "b"], ["x,y\n1,2\n", ""]]   [warning: unterminated "-quoted field]
unterminated       postgres  RuntimeError: ERROR:  unterminated CSV quoted field

Take the long-field row. Python's csv module refuses a field over 131,072 characters by default, with field larger than field limit (131072), which is a real production error covered in the Python field limit article. And take semicolon-file, where DuckDB split a;b into two columns, which is what you want from a sniffer when the file really is semicolon separated and what you do not want when the file is a single column of text that happens to contain a semicolon.

Ragged and truncated input

The invalid files in the matrix are the ones I care about most, because real exports break in these ways. A row with too many fields, a row with too few, a stray quote and an unterminated quote are each a different bug upstream, and each reader handles them with a different policy.

Python's reader never complains about width. It returned the long row as three cells and the short row as two, and leaves counting to you. pandas refuses a long row with Expected 2 fields in line 2, saw 3 and pads a short one with an empty cell. DuckDB reads a long row as a single row with three values, and for the short row it sniffed the file as a single column. SQLite imports both and prints a warning to stderr, which a script that only looks at the exit status will never see. PostgreSQL refuses both, with extra data after last expected column and missing data for column "c2".

Truncation is the version of this that looks like success. If an export is cut off mid-record, the last line is short, and Python and pandas accept it without a word. The truncated last line test is one of the cheapest checks you can add to a pipeline, and it only works because a CSV has no end-of-file marker, so nothing in the file proves it is complete.

An unterminated quote deserves a note of its own. The rest of the file becomes one field, so a reader that does not complain returns a short file with one enormous cell. Python returned two rows from my five-line test. DuckDB, which sniffs, found three rows by treating the stray quote as data. Only pandas, SQLite and PostgreSQL stopped. If that surprises you, it is the same behavior as an unterminated string in a programming language: the parser has no way to know where you meant it to end.

A defensive recipe for reading a CSV you care about

When a file feeds something that matters, I stop letting the reader guess, in this order.

First, check that the file is shaped the way I think. A record count and a width check cost seconds and catch the damage that no parser will report. This is the whole check for two files, one clean and one with a row that has an extra field.

python - <<'PY'
import csv
for f in ("orders-awkward.csv", "orders-ragged.csv"):
    with open(f, newline="") as fh:
        widths = [len(r) for r in csv.reader(fh)]
    print(f"{f:20} lines={sum(1 for _ in open(f))}  records={len(widths)}  widths={sorted(set(widths))}")
PY

orders-awkward.csv   lines=6  records=5  widths=[5]
orders-ragged.csv    lines=4  records=4  widths=[5, 6]

The widths of [5, 6] tell me the second file has a ragged row before I load anything. Second, declare the dialect, the columns and strict mode, and turn off every option that quietly repairs bad input. With DuckDB that looks like this, and on the awkward file it returns exactly the five records, as text where I said text:

duckdb -box -c "
SELECT * FROM read_csv('orders-awkward.csv',
  header = true, delim = ',', quote = '\"', escape = '\"',
  columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'note': 'VARCHAR', 'amount': 'VARCHAR', 'zip': 'VARCHAR'},
  strict_mode = true, null_padding = false, ignore_errors = false);"

┌────┬─────────────┬────────────┬─────────┬───────┐
│ id │    name     │    note    │ amount  │  zip  │
├────┼─────────────┼────────────┼─────────┼───────┤
│ 1  │ Smith, Jane │ said "hi"  │ 1200.50 │ 02134 │
│ 2  │ Ōtsuki Ren  │ two\nlines │ NULL    │ 90210 │
│ 3  │ Dupont      │ NULL       │ 0       │ 00501 │
│ 4  │ NULL        │ plain      │  7      │ 10001 │
└────┴─────────────┴────────────┴─────────┴───────┘

Run on the ragged file, the same command refuses to guess:

Invalid Input Error: Error when sniffing file "orders-ragged.csv".
It was not possible to automatically detect the CSV parsing dialect

* Columns are set as: "columns = { 'id' : 'INTEGER', 'name' : 'VARCHAR', 'note' : 'VARCHAR', 'amount' : 'VARCHAR', 'zip' : 'VARCHAR'}", and they contain: 5 columns. It does not match the number of columns found by the sniffer: 6. Verify the columns parameter is correctly set.

That second message is the useful one. It tells me that the file has six columns where I declared five, which is the bug, and it does so before a single row reaches a table. For other engines the same idea is spelled differently. PostgreSQL's COPY with HEADER MATCH checks the header, a table with the right column types rejects a bad value, and the load article for PostgreSQL shows how to quarantine bad rows instead of failing the whole load. The MySQL import guide, the SQLite import guide and the DuckDB guide do the same for their engines.

Writing a CSV that other people can read

Most of this guide has been about readers, but you are also a writer. The conventions that cost the least are these.

Write UTF-8, and if the consumer is Excel on Windows, write it with a BOM, because without one Excel guesses a legacy code page. Write a header row and keep column names unique, lowercase and free of spaces. Use a comma unless your data is full of commas, in which case use a tab and forbid tabs in the data. Quote with the minimum rule, which means wrapping a field only when it contains the delimiter, a quote or a line break. Write dates as 2025-03-04 and numbers with a point and no thousands separator. Write empty for missing and say so. Pick one line ending and keep it.

Then add a few lines of documentation next to the file: the encoding, the delimiter, the meaning of an empty cell, and the type of each column. A plain text description is enough, and the W3C's CSV on the Web primer describes a machine-readable version if you want one. When someone asks why their ZIP codes lost a zero, that page is the answer, and the data contract article goes through what to ask a provider to promise.

Where each problem is covered

If you arrived with a problem rather than a curiosity, this is the shortest route. Text turned into question marks or accented letters turned into strange pairs: garbled text in CSV files. A file you need in a database: PostgreSQL, MySQL, SQLite, or skip the load and run SQL straight on the file with DuckDB. A script rather than a tool: Python, the shell or Node. A file that is dirty: a repeatable cleaning routine and validation before you trust it. A file that is too big: handling files too big to open. A file Excel opened and damaged: opening CSV in Excel without damage. The browser-side formatter and converter pages under the tools section are useful for small samples, though a production file should be read by code you can rerun.

For testing your own parser, the edge case cheat sheet lists the files behind the matrix above.

When a CSV file is the wrong container

CSV is the right format when the data is flat, the audience is mixed and the file is a hand-off. It stops being right in a few specific situations, each with a person behind it.

If you are an analyst running the same aggregation on a 5 GB export every morning, each run re-parses text and re-infers types. Convert it once to Parquet and query that; the Parquet comparison measures the gap. Here is the same 2,000,000 row table in the four forms I could write with one DuckDB command each:

orders-2m.csv           129.9 MB
orders-2m.csv.gz         42.1 MB
orders-2m.jsonl         312.7 MB
orders-2m.parquet        34.2 MB
orders-2m.tsv           129.9 MB

The CSV is 129.9 MB, the same data as Parquet with zstd compression is 34.2 MB, and the JSON Lines version is 312.7 MB, more than twice the CSV because every row repeats every key. Gzip alone gets the CSV to 42.1 MB, but you still pay to parse text on every read, and you still carry no types. If you are an API developer who needs nesting, arrays or booleans that stay booleans, use JSON and accept a larger file, because forcing a nested object into columns recreates the schema problem with extra steps. If the receiving team sends you workbooks with several sheets, merged cells and formulas, the file is not tabular data at all, and a CSV export will drop most of what they care about. And if the data must be shared with a strict contract, a type system and versioning, a CSV with a README is a weak substitute for a schema-carrying format.

The honest summary is that CSV survives because everything can read it, not because it is precise. Treat it as a hand-off where the contract lives outside the file, and the guesses in this guide stop costing you rows.