Python CSV Newlines and the Blank Line Bug on Windows

A script that writes a CSV with the standard library works on a Linux server and, run on a Windows laptop, produces a file with an empty line after every row. Excel shows a blank row between records, a database import reports short rows, and a diff is full of noise. The fix is one keyword argument that the documentation has asked for since Python 3, and which is easy to omit because the code runs without it. This article explains what happens to the bytes, reproduces the damage without needing a Windows machine, and covers the reading side, where the same missing argument corrupts data more quietly. The writer is described in the writing article, the reader in the csv module article, and the line ending conventions in the line endings article. The argument is documented under open() and in the csv module reference.

What happens to the bytes

The csv writer ends every record with \r\n (CR LF). That is its default lineterminator, and the format RFC 4180 specifies, on every platform. The trouble starts one layer lower, in the file object. In text mode Python translates line endings: on output, any \n the program writes becomes the platform's line ending. On Linux and macOS that is \n, so nothing changes. On Windows it is \r\n, so the \n that the csv writer already preceded with \r becomes \r\n and the record ends with \r\r\n. Two carriage returns and a line feed.

That is a documented behavior of the newline parameter of open(), and the csv module's documentation asks you to open files with newline="" so that no translation happens and the writer's own terminator goes through unchanged. On Linux you will never see the difference, which is why the bug survives testing and appears when someone runs your script on Windows.

Reproducing it without Windows

open() takes a newline argument that sets the translation. Giving it "\r\n" on output makes every written \n become \r\n, which is the same translation Windows applies by default. I wrote the same three rows three ways:

import csv

rows = [["id", "name"], [1, "Ana"], [2, "Bo"]]

def write(path, **open_kw):
    with open(path, "w", encoding="utf-8", **open_kw) as f:
        csv.writer(f).writerows(rows)
    return open(path, "rb").read()

# newline="" is the documented way; the writer's own \r\n goes through untouched
print("newline=''      :", write("nl-ok.csv", newline=""))
# a Windows text-mode file turns every \n written into \r\n; newline="\r\n" does the same on any OS
print("windows-style   :", write("nl-bad.csv", newline="\r\n"))
# default newline=None on Linux leaves \n alone, so the bug cannot show here
print("default, Linux  :", write("nl-default.csv"))

python /batches/csv-0001-0200/lab/py/newline_write.py

newline=''      : b'id,name\r\n1,Ana\r\n2,Bo\r\n'
windows-style   : b'id,name\r\r\n1,Ana\r\r\n2,Bo\r\r\n'
default, Linux  : b'id,name\r\n1,Ana\r\n2,Bo\r\n'

• newline="": b'id,name\r\n1,Ana\r\n2,Bo\r\n'. Correct.

• Windows-style translation: b'id,name\r\r\n1,Ana\r\r\n2,Bo\r\r\n'. Every line ends in \r\r\n.

• Default on Linux: identical to the first, because Linux's text mode writes \n as \n. This is why the default looks fine in development.

The bad file is exactly what a Windows machine would write for code that omits newline=""; the same bytes, the same readers' reactions.

The symptom

A reader splits records at line endings. A lone \r counts as a line ending in many readers, including Python's, and a \r\r\n is then one terminator (\r), an empty line, and the \r\n. So each record is followed by an empty one:

import csv

for path in ["nl-ok.csv", "nl-bad.csv"]:
    with open(path, newline="", encoding="utf-8") as f:
        print(f"{path:11s}", list(csv.reader(f)))

python /batches/csv-0001-0200/lab/py/newline_read.py

nl-ok.csv   [['id', 'name'], ['1', 'Ana'], ['2', 'Bo']]
nl-bad.csv  [['id', 'name'], [], ['1', 'Ana'], [], ['2', 'Bo'], []]

The good file reads as three records. The bad one reads as [['id', 'name'], [], ['1', 'Ana'], [], ['2', 'Bo'], []]: a blank row after every record. That is the blank line bug. A tool that skips empty lines hides it, and one that does not shows rows full of empty cells or a count that is double what it should be. Excel's behavior on the doubled terminator is widely reported to match this, with blank rows between data rows; I could not confirm it with Excel here.

Checking the bytes

Do not trust how a file looks in an editor, because most editors display \r\r\n as a single line break. Look at the bytes:

xxd nl-bad.csv | head -3
echo
printf 'doubled CR count: '; python3 -c "print(open('nl-bad.csv','rb').read().count(b'\r\r\n'))"
grep -c $'\r\r' nl-bad.csv

00000000: 6964 2c6e 616d 650d 0d0a 312c 416e 610d  id,name...1,Ana.
00000010: 0d0a 322c 426f 0d0d 0a                   ..2,Bo...

doubled CR count: 3
3

The hex dump shows 0d 0d 0a after id,name, after 1,Ana and after 2,Bo. Counting the pattern gave 3, and grep -c gave 3 lines containing it. On Windows, certutil -encodehex or PowerShell's Format-Hex do the same job. The file command did not help: it printed CSV text for the good file and for the bad one, so it is not a check for this. A one-line assertion in a test is better: assert b"\r\r\n" not in open(path, "rb").read().

The fixes

The standard one is newline="" on open(), as in every example in this section. If you want LF endings instead of CRLF, change the writer's terminator and keep newline="", so that no layer touches it:

import csv

rows = [["id", "name"], [1, "Ana"], [2, "Bo"]]
with open("nl-lf.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f, lineterminator="\n").writerows(rows)
print("lineterminator='\\n':", open("nl-lf.csv", "rb").read())

import pandas as pd
df = pd.DataFrame(rows[1:], columns=rows[0])
df.to_csv("nl-pd.csv", index=False)
print("pandas default     :", open("nl-pd.csv", "rb").read())
df.to_csv("nl-pd-crlf.csv", index=False, lineterminator="\r\n")
print("pandas \\r\\n        :", open("nl-pd-crlf.csv", "rb").read())

python /batches/csv-0001-0200/lab/py/newline_fix.py

lineterminator='\n': b'id,name\n1,Ana\n2,Bo\n'
pandas default     : b'id,name\n1,Ana\n2,Bo\n'
pandas \r\n        : b'id,name\r\n1,Ana\r\n2,Bo\r\n'

csv.writer(f, lineterminator="\n") wrote b'id,name\n1,Ana\n2,Bo\n'. LF is what Unix tools and most version control setups prefer, and it is consistent across machines. Choose it when the consumer is your own code. CRLF is the safer choice for files sent to Excel users and strict RFC consumers, which is what the default gives you.

pandas handles the opening itself. DataFrame.to_csv(path) wrote b'id,name\n1,Ana\n2,Bo\n' on this Linux machine, LF by default, and to_csv(path, lineterminator="\r\n") wrote CRLF. The parameter was called line_terminator in older versions and is lineterminator now. If you pass pandas a file object you opened yourself, open it with newline="" for the same reason as before.

The reading side

The missing newline="" on a reader does not make blank lines. It does something harder to notice. In text mode, Python reads \r\n and \r as \n, including inside quoted fields. A field that held a Windows line break, line one\r\nline two, is returned as line one\nline two, with the \r quietly dropped, as the reader article showed. That matters if you compare values to another system, hash them, or write them back out. Both ends of the pipeline need the argument.

Related traps

Opening a file in binary mode with the csv module fails in Python 3, with TypeError: a bytes-like object is required, not 'str'. That was the Python 2 idiom, open(path, "wb"), and old answers still show it. Do not use it. Appending to a file written elsewhere deserves a check of what its line endings are, because mixing them in one file produces the same confusion. And a file edited in a text editor that normalizes line endings can change \r\n to \n or the reverse, including inside quoted fields, which changes the data.

How to tell it worked

Read the first few hundred bytes of the output with xxd or Format-Hex and confirm every record ends with the sequence you meant, 0d 0a or 0a. Check that b"\r\r\n" does not occur. Read the file back with csv.reader and compare the number of records to the number written. And open it in the program the recipient uses, since that is the test that decides whether the file is acceptable.

What I could not test on Windows

I reproduced the bytes, not the platform. Windows text mode, the default newline behavior, and Excel's reaction are documented or widely reported, and I labeled them that way; the claim I verified is that these bytes, read by Python's csv.reader, give a blank record after every row. If the script that matters runs on Windows, run the byte check there once. The fix is one argument, and it does no harm on any platform.