CSV in Python adding an extra carriage return, on Windows

Question

import csv

with open('test.csv', 'w') as outfile:
    writer = csv.writer(outfile, delimiter=',', quoting=csv.QUOTE_MINIMAL)
    writer.writerow(['hi', 'dude'])
    writer.writerow(['hi2', 'dude2'])

The above code generates a file, test.csv, with an extra \r at each row, like so:

hi,dude\r\r\nhi2,dude2\r\r\n

instead of the expected

hi,dude\r\nhi2,dude2\r\n

Why is this happening, or is this actually the desired behavior?

Possible duplicate of [Python 2 CSV writer produces wrong line terminator on Windows](https://stackoverflow.com/questions/1170214/python-2-csv-writer-produces-wrong-line-terminator-on-windows) — John Y, Nov 17 '17 at 17:47

score 380 · Accepted Answer · edited Mar 13 '21 at 12:56

Python 3:

The official csv documentation recommends opening the file with newline='' on all platforms to disable universal newlines translation:

with open('output.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    ...

The CSV writer terminates each line with the lineterminator of the dialect, which is '\r\n' for the default excel dialect on all platforms because that's what RFC 4180 recommends.

Python 2:

On Windows, always open your files in binary mode ("rb" or "wb"), before passing them to csv.reader or csv.writer.

Although the file is a text file, CSV is regarded a binary format by the libraries involved, with \r\n separating records. If that separator is written in text mode, the Python runtime replaces the \n with \r\n, hence the \r\r\n observed in the file.

See this previous answer.

score 262 · Answer 2 · edited Sep 19 '18 at 16:20

262

While @john-machin gives a good answer, it's not always the best approach. For example, it doesn't work on Python 3 unless you encode all of your inputs to the CSV writer. Also, it doesn't address the issue if the script wants to use sys.stdout as the stream.

I suggest instead setting the 'lineterminator' attribute when creating the writer:

import csv
import sys

doc = csv.writer(sys.stdout, lineterminator='\n')
doc.writerow('abc')
doc.writerow(range(3))

That example will work on Python 2 and Python 3 and won't produce the unwanted newline characters. Note, however, that it may produce undesirable newlines (omitting the LF character on Unix operating systems).

In most cases, however, I believe that behavior is preferable and more natural than treating all CSV as a binary format. I provide this answer as an alternative for your consideration.

edited Sep 19 '18 at 16:20

Stevoisiak

16,510
19
94
173

answered Jul 18 '13 at 13:59

Jason R. Coombs

36,798
8
75
83

2

Can you give an example of the problem that arises if you don't "encode all of your inputs to the CSV writer"? – Stephen Jul 15 '17 at 08:02
3

BEWARE: using this means `\r` is no longer escaped! Looks like this is bug in `csvwriter`, but as it stands, outputting non-conformant CSV means this is *not* the way to go. – flow2k Mar 08 '19 at 21:41
This solved the `^M` problem for me while the accepted answer's 2 suggestions did not work. – user985366 Jan 29 '20 at 10:10
Note: @flow2k reported https://bugs.python.org/issue36246 which already got wontfixed ("working as documented"). Perhaps writing lone CR character using csvwriter is uncommon, but this `lineterminator='\n'` solution, as the whole universal newlines idea in Python, just smells bad. I prefer to disable universal newlines with newline='' (or [this hack for `stdout`](https://stackoverflow.com/a/34997357/1026)). If you want to get rid of *all* CRs, not just the duplicate one, as @user985366 probably does, additionally change the dialect to `'unix'`. – Nickolay Oct 17 '20 at 12:47
1

BTW, I was wondering the same as @Stephen, and apparently _"it doesn't work on Python 3 unless you encode"_ refers to the 2010 version of the other answer, which recommended `open(..., 'wb')` without saying it applies to Python 2 only. I [don't believe it's actually possble to have the csvwriter work with a 'wb' file object in Python 3](https://stackoverflow.com/q/5358322/1026). – Nickolay Oct 17 '20 at 12:48
Finally I think that _"it may produce undesirable newlines (omitting the LF character on Unix operating systems)."_ was supposed to mean that with the `lineterminator='\n'` hack, the `excel` dialect will use `\n` on Unix, rather than `\r\n` like [it's supposed to](https://stackoverflow.com/q/49204639/1026). – Nickolay Oct 17 '20 at 12:50

Yibo Yang · Answer 3 · 2016-09-08T19:38:57.503

64

In Python 3 (I haven't tried this in Python 2), you can also simply do

with open('output.csv','w',newline='') as f:
    writer=csv.writer(f)
    writer.writerow(mystuff)
    ...

as per documentation.

More on this in the doc's footnote:

If newline='' is not specified, newlines embedded inside quoted fields will not be interpreted correctly, and on platforms that use \r\n linendings on write an extra \r will be added. It should always be safe to specify newline='', since the csv module does its own (universal) newline handling.

edited Sep 08 '16 at 19:38

answered Mar 18 '15 at 07:43

Yibo Yang

2,053
3
22
35

1

Why wouldn't this be the default behavior? – Marc Stober May 23 '18 at 12:43
2

@MarcStober because for the majority of opened files you want newlines translated to the platform default. You only ever want to set `newline=''` when you need full control over how line terminators are handled. Like when writing CSV data that’s compliant with the RFC. Note that it is **not the `CSV` module that opens the file here**. – Martijn Pieters Oct 29 '20 at 21:06

score 9 · Answer 4 · answered Oct 09 '17 at 10:21

9

You can introduce the lineterminator='\n' parameter in the csv writer command.

import csv
delimiter='\t'
with open('tmp.csv', '+w', encoding='utf-8') as stream:
    writer = csv.writer(stream, delimiter=delimiter, quoting=csv.QUOTE_NONE, quotechar='',  lineterminator='\n')
    writer.writerow(['A1' , 'B1', 'C1'])
    writer.writerow(['A2' , 'B2', 'C2'])
    writer.writerow(['A3' , 'B3', 'C3'])

answered Oct 09 '17 at 10:21

Wesam Na

1,647
21
18

1

With Python 3.5.2, this was the only thing that worked for me (well, I used just `lineterminator='\n'`); the CSV module seemed to be the origin of `\r\n`. No set of arguments to `open` had any effect. – Tommy Jan 24 '19 at 19:30

score 2 · Answer 5 · answered Jun 01 '17 at 20:41

2

You have to add attribute newline="\n" to open function like this:

with open('file.csv','w',newline="\n") as out:
    csv_out = csv.writer(out, delimiter =';')

answered Jun 01 '17 at 20:41

Gregor Ažbe

333
6
11

1

No, you need to Sen`newline=''` and give the CSV module full control over how line terminators are written. The CSV format uses `\r\n` terminators *regardless of the OS default lone terminator*. – Martijn Pieters Oct 29 '20 at 21:08
`newline=''` and `newline='\n'` have the exact same effect when opening a file for writing [according to the docs](https://docs.python.org/3/library/functions.html#open-newline-parameter) so I don't think this answer is adding anything – Boris Mar 13 '21 at 12:53

score 1 · Answer 6 · answered Oct 23 '17 at 21:39

1

Note that if you use DictWriter, you will have a new line from the open function and a new line from the writerow function. You can use newline='' within the open function to remove the extra newline.

answered Oct 23 '17 at 21:39

Erick Stone

689
1
5
16

1

This has nothing to do with DictWriter. DictWriter is a wrapper class around `CSV.writer()` and it doesn’t add newlines. There are no “extra newlines”. – Martijn Pieters Oct 29 '20 at 21:10

CSV in Python adding an extra carriage return, on Windows

6 Answers6

Python 3:

Python 2:

Linked

Related