Python csv: eight boundaries that silently move your data
Last week I dumped a table to CSV and read it back with a second script to reconcile the numbers. The write log said two rows. The reader printed four records, two of them empty lists. The reconciliation script treated those empty rows as missing fields and flagged the whole batch as incomplete — but the file itself had not a single wrong byte. What was wrong was my assumption about what the newline argument of open() actually does.
This post collects the eight boundaries I hit in that round. Everything below was measured on Windows 11 with CPython 3.13.14 and the standard library csv module (module version 1.0); each item comes with the on-disk bytes or the read-back result, and the code can be copied as is. The layer in question is "who decides the boundary": where a field starts and where a record ends is not decided by the data, but by the dialect plus the newline mode of open(). Earlier posts in this line covered module resolution (Python imports), numeric rounding (round, Decimal and float) and inline markup (CommonMark bold next to Chinese punctuation). This one is the outermost boundary of the three.
TL;DR: csv.writer already writes CRLF, and if the text layer translates the line ending a second time the file ends up with CR CR LF; because the reader treats both CR and LF as end-of-line, every row is followed by an extra empty record (write 2 rows, read 4). Always open both sides with newline=''. A CRLF inside a quoted field is folded to LF unless you pass newline=''. QUOTE_NONNUMERIC turns every unquoted field into a float. And None versus the empty string cannot survive a round trip at all unless you use QUOTE_NOTNULL or QUOTE_STRINGS, both added in 3.12.
1. Two rows in, four records out
Start with a reproduction. First write a file the most ordinary way, then read it back the most ordinary way:
import csv
with open("demo.csv", "w", encoding="utf-8") as f:
w = csv.writer(f)
w.writerow(["name", "qty"])
w.writerow(["widget", 3])
The bytes on disk, read back with open(path, "rb").read(), look like this:
b'name,qty\r\r\nwidget,3\r\r\n'
Note the CR CR LF: two carriage returns and one line feed. csv.writer defaults lineterminator to CRLF, and text mode on Windows translates LF into CRLF again, which adds one extra CR. When reading, csv.reader treats both CR and LF as end-of-line, so CR CR LF becomes "one row ended" plus "one empty record":
rows: [['name', 'qty'], [], ['widget', '3'], []]
record count: 4 (2 rows were written)
The field values themselves are clean: qty is still 'qty' with no trailing CR. That is exactly why this is hard to find. Reconciling by field value always passes; the problem only shows up when you count records or iterate with for row in reader. Write the same data with newline='' and the disk holds b'name,qty\r\nwidget,3\r\n', which reads back as exactly two records.
The official documentation says, in both csv.reader and csv.writer, "If csvfile is a file object, it should be opened with newline=''". The explanation, though, lives in a footnote: "If newline='' is not specified, newlines embedded inside quoted fields will not be interpreted correctly, and on platforms that use \r\n line endings on write an extra \r will be added." The extra CR is documented in plain words — it is just buried in a footnote while the body text gets one sentence.
2. The newline argument controls the second of two translations
To understand that CR CR LF, separate the two steps a line ending goes through.
The first step is inside csv.writer: it builds the line ending from lineterminator (CRLF by default) and hands the string to the file object. The second step is inside io.TextIOWrapper: text mode defaults to newline=None, which translates every LF into the platform line ending (CRLF on Windows). Enable both and they stack into CR CR LF. Passing newline='' means "do not do the second translation", so only the csv step remains and the bytes are clean.
Reading works the same way in reverse. With newline=None, TextIOWrapper normalises CRLF and CR to LF during the read, before csv.reader ever sees the text. That means the content inside a field has already been modified once and csv is not looking at the bytes that are actually on disk. The next section is the consequence.
One rule of thumb worth keeping: whether a CSV file needs newline='' is decided by the bytes after writing, not by the code. The same snippet that produces CRLF on Linux produces CR CR LF on Windows — so the sentence "it works on my machine" carries almost no information on this particular path.
3. A line break inside quotes gets folded: the field changes, the row count does not
Build one record with an embedded line break and read it both ways:
import csv
with open("note.csv", "w", newline="", encoding="utf-8") as f:
w = csv.writer(f)
w.writerow(["id", "note"])
w.writerow([1, "line1\r\nline2"])
On disk: b'id,note\r\n1,"line1\r\nline2"\r\n'. The two read modes disagree:
newline='' -> [['id', 'note'], ['1', 'line1\r\nline2']]
newline=None -> [['id', 'note'], ['1', 'line1\nline2']]
The field goes from 13 characters to 12 and the CR is gone. No exception, no warning, and len(row) is unchanged. For display purposes this is invisible. If you hash field values, sign them, or the field carries formatted text, the difference is sitting right there. I ran into this on a "deduplicate by content hash" script: the same file produced two different hashes on two machines, because one read it as plain UTF-8 text while the other used newline=''.
Conversely, if you do want everything normalised to LF before inserting into a database, the default mode saves you work — but you should know you are rewriting the data rather than reading it as it is.
4. The reader only knows CR and LF: lineterminator does nothing on the way in
A common misconception is that passing the same lineterminator to writer and reader gives you a custom record separator. It does not:
import csv
with open("pipe.csv", "w", newline="", encoding="utf-8") as f:
w = csv.writer(f, lineterminator="|")
w.writerow(["a", "b"])
w.writerow(["c", "d"])
The file is b'a,b|c,d|', two rows separated by a pipe. But the reader produces the same result whether or not you pass lineterminator:
reader(default) -> [['a', 'b|c', 'd|']]
reader(lineterminator='|') -> [['a', 'b|c', 'd|']]
The whole file collapses into one record and the pipe becomes field content. The Dialect.lineterminator section of the documentation carries a note: "The reader is hard-coded to recognise either '\r' or '\n' as end-of-line, and ignores lineterminator. This behavior may change in the future." The reading side simply ignores the parameter.
So a custom record separator is only half possible: you can write it, you cannot read it back. If you genuinely need another separator, split the input yourself and hand the lines to csv.reader (it accepts any iterable of strings), or pick a different format.
5. QUOTE_NONNUMERIC has two faces
QUOTE_NONNUMERIC is asymmetric between reading and writing. When writing, it means "quote every non-numeric field". When reading, it means "convert every unquoted field to float". The second meaning is far more dangerous than the first.
Start with reading an ordinary file whose header row carries no quotes:
with open("plain.csv", "w", newline="", encoding="utf-8") as f:
f.write('code,name\n0012,"Widget"\n,empty\n')
Read that with QUOTE_NONNUMERIC and it fails on the first line:
ValueError: could not convert string to float: 'code'
The header value code is unquoted, so the parser tries to turn it into a float. That error is actually helpful: at least it stops you. The more interesting behaviour shows up once writing and reading agree on the dialect:
import csv
with open("pair.csv", "w", newline="", encoding="utf-8") as f:
w = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC)
w.writerow(["code", "qty", "price"])
w.writerow(["0012", 12, 1.5])
On disk: b'"code","qty","price"\r\n"0012",12,1.5\r\n'. Read back it is [['code', 'qty', 'price'], ['0012', 12.0, 1.5]] with types str / float / float. The leading zeros survive, because '0012' is a string and therefore quoted, but the integer 12 comes back as 12.0. Any column that mixes "numeric-looking strings" with real numbers can only be sorted out afterwards by inspecting types.
There is also a trap the documentation states outright: bool has a string representation that cannot be converted to float. I wrote True (a bool, so it was not quoted), the file holds True, and reading it back with QUOTE_NONNUMERIC gives:
ValueError: could not convert string to float: 'True'
The docs add a Note under QUOTE_NONNUMERIC: "Some numeric types, such as bool, Fraction, or IntEnum, have a string representation that cannot be converted to float. They cannot be read in the QUOTE_NONNUMERIC and QUOTE_STRINGS modes." You can write it, you cannot read it back.
6. Escape characters must be paired, or the backslash stays in your data
doublequote=False means "escape a quote inside a field with the escapechar prefix instead of doubling it". Turn off doubling without providing an escape character and the writer refuses outright:
import csv, io
buf = io.StringIO()
csv.writer(buf, doublequote=False).writerow(['he said "hi"'])
_csv.Error: need to escape, but no escapechar set
Add escapechar="\\" and the file contains b'he said \\"hi\\","a,b"\r\n'. Now read it back without the matching parameter and the backslashes remain in the data:
reader({}) -> [['he said \\"hi\\"', 'a,b']]
reader({'escapechar': '\\'}) -> [['he said "hi"', 'a,b']]
The docs describe both sides under the escapechar attribute: "On reading, the escapechar removes any special meaning from the following character. It defaults to None, which disables escaping." Disabled by default. So "escaped on write, not unescaped on read" hands you a string that still looks like legitimate data, which makes it very hard to spot by eye.
The same message has a second source: writing a field that contains the delimiter under QUOTE_NONE. csv.writer(buf, quoting=csv.QUOTE_NONE).writerow(["a,b"]) raises need to escape, but no escapechar set for the same reason — under QUOTE_NONE a comma must be hidden behind an escape character, and by default there is none.
7. BOM and field size: one swaps a key name, the other stops the read
"CSV UTF-8" files saved by Excel start with a BOM (EF BB BF). Open one with encoding='utf-8' and the BOM becomes part of the first field name:
fieldnames(utf-8): ['\ufeffid', 'name']
row: {'\ufeffid': '1', 'name': 'alpha'}
row['id'] -> KeyError: 'id'
row.get('id') -> None
A KeyError at least makes noise; row.get('id') returning None is completely silent — the row simply looks like that column is empty. The fix is one word: read with encoding='utf-8-sig' (and write with utf-8-sig if you want symmetry), and fieldnames come back as ['id', 'name']. The diagnostic is easy: print fieldnames and look for a key starting with \ufeff.
Field size is the other kind: it is not silent, but the number in the message makes people think their code is wrong. The default is 131072:
python -c "import csv; print(csv.field_size_limit())"
131072
Feed it a single 200,000-character field (152.6% of the limit) and you get:
_csv.Error: field larger than field limit (131072)
That constant is not in the documentation body; you either measure it or read the source. In CPython 3.13 the line in Modules/_csv.c is module_state->field_limit = 128 1024 * 1024) — but think first: a 10 MB single field usually means that column should not be a CSV field at all (a JSON blob or a file path fits better).
8. None and the empty string: one round trip and they are the same value
In a database, NULL and the empty string are two different values. Write them to CSV and read them back and only one remains. That is by design; the documentation says: "the value None is written as the empty string. While this isn't a reversible transformation, it makes it easier to dump SQL NULL data values to CSV files without preprocessing the data returned from a cursor.fetch* call."
Measured round trip:
write: ',,0,0\r\n'
read : [['', '', '0', '0']]
None == '' ? True
None, '', and "this column was absent" are all identical in the resulting list. DictReader adds another layer: a short row is padded with restval, which also defaults to None, so "missing column" and "value is NULL" collapse into the same None.
Python 3.12 added two constants for exactly this. QUOTE_NOTNULL quotes every field that is not None on write and writes None as an unquoted empty field; on read it interprets an unquoted empty field as None. So None and '' finally look different on disk:
disk: b'"a","b","c"\r\n,"","x"\r\n'
QUOTE_NOTNULL -> [['a', 'b', 'c'], [None, '', 'x']]
Note that the read side must use the same constant. Read the same file with the default QUOTE_MINIMAL and you get ['', '', 'x'] — None has become an empty string again. These constants are switches on both sides; changing only one side changes nothing. If you are still on 3.11 or earlier, your only option is to encode NULL yourself, for example by writing \N for NULL and agreeing on it everywhere.
Troubleshooting: six errors I reproduced and how I fixed them
All six messages below were reproduced locally; the text is copied verbatim.
Error 1: _csv.Error: iterator should return strings, not bytes (the file should be opened in text mode)
Symptom: the read dies on the first line. Cause: the file was opened with 'rb' and handed to csv.reader, so each line arrives as bytes. Fix: open in text mode with newline=''; if you must handle decoding yourself, wrap it: io.TextIOWrapper(open(p,'rb'), encoding='utf-8-sig', newline='').
Error 2: _csv.Error: field larger than field limit (131072)
Symptom: the read stops abruptly at one row after tens of thousands of good ones. Cause: a single field exceeds 131072 characters. Fix: first find out which column carries the long text; if you really need it, csv.field_size_limit(10 1024), and do not jump straight to sys.maxsize.
Error 3: _csv.Error: need to escape, but no escapechar set
Symptom: the write fails, and only on certain rows. Cause: doublequote=False or quoting=QUOTE_NONE met a character that needs escaping (quote, delimiter, newline) while escapechar is None. Fix: either keep the default doublequote=True (doubling the quote), or set escapechar explicitly and pass the same value when reading.
Error 4: ValueError: could not convert string to float: 'code'
Symptom: the read fails on the header row. Cause: quoting=QUOTE_NONNUMERIC treats the unquoted header as a number. Fix: consume the header separately (next(reader) before switching), or drop QUOTE_NONNUMERIC and convert types yourself after reading strings.
Error 5: KeyError: 'id' when reading a BOM-prefixed file
Symptom: DictReader keys look like id but lookups fail. Cause: the key is actually '\ufeffid'. Fix: encoding='utf-8-sig'; when diagnosing, print fieldnames first and look for \ufeff.
Error 6: _csv.Error: unexpected end of data
Symptom: only appears with strict=True; the default mode reports nothing. Cause: an unterminated quote. With the default strict=False the parser swallows the rest of the input into the current field, including later line breaks, so you lose rows instead of getting an error. Fix: pass strict=True for any file you did not produce yourself, so it stops at the first malformed record; and log reader.line_num, which is a physical line number rather than a record number (in my test the second record reported line_num=4 because the first spanned three lines).
9. The five checks I actually run
After writing this up I compressed the round into five checks that I now run on any new CSV:
- Search the writing code for open( and confirm every csv.writer gets newline='';
- Assert the record count after reading, not just the first row's fields — CR CR LF only changes the count;
- Print reader.fieldnames and switch to utf-8-sig the moment \ufeff shows up;
- When NULL must be distinguishable from empty, use QUOTE_NOTNULL on both sides (3.12+), otherwise define an explicit sentinel value;
- Pass strict=True for external files and put line_num into the exception log.
One more item that is not an error but costs me time every single time: csv.Sniffer. It fails outright on a single-column sample (Error: Could not determine delimiter), and on text like hello|world it guesses the delimiter is the letter o — because the documented initial value of Sniffer.preferred is [',', '\t', ';', ' ', ':'], the pipe is not in it, and whatever comes out is down to pattern-matching luck. Any "sniff first, then parse" code needs an escape hatch where a human supplies the dialect.
There is also a locale variant that turns a whole row into one field: a file that uses the full-width comma. With the default delimiter=',' nothing splits, row has exactly one element, and no error is raised. You have to pass delimiter= explicitly.
10. What this post does not cover
- Only CPython 3.13.14 on Windows 11 was tested. QUOTE_NOTNULL and QUOTE_STRINGS do not exist before 3.12, so the fix in section 8 does not apply to older versions; before 3.10 the escaping of the escapechar itself differed too (the documentation marks Changed entries in 3.10 and 3.11).
- No throughput numbers. Every conclusion here rests on small samples; the parser's speed depends on field length and quote density rather than file size, so quoting a duration would be misleading.
- Not tested: Excel's sep=, first-line convention, and old Mac files that use a bare CR as the line ending.
- Only one pandas 2.3.1 comparison (read_csv(dtype=str) keeps the leading zeros of '0012'). I did not go into how the pandas C engine and the csv module differ in quote handling — that is a separate topic.
This post contains no affiliate links. Every version number, byte sequence and error string comes from local runs plus the official CPython documentation and source, not from second-hand tutorials.
If what you are dealing with is "a one-gigabyte CSV on this machine, queried in seconds", that is out of scope here; see the DuckDB CSV benchmark. If your problem is "two strings that look identical are not equal", that belongs to the Unicode normalisation layer.
FAQ
Q: Who does newline='' actually belong to?
To the TextIOWrapper returned by open(), not to csv. It disables the text layer's newline translation so the CRLF that csv builds itself lands on disk untouched. Passing it to csv is meaningless — csv.writer has no such parameter.
Q: Why not just use pandas and skip all this?
pandas moves these boundaries into read_csv arguments (dtype, keep_default_na, na_values, encoding); the traps are still there under different names. read_csv also treats NA, N/A and null as missing by default, which can collide with your own sentinel values. The advantage of the standard library is that every layer stays visible.
Q: Is there really no way to tell None from an empty string before 3.12?
Not with the csv module's own features. None is always written as empty and always read back as '', and restval also defaults to None. Either move to 3.12+ and use QUOTE_NOTNULL, or encode at the field level (for example \N for NULL and a doubled backslash for a literal) and keep both sides to the same convention.
Q: How does this relate to the earlier post about imports?
Two directions of the same class of problem: that post asked "which file did I actually import", this one asks "did I read back one record or two". In both cases the code raises nothing, and the object your program receives is not the one you think it is.
👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference
👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation
📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews
🔗 Recommended Tools
These are carefully selected tools. Using our affiliate links supports us to keep producing quality content:
As an Amazon Associate, I earn from qualifying purchases at no extra cost to you.