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?
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.
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.
codecs.open without having to specify a newline ending, as stated in doc: "If encoding is not None, then the underlying encoded files are always opened in binary mode. No automatic conversion of '\n' is done on reading and writing."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.
\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.
^M problem for me while the accepted answer's 2 suggestions did not work.
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.lineterminator='\n' hack, the excel dialect will use \n on Unix, rather than \r\n like it's supposed to.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.
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.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'])
lineterminator='\n'); the CSV module seemed to be the origin of \r\n. No set of arguments to open had any effect.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 =';')
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.newline='' and newline='\n' have the exact same effect when opening a file for writing according to the docs so I don't think this answer is adding anythingNote 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.
CSV.writer() and it doesn’t add newlines. There are no “extra newlines”.
csv module, it also applies to csv.writer().