CSV file written with Python has blank lines between each row
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] += 1 with open('/pythonwork/thefile_subset11.csv', 'w') as outfile: writer = csv.writer(outfile) for row in data: if counter[row] >= 504: writer.writerow(row)
This code reads
thefile.csv, makes changes, and writes results to
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?
In Python 2, open
outfile with mode
'wb' instead of
\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
In Python 3 the required syntax changed (see documentation links below), so open
outfile with the additional parameter
newline='' (empty string) instead.
# 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)
Read more... Read less...
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:
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
\r\n (the Windows standard line terminator) giving a result of
Fiddling with the
lineterminator is NOT the solution.
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.
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'])
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.
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.