用 Python 编写的 CSV 文件每行之间有空行

CSV file written with Python has blank lines between each row

提问by l--''''''---------''''''''''''

import csv

with open('thefile.csv', 'rb') as f:
  data = list(csv.reader(f))
  import collections
  counter = collections.defaultdict(int)

  for row in data:
        counter[row[10]] += 1


with open('/pythonwork/thefile_subset11.csv', 'w') as outfile:
    writer = csv.writer(outfile)
    for row in data:
        if counter[row[10]] >= 504:
           writer.writerow(row)

This code reads thefile.csv, makes changes, and writes results to thefile_subset1.

此代码读取thefile.csv、进行更改并将结果写入thefile_subset1。

However, when I open the resulting csv in Microsoft Excel, there is an extra blank line after each record!

但是，当我在 Microsoft Excel 中打开生成的 csv 时，每条记录后面都有一个额外的空行！

Is there a way to make it not put an extra blank line?

有没有办法让它不放额外的空行？

采纳答案by Mark Tolonen

In Python 2, open outfilewith mode 'wb'instead of 'w'. The csv.writerwrites \r\ninto the file directly. If you don't open the file in binarymode, it will write \r\r\nbecause on Windows textmode will translate each \ninto \r\n.

在 Python 2 中，outfile使用 mode'wb'而不是'w'. 该csv.writer写入\r\n直接到文件中。如果您不以二进制模式打开文件，它将写入，\r\r\n因为在 Windows文本模式下会将每个文件\n转换为\r\n.

In Python 3 the required syntax changed (see documentation links below), so open outfilewith the additional parameter newline=''(empty string) instead.

在 Python 3 中，所需的语法发生了变化（请参阅下面的文档链接），因此请outfile使用附加参数newline=''（空字符串）打开。

Examples:

例子：

# Python 2
with open('/pythonwork/thefile_subset11.csv', 'wb') as outfile:
    writer = csv.writer(outfile)

# Python 3
with open('/pythonwork/thefile_subset11.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile)

Documentation Links

文档链接

• https://docs.python.org/2/library/csv.html#csv.writer
• https://docs.python.org/3/library/csv.html#csv.writer

• https://docs.python.org/2/library/csv.html#csv.writer
• https://docs.python.org/3/library/csv.html#csv.writer

回答by Derek Litz

Note: It seems this is not the preferred solution because of how the extra line was being added on a Windows system. As stated in the python document:

注意：这似乎不是首选的解决方案，因为在 Windows 系统上如何添加额外的行。如python文档中所述：

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

如果 csvfile 是文件对象，则必须在有区别的平台上使用 'b' 标志打开它。

Windows is one such platform where that makes a difference. While changing the line terminator as I described below may have fixed the problem, the problem could be avoided altogether by opening the file in binary mode. One might say this solution is more "elegent". "Fiddling" with the line terminator would have likely resulted in unportable code between systems in this case, where opening a file in binary mode on a unix system results in no effect. ie. it results in cross system compatible code.

Windows 就是一个这样的平台，它会有所作为。虽然如下所述更改行终止符可能已解决问题，但可以通过以二进制模式打开文件来完全避免该问题。有人可能会说这个解决方案更“优雅”。在这种情况下，“摆弄”行终止符可能会导致系统之间的代码不可移植，在这种情况下，在 unix 系统上以二进制模式打开文件不会产生任何效果。IE。它导致跨系统兼容的代码。

From Python Docs:

来自Python 文档：

On Windows, 'b' appended to the mode opens the file in binary mode, so there are also modes like 'rb', 'wb', and 'r+b'. Python on Windows makes a distinction between text and binary files; the end-of-line characters in text files are automatically altered slightly when data is read or written. This behind-the-scenes modification to file data is fine for ASCII text files, but it'll corrupt binary data like that in JPEG or EXE files. Be very careful to use binary mode when reading and writing such files. On Unix, it doesn't hurt to append a 'b' to the mode, so you can use it platform-independently for all binary files.

在 Windows 上，附加到模式的 'b' 以二进制模式打开文件，因此还有像 'rb'、'wb' 和 'r+b' 这样的模式。Windows 上的 Python 区分文本文件和二进制文件；读取或写入数据时，文本文件中的行尾字符会自动稍微改变。这种对文件数据的幕后修改适用于 ASCII 文本文件，但它会破坏 JPEG 或 EXE 文件中的二进制数据。读写此类文件时要非常小心地使用二进制模式。在 Unix 上，将 'b' 附加到模式并没有什么坏处，因此您可以独立于平台对所有二进制文件使用它。

Original:

原文：

As part of optional paramaters for the csv.writer if you are getting extra blank lines you may have to change the lineterminator (info here). Example below adapated from the python page csv docs.Change it from '\n' to whatever it should be. As this is just a stab in the dark at the problem this may or may not work, but it's my best guess.

作为 csv.writer 的可选参数的一部分，如果您得到额外的空行，您可能需要更改换行符（此处的信息）。下面的示例改编自 python 页面csv 文档。将它从 '\n' 更改为它应该是的任何内容。由于这只是在黑暗中解决问题，这可能会或可能不会奏效，但这是我最好的猜测。

>>> import csv
>>> spamWriter = csv.writer(open('eggs.csv', 'w'), lineterminator='\n')
>>> spamWriter.writerow(['Spam'] * 5 + ['Baked Beans'])
>>> spamWriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

回答by John Machin

The simple answer is that csv files should always be opened in binary modewhether for input or output, as otherwise on Windows there are problems with the line ending. Specifically on output the csv module will write \r\n(the standard CSV row terminator) and then (in text mode) the runtime will replace the \nby \r\n(the Windows standard line terminator) giving a result of \r\r\n.

简单的答案是csv 文件应该始终以二进制模式打开，无论是输入还是输出，否则在 Windows 上会出现行尾问题。特别是在输出时，csv 模块将写入\r\n（标准 CSV 行终止符），然后（在文本模式下）运行时将替换\nby \r\n（Windows 标准行终止符），给出\r\r\n.

Fiddling with the lineterminatoris NOT the solution.

摆弄lineterminator不是解决方案。

回答by David Maddox

Opening the file in binary mode "wb" will not work in Python 3+. Or rather, you'd have to convert your data to binary before writing it. That's just a hassle.

以二进制模式“wb”打开文件在 Python 3+ 中不起作用。或者更确切地说，您必须在写入数据之前将其转换为二进制文件。那只是个麻烦事。

Instead, you should keep it in text mode, but override the newline as empty. Like so:

相反，您应该将其保持在文本模式，但将换行符覆盖为空。像这样：

with open('/pythonwork/thefile_subset11.csv', 'w', newline='') as outfile:

回答by Debanjan Dey

I'm writing this answer w.r.t. to python 3, as I've initially got the same problem.

我正在将这个答案写到 python 3，因为我最初遇到了同样的问题。

I was supposed to get data from arduino using PySerial, and write them in a .csv file. Each reading in my case ended with '\r\n', so newline was always separating each line.

我应该使用从 arduino 获取数据PySerial，并将它们写入 .csv 文件。在我的案例中'\r\n'，每次阅读都以 结束，所以换行符总是分隔每一行。

In my case, newline=''option didn't work. Because it showed some error like :

就我而言，newline=''选项不起作用。因为它显示了一些错误，例如：

with open('op.csv', 'a',newline=' ') as csv_file:

ValueError: illegal newline value: ''

So it seemed that they don't accept omission of newline here.

因此，他们似乎不接受此处省略换行符。

Seeing one of the answers here only, I mentioned line terminator in the writer object, like,

只看到这里的答案之一，我在 writer 对象中提到了行终止符，例如，

writer = csv.writer(csv_file, delimiter=' ',lineterminator='\r')

writer = csv.writer(csv_file, delimiter=' ',lineterminator='\r')

and that worked for me for skipping the extra newlines.

这对我有用，可以跳过额外的换行符。

回答by JBa

When using Python 3 the empty lines can be avoid by using the codecsmodule. As stated in the documentation, files are opened in binary mode so no change of the newline kwarg is necessary. I was running into the same issue recently and that worked for me:

使用 Python 3 时，可以使用codecs模块避免空行。如文档中所述，文件以二进制模式打开，因此无需更改换行符 kwarg。我最近遇到了同样的问题，这对我有用：

with codecs.open( csv_file,  mode='w', encoding='utf-8') as out_csv:
     csv_out_file = csv.DictWriter(out_csv)

回答by Febin Mathew

Use the method defined below to write data to the CSV file.

使用下面定义的方法将数据写入 CSV 文件。

open('outputFile.csv', 'a',newline='')

Just add an additional newline=''parameter inside the openmethod :

只需newline=''在open方法中添加一个额外的参数：

def writePhoneSpecsToCSV():
    rowData=["field1", "field2"]
    with open('outputFile.csv', 'a',newline='') as csv_file:
        writer = csv.writer(csv_file)
        writer.writerow(rowData)

This will write CSV rows without creating additional rows!

这将写入 CSV 行而不创建额外的行！

回答by SheRa

with open(destPath+'\'+csvXML, 'a+') as csvFile:
    writer = csv.writer(csvFile, delimiter=';', lineterminator='\r')
    writer.writerows(xmlList)

The "lineterminator='\r'" permit to pass to next row, without empty row between two.

"lineterminator='\r'" 允许传递到下一行，两行之间没有空行。

回答by phantom-99w

Borrowing from this answer, it seems like the cleanest solution is to use io.TextIOWrapper. I managed to solve this problem for myself as follows:

借用这个答案，似乎最干净的解决方案是使用io.TextIOWrapper. 我设法为自己解决了这个问题，如下所示：

from io import TextIOWrapper

...

with open(filename, 'wb') as csvfile, TextIOWrapper(csvfile, encoding='utf-8', newline='') as wrapper:
    csvwriter = csv.writer(wrapper)
    for data_row in data:
        csvwriter.writerow(data_row)

The above answer is not compatible with Python 2. To have compatibility, I suppose one would simply need to wrap all the writing logic in an ifblock:

上面的答案与 Python 2 不兼容。为了兼容，我想只需要将所有写入逻辑包装在一个if块中：

if sys.version_info < (3,):
    # Python 2 way of handling CSVs
else:
    # The above logic