94

I am using DictWriter to output data in a dictionary to a csv file. Why does the CSV file have a blank line in between each data line? It's not a huge deal, but my dataset is big and doesn't fit into one csv file because it has too many lines since the "double-spacing" doubles the number of lines in the file.

My code for writing to the dictionary is:

headers=['id', 'year', 'activity', 'lineitem', 'datum']
output = csv.DictWriter(open('file3.csv','w'), delimiter=',', fieldnames=headers)
output.writerow(dict((fn,fn) for fn in headers))
for row in rows:
    output.writerow(row)
myname
  • 1,337
  • 2
  • 11
  • 17

4 Answers4

132

By default, the classes in the csv module use Windows-style line terminators (\r\n) rather than Unix-style (\n). Could this be what’s causing the apparent double line breaks?

If so, in python 2 you can override it in the DictWriter constructor:

output = csv.DictWriter(open('file3.csv','w'), delimiter=',', lineterminator='\n', fieldnames=headers)
krock
  • 28,904
  • 13
  • 79
  • 85
dhwthompson
  • 2,501
  • 1
  • 15
  • 11
  • 38
    Or you can just open the output csv like this: open(filename, "wb"). It fixed that for me. – Dandré Aug 05 '13 at 06:47
  • 6
    Not sure if it's Python 3 or some other reason but when i use "wb" it writes a blank file, whereas the lineterminator = '\n' option works perfectly. As a side note, it seems counter-intuitive that you would want to write a CSV file in binary mode, considering it's a text file. Maybe that's only relevant for reading, not writing. – Davos May 02 '14 at 03:35
  • 1
    in python 3, if you use 'wb' you need to specify the encoding of your strings so that it knows how to convert your unicode text into bytes http://www.pythoncentral.io/encoding-and-decoding-strings-in-python-3-x/ afaik if you use just 'w' and you write a string it will use ascii encoding, and fail if you have any unicode chars in there. – drojf May 15 '15 at 04:14
  • 5
    The issue in Python 3 on Windows seems to be that the file object writes `\r\n` for every line, but the csv writer seems to write an additional `\r`. In a hex editor, you will see `0D 0D 0A`, which will make text editors recognize Macintosh line break style and thus display two line breaks for `\r\r` and ignore `\n`. Supplying `newline='\n'` to `open()` solved it for me (writes `\r\n`, which is correct for Excel dialect). – CodeManX Jun 18 '15 at 08:53
  • either seem to work for me in 2.7 – blindguy Nov 04 '15 at 16:54
  • 26
    [Read the docs for **python 3**](https://docs.python.org/3.5/library/csv.html): *If csvfile is a file object, it should be opened with newline=''*. That means, with python 3 you should always open to write like this: `open('some.csv', 'w', newline='')` [(source)](https://docs.python.org/3.5/library/csv.html#id3) – erik Apr 29 '16 at 18:00
  • this is a python 2 only answer – krock May 20 '22 at 07:45
31

From csv writer documentation:

If csvfile is a file object, it should be opened with newline=''

In other words, when opening the file you pass newline='' as a parameter.
You can also use a with statement to close the file when you're done writing to it.
Tested example below:

from __future__ import with_statement # not necessary in newer versions
import csv
headers=['id', 'year', 'activity', 'lineitem', 'datum']
with open('file3.csv','w', newline='') as fou:
    output = csv.DictWriter(fou,delimiter=',',fieldnames=headers)
    output.writerow(dict((fn,fn) for fn in headers))
    output.writerows(rows)
krock
  • 28,904
  • 13
  • 79
  • 85
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • 1
    Thanks! I just changed the open flag from 'w' to 'wb' and it worked, without having to add any lineterminator. f = open(file_path, 'wb') file_writer = csv.writer(f, quotechar='"', quoting=csv.QUOTE_MINIMAL) – Varun Verma Aug 23 '17 at 14:43
10

Changing the 'w' (write) in this line:

output = csv.DictWriter(open('file3.csv','w'), delimiter=',', fieldnames=headers)

To 'wb' (write binary) fixed this problem for me:

output = csv.DictWriter(open('file3.csv','wb'), delimiter=',', fieldnames=headers)

Python v2.75: Open()

Credit to @dandrejvv for the solution in the comment on the original post above.

nicholsonjf
  • 971
  • 2
  • 11
  • 21
  • When using 'wb' I found I couldn't append later on in the Python script else the final CSV would only contain the final line written. I had to open output file with 'a' – armani Nov 17 '14 at 21:55
0

I just tested your snippet, and their is no double spacing line here. The end-of-line are \r\n, so what i would check in your case is:

  1. your editor is reading correctly DOS file
  2. no \n exist in values of your rows dict.

(Note that even by putting a value with \n, DictWriter automaticly quote the value.)

tito
  • 12,990
  • 1
  • 55
  • 75