CSV file written with Python has blank lines betwe

Question

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?

Answer 1

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.

Answer 2

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!

Answer 3

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, so open outfile with the additional parameter newline='' 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

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

Answer 4

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.

Answer 5

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:

Answer 6

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)