The best answers to the question “CSV in Python adding an extra carriage return, on Windows” in the category Dev.
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:
instead of the expected
Why is this happening, or is this actually the desired behavior?
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.
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.
On Windows, always open your files in binary mode (
"wb"), before passing them to
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
\r\n, hence the
\r\r\n observed in the file.
See this previous answer.
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'])
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.