CSV file written with Python has blank lines between each row

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

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

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