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

问题:用Python编写的CSV文件每行之间都有空行

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)

该代码读取thefile.csv,进行更改并将结果写入thefile_subset1

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

有没有办法使它不放在多余的空白行?

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.

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

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


回答 0

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

在Python 3中,所需的语法已更改(请参见下面的文档链接),因此请outfile使用附加参数newline=''(空字符串)打开。

例子:

# 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)

文档链接

In Python 2, open outfile with mode 'wb' instead of 'w'. The csv.writer writes \r\n into the file directly. If you don’t open the file in binary mode, it will write \r\r\n because on Windows text mode will translate each \n into \r\n.

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

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


回答 1

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

相反,您应该将其保留在文本模式下,但是将换行符替换为空。像这样:

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

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.

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:

回答 2

简单的答案是,无论输入还是输出,都应始终以二进制模式打开csv文件,否则在Windows上,行尾出现问题。具体上输出csv模块将写\r\n(标准CSV行终止),然后(在文本模式)运行时将取代\n通过\r\n(Windows标准线路终端),得到的结果\r\r\n

摆弄lineterminator不是解决方案。

The simple answer is that csv files should always be opened in binary mode whether 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 \n by \r\n (the Windows standard line terminator) giving a result of \r\r\n.

Fiddling with the lineterminator is NOT the solution.


回答 3

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

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

Windows是其中一个与众不同的平台。虽然按照我下面所述更改行终止符可能已解决了该问题,但可以通过以二进制模式打开文件来完全避免该问题。有人可能会说这种解决方案更“优雅”。在这种情况下,用行终止符“摆弄”可能会导致系统之间无法移植的代码,在此情况下,在UNIX系统上以二进制模式打开文件不会产生任何效果。即。它导致跨系统兼容的代码。

Python Docs

在Windows上,附加到模式的’b’以二进制模式打开文件,因此也有’rb’,’wb’和’r + b’之类的模式。Windows上的Python区分文本文件和二进制文件。当读取或写入数据时,文本文件中的行尾字符会自动更改。对于ASCII文本文件来说,对文件数据进行这种幕后修改是可以的,但它会破坏JPEG或EXE文件中的二进制数据。读写此类文件时,请务必小心使用二进制模式。在Unix上,将’b’附加到该模式没有什么坏处,因此您可以在平台上独立地将其用于所有二进制文件。

原件

作为csv.writer的可选参数的一部分,如果您获得多余的空行,则可能必须更改lineterminator(信息此处)。以下示例是从python页面csv docs改编的 将其从“ \ 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'])

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:

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

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.

From Python Docs:

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.

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.

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

回答 4

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

我应该使用来从arduino获取数据PySerial,并将其写入.csv文件中。在我的情况下'\r\n',每个读数都以结尾,因此换行符总是分隔每行。

就我而言,newline=''选项无效。因为它显示了一些错误,例如:

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

ValueError: illegal newline value: ''

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

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

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

这对我来说是多余的换行符。

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

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.

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

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 = csv.writer(csv_file, delimiter=' ',lineterminator='\r')

and that worked for me for skipping the extra newlines.


回答 5

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

“ lineterminator =’\ r’”允许传递到下一行,而在两行之间没有空行。

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.


回答 6

这个答案中借用,似乎最干净的解决方案是使用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)

上面的答案与Python 2不兼容。为了具有兼容性,我想一个人只需要将所有写入逻辑包装在一个if块中即可:

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

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:

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 if block:

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

回答 7

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

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

只需newline=''open方法内部添加一个附加参数:

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

这将写入CSV行,而不会创建其他行!

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

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

Just add an additional newline='' parameter inside the open method :

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!


回答 8

使用Python 3时,可以使用编解码器模块避免出现空行。如文档中所述,文件以二进制模式打开,因此不需要更改换行符kwarg。我最近遇到了同样的问题,对我有用:

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

When using Python 3 the empty lines can be avoid by using the codecs module. 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:

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