Python 2 CSV writer produces wrong line terminator on Windows

Question

According to the its documentation csv.writer should use '\r\n' as lineterminator by default.

import csv

with open("test.csv", "w") as f:
    writer = csv.writer(f)

    rows = [(0,1,2,3,4),
           (-0,-1,-2,-3,-4),
           ("a","b","c","d","e"),
           ("A","B","C","D","E")]           

    print writer.dialect.lineterminator.replace("\r", "\\r").replace("\n", "\\n")
    writer.writerows(rows)
    print writer.dialect.lineterminator.replace("\r", "\\r").replace("\n", "\\n")

This prints

\r\n
\r\n

as expected. But, the created csv-file uses the lineterminator '\r\r\n'

0,1,2,3,4

0,-1,-2,-3,-4

a,b,c,d,e

A,B,C,D,E

Is this a bug or is there something wrong in my usage of csv.writer?

Python version:

ActivePython 2.6.2.2 (ActiveState Software Inc.) based on Python 2.6.2 (r262:71600, Apr 21 2009, 15:05:37) [MSC v.1500 32 bit (Intel)] on win32

on Windows Vista

I can't reproduce your problem: $ file test.csv test.csv: ASCII text, with CRLF line terminators — , Jul 23 '09 at 07:37
@lutz: $ prompt means *x which means no difference (in Python 2.x) between text mode and binary mode; OP is running Windows. — John Machin, Jul 23 '09 at 07:56
@wierob: lose the .replace(...).replace(...), use the built-in repr() — John Machin, Jul 23 '09 at 08:00
@JohnMachin does `*x` mean Unix? Abrevs are getting shorter and shorter, — Collin Bell, Oct 19 '17 at 16:06

score 69 · Accepted Answer · edited Sep 20 '18 at 02:34

69

In Python 2.x, always open your file in binary mode, as documented. csv writes \r\n as you expected, but then the underlying Windows text file mechanism cuts in and changes that \n to \r\n ... total effect: \r\r\n

From the csv.writer documentation:

If csvfile is a file object, it must be opened with the 'b' flag on platforms where that makes a difference.

There seems to be some reticence about actually uttering the name of the main culprit :-)

Edit: As mentioned by @jebob in the comments to this answer and based on @Dave Burton's answer, to handle this case in both Python 2 and 3, you should do the following:

if sys.version_info >= (3,0,0):
    f = open(filename, 'w', newline='')
else:
    f = open(filename, 'wb')

edited Sep 20 '18 at 02:34

Blairg23

8,642
5
61
61

answered Jul 23 '09 at 07:53

John Machin

75,436
11
125
178

6

A nice "feature" is that one can still open in binary mode on platforms where it does not matter - eg, Linux, so always use binary mode. – Arafangion May 09 '10 at 05:48
3

As of 3.6, the docs now say `If csvfile is a file object, it should be opened with newline=''` – jebob Mar 10 '17 at 11:23
1

@jebob start of question, tag, start of my answer: all say Python 2.x – John Machin Mar 10 '17 at 21:08
I am using `f = sys.stdout` and it still produces these carriage returns, even on Linux, using Python 2.7. Is there a solution for this? – user5359531 Oct 15 '18 at 15:06
Also on Linux this is still producing carriage returns in my scripts under Python 2.7. – user5359531 Oct 15 '18 at 15:31

score 26 · Answer 2 · answered Jun 27 '12 at 22:08

26

Unfortunately, it's a bit different with the csv module for Python 3, but this code will work on both Python 2 and Python 3:

if sys.version_info >= (3,0,0):
    f = open(filename, 'w', newline='')
else:
    f = open(filename, 'wb')

answered Jun 27 '12 at 22:08

Dave Burton

2,552
25
18

3

I believe this is is the only solution that works on both Python 2 and 3, both Windows and Linux, and generate files that adhere to the CSV standard of `\r\n` regardless of platform. – MestreLion Feb 10 '18 at 07:45
Yes, this should be the accepted answer. Unwanted newlines in `csvwriter` is a notorious pain in Python. – smci Jul 31 '18 at 08:27
This is not working for me on either Python 2 or 3, on Linux/Mac. Its still outputing Windows style newlines. – user5359531 Oct 15 '18 at 15:49
That is what it is supposed to do, user5359531. The .csv file format is supposed to consist of lines (records) terminated with Windows-style newlines: "\r\n" If it omitted the \r that would be an error. – Dave Burton Oct 16 '18 at 18:17

Jason Callahan · Answer 3 · 2015-07-14T22:44:50.663

23

To change the line terminator in Python 2.7 csv writer use

writer = csv.writer(f, delimiter = '|', lineterminator='\n')

This is a much simpler way to change the default delimiter from \r\n.

edited Jul 14 '15 at 22:44

answered Jul 14 '15 at 22:24

Jason Callahan

456
5
6

It works and is simple!! I'm using DictWriter to stdout --- so the above solutions aren't really appropriate or add extra overhead. – ripvlan Oct 20 '16 at 20:06
1

While this works for python 2 and 3 on windows, it creates non-standard files on Linux machines: As per specification CSV files _should_ end lines with `\r\n` regardless of platform, not `\n`. – MestreLion Feb 10 '18 at 07:43
1

Good point, but sometimes it is necessary to violate standards to complete a specific task. – Jason Callahan Mar 06 '18 at 17:18
The CSV spec does not matter if you were never writing .csv files in the first place, e.g. .tsv or other delimited text formats, which in my experience is far more common than actually writing .csv. – user5359531 Oct 15 '18 at 15:58

Python 2 CSV writer produces wrong line terminator on Windows

3 Answers3

Linked

Related