CSV in Python adding an extra carriage return, on

问题:

In Python 2.7 running on Windows XP pro:

import csv
outfile = file(\'test.csv\', \'w\')
writer = csv.writer(outfile, delimiter=\',\', quoting=csv.QUOTE_MINIMAL)
writer.writerow([\'hi\',\'dude\'])
writer.writerow([\'hi2\',\'dude2\'])
outfile.close()

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

test.csv

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?

回答1:

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

CSV is really a binary format, 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\" that you observed in your file.

See this previous answer.

---

This answer was posted in 2010 and does not address the problem in Python3.

One of the possible fixes in Python3, as described in @YiboYang\'s answer, is opening the file with the newline parameter set to be an empty string:

f = open(path_to_file, \'w\', newline=\'\')
writer = csv.writer(f)
...
...

回答2:

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.

回答3:

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.

回答4:

I\'m not sure exactly why it is happening, but changing your file mode from \"w\" to \"wb\" fixes it. See my answer to \"how to remove ^M\" for more details.

回答5:

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 =\';\')

回答6:

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.

回答7:

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\'])