Python – CSV file written with Python has blank lines between each row

csvpythonwindows

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?

Best Answer

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

Related Solutions

Dealing with commas in a CSV file

There's actually a spec for CSV format, RFC 4180 and how to handle commas:

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

^{http://tools.ietf.org/html/rfc4180}

So, to have values foo and bar,baz, you do this:

foo,"bar,baz"

Another important requirement to consider (also from the spec):

If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example:
"aaa","b""bb","ccc"

Python – Writing a list to a file with Python

You can use a loop:

with open('your_file.txt', 'w') as f:
    for item in my_list:
        f.write("%s\n" % item)

In Python 2, you can also use

with open('your_file.txt', 'w') as f:
    for item in my_list:
        print >> f, item

If you're keen on a single function call, at least remove the square brackets [], so that the strings to be printed get made one at a time (a genexp rather than a listcomp) -- no reason to take up all the memory required to materialize the whole list of strings.