CSV Parser Guide: Delimiters, Quotes, and Encodings

A one-million-row product export, 60.7 MB, five columns. Splitting each line on the comma runs in 0.30 seconds and gets the field count wrong for 199,341 rows, because one product name in five holds a comma inside quotes. A real reader takes 0.81 seconds and gets every row right. Half a second is the whole cost of not writing your own parser. This article is part of our overview of document parsing.

Everything below was run on 13 August 2026 against Python 3.11.15, pandas 3.0.2, Node 22.22.2 with papaparse 5.5.4 and csv-parse 7.0.2, PHP 8.4.21, Go 1.24.7, DuckDB 1.5.5 and polars 1.43.2. One machine, warm cache. Your timings will differ; the failure modes will not.

There is no CSV standard, and that is the whole problem

The nearest thing to a specification is RFC 4180, October 2005. It is Informational, not a standard, and it opens by admitting the format was already decades old and undocumented: "Surprisingly, while this format is very common, it has never been formally documented." A CSV file carries none of its encoding, delimiter, quoting rule, escape rule, column types, or whether row one is a header. Six unknowns, none in band. Parsers guess, and they guess differently. W3C's Model for Tabular Data and Metadata on the Web has been a Recommendation since December 2015 for exactly this, and almost nobody ships one.

Why "split on the comma" is a bug

CSV has escaping: a value containing the delimiter is wrapped in quotes, and quotes inside a value are doubled.

sku,name,price
101,"Coffee grinder, manual",34.90
102,"Kettle ""Retro""",21.90

"Coffee grinder, manual" is one value, not two, and Kettle "Retro" contains quotes. A manual split breaks on both. A value can also hold a line break inside its quotes, so even "one line, one row" is unsafe.

Delimiters, encodings, and the BOM

The delimiter is often a semicolon, which is what Excel writes in locales where the comma is the decimal separator. Tabs and the vertical bar turn up too. A semicolon file usually also carries 34,90 in its price column, so the wrong delimiter and the wrong number format arrive together.

Excel's sep= hint does not help. Excel writes a first line reading sep=; to declare the delimiter, and no CSV library honours it. Fed that line above a normal header, Python's csv module returns a header of ['sep=', ''], pandas ['sep=', 'Unnamed: 1'], Papa Parse {"sep=": "sku", "": "name"}. All three silently shift the file down a row. Strip it first.

The BOM is where a working script fails on Tuesday. Files exported from Excel often begin with a byte order mark, an invisible three-byte prefix that glues itself to the first column name.

The readers that keep it are where the bug hides best: the value prints identically in a terminal.

Encoding is a question to ask, not to answer. Files from older systems arrive in windows-1252, windows-1251 or Latin-1. We encoded five CSV files in known legacy codepages, asked both common Python detectors to name the encoding, then decoded with the answer and compared against the original:

Five right out of ten, one of them from charset-normalizer. chardet got the short German file right and the long one wrong, and never reported confidence above 0.24 on non-ASCII input. Detection is salvage. For a recurring feed, get the encoding in writing.

Python

The standard library's csv module covers most jobs. DictReader returns each row as a dictionary keyed by the headers.

import csv

with open("prices.csv", encoding="utf-8-sig", newline="") as f:
    reader = csv.DictReader(f, delimiter=";")
    for row in reader:
        print(row["sku"], row["name"], row["price"])

Both arguments in that open() call are working. encoding="utf-8-sig" strips the BOM if present, harmless if not. newline="" is not cosmetic: without it a \r\n inside a quoted field becomes \n before the parser sees it, so the field you read is not the field written. The docs say it "should always be safe to specify newline=''".

Two more edges. csv.Sniffer().sniff() raises _csv.Error: Could not determine delimiter on a single-column file, which is valid CSV. And the parser refuses any field over 131,072 characters with _csv.Error: field larger than field limit (131072). One embedded HTML blob trips it. Raise the limit deliberately with csv.field_size_limit().

For large files and immediate aggregation, reach for pandas. Version 3.0.0 shipped 21 January 2026, needs Python 3.11 or newer, and returns strings as a dedicated str dtype instead of object. A frame writes back out to CSV, Excel or JSON in one call.

What type inference does to your identifiers

Type inference is where CSV quietly corrupts data.

sku,zip,ean,country,flag
007,01234,4006381333931,NA,NULL

Read that with pd.read_csv() and the row comes back as {'sku': 7, 'zip': 1234, 'ean': 4006381333931, 'country': nan, 'flag': nan}. Leading zeros gone. NA, the ISO country code for Namibia, is now a missing value, and so is the string NULL.

Worse is what one blank cell does to large integers. A column holding 9007199254740993 and 9007199254740995 with one empty field between them cannot be int64, because there is no integer NaN, so pandas falls back to float64. Those values read back as 9007199254740992 and 9007199254740996: wrong by one, still sixteen digits, no error raised. Past 2^53 a float64 cannot represent consecutive integers, and order IDs and EANs live well past that line. A code column containing 2E5 returns 200000.0 from pandas, polars and DuckDB alike.

The fix belongs in every ingest script that touches identifiers:

df = pd.read_csv("prices.csv", dtype=str, keep_default_na=False)

Read identifiers as text, convert only the columns you do arithmetic on. For nullable integers ask by name with dtype="Int64", which preserved the value exactly.

JavaScript / Node.js

In the browser and in Node the most popular parser is Papa Parse, on 5.5.4 since 19 June 2026. It auto-detects the delimiter, reads headers, strips the BOM, and streams: the default answer for a nodejs csv parser.

const Papa = require("papaparse");
const fs = require("fs");

const file = fs.readFileSync("prices.csv", "utf-8");
const result = Papa.parse(file, { header: true, skipEmptyLines: true });

for (const row of result.data) {
  console.log(row.sku, row.name, row.price);
}

Read result.errors before result.data. Papa never throws, it reports. On a single-column file it emits UndetectableDelimiter and carries on with a comma, the opposite of Python's Sniffer. Server-side pipelines more often use csv-parse, on 7.0.2 since 2 August 2026 and stricter by design.

PHP

The built-in fgetcsv handles quoting, enough for simple jobs. It also has a defect worth knowing before you trust it.

<?php
$f = fopen("prices.csv", "r");
$headers = fgetcsv($f, 0, ";", "\"", "");

while (($row = fgetcsv($f, 0, ";", "\"", "")) !== false) {
    $record = array_combine($headers, $row);
    echo $record["sku"] . " — " . $record["name"] . "\n";
}
fclose($f);

That fifth argument is not optional any more, and the earlier version of this article omitted it. PHP's CSV functions carry a proprietary backslash escape that RFC 4180 does not have, and it corrupts data. Writing ["1", "C:\", "trailing backslash"] with fputcsv produces 1,"C:\","trailing backslash", and reading that back with the default escape returns two fields instead of three. Three columns become two, silently. Pass "" and the round trip is exact. On 8.4.21 the short call prints, verbatim: Deprecated: fgetcsv(): the $escape parameter must be provided as its default value will change. The manual says the default changes no earlier than PHP 9.0.

$separator must also be a single byte: fgetcsv($f, 0, "‖") throws ValueError: fgetcsv(): Argument #3 ($separator) must be a single character. Beyond one-off scripts, use league/csv, on its version 9 line, which has querying and encoding conversion without the escape landmine.

Go

Go's standard library has encoding/csv. Go 1.26 is current, released 10 February 2026, and the package has gained no method since InputOffset in Go 1.19.

f, _ := os.Open("prices.csv")
defer f.Close()

r := csv.NewReader(f)
r.Comma = ';'
rows, err := r.ReadAll()
if err != nil {
    panic(err)
}
// encoding/csv does not strip the BOM. Do it yourself.
rows[0][0] = strings.TrimPrefix(rows[0][0], "\ufeff")

for _, row := range rows[1:] { // skip the header
    fmt.Println(row[0], row[1], row[2])
}

Comma is a rune, not a byte, so a multi-byte separator such as '‖' works where PHP refuses. FieldsPerRecord locks onto the first record's width; set -1 to accept ragged input. LazyQuotes turns a fatal extraneous or missing " in quoted-field into a best-effort parse: right for salvage, wrong for a feed you control. For fetching rows off the web, see our Go web scraping guide.

When rows do not have the same field count

Real exports are ragged: a trailing delimiter here, an unescaped quote there. No two readers agree. Given a,b,c, then 1,2,3, 4,5, 6,7,8,9:

Read the pandas row twice. It raises on a row that is too long and invents a value for a row that is too short. Same defect in the file, one fatal, one invisible.

DuckDB earns its own paragraph: its auto-detection is the best here and its failure is the most dramatic. The sniffer picks the dialect yielding consistent column counts "with the maximum number of columns". Here that maximum is four, so it concluded four columns and no header, set skip=3, and returned one row: (6, 7, 8, 9). Header and two of three data rows discarded, nothing reported. With store_rejects=true the same call returns (1, 2, 3) and logs MISSING COLUMNS and TOO MANY COLUMNS to a rejects table. The tool is fine; the default is a trap on small inconsistent input.

Pick a policy per feed and encode it: reject, quarantine, or pad. Never leave it to whichever library you imported.

What breaks when there are ten thousand files

On the 60.7 MB file from the opening, median of five runs each:

DictReader costs 1.9x the time of csv.reader for named fields: nothing on one file, twenty minutes across ten thousand. The columnar readers are five to six times faster than the pandas C engine, and engine="pyarrow" is a one-word change. Peak memory, not speed, kills nightly jobs: streaming holds 11 MiB at any size, a frame 204 MiB for 60 MB.

Sniffers only look at the beginning. DuckDB samples 20,480 rows by default and considers only ,, |, ; and tab. A file whose first 20,000 rows are clean and whose row 400,000 carries an unescaped quote passes detection and fails at load, or worse, does not fail. Pin the dialect rather than re-detect nightly.

Writers own one more bug. CSV injection fires when a cell beginning with =, +, -, @ or a tab is read as a formula by Excel or LibreOffice Calc. OWASP is direct that quoting does not save you: "The above techniques are not reliable in Microsoft Excel after saving and re-opening the CSV file."

When CSV stops being enough

CSV is fine while the data is flat. Meet nesting, several tables in one file, formatting, formulas or merged cells, and it becomes Excel parsing. Hard ceilings apply: a worksheet holds 1,048,576 rows by 16,384 columns and a cell 32,767 characters, so a CSV past any of those cannot round-trip through a spreadsheet. The reverse is why CSV survives, since a source workbook too messy to read directly is often exported to CSV first. For an export of events or log lines, see parsing TXT and parsing logs.

How much inferred types cost is on record. In a study published in Genome Biology on 23 August 2016, Ziemann, Eren and El-Osta screened 35,175 supplementary Excel files from 3,597 papers and found gene name errors from type conversion in 19.6% of the papers carrying gene lists.

If your exports arrive with drifting encodings, a different delimiter from one dump to the next, or broken quotes, the work is normalisation before it is parsing: pin the dialect per source, read identifiers as text, quarantine ragged rows, check the field count. Feeds that never stop moving are where managed parsing and data extraction or a maintained data feed earns its keep.