CSV file written with Python has blank lines between each row

user_117700 0
Viewed 461413
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?

csv windows python
11 Answers

A lot of the other answers have become out of date in the ten years since the original question. For Python3, the answer is right in the documentation:

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

The footnote explains in more detail:

If newline='' is not specified, newlines embedded inside quoted fields will not be interpreted correctly, and on platforms that use \r\n linendings on write an extra \r will be added. It should always be safe to specify newline='', since the csv module does its own (universal) newline handling.

user_4942120

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!

user_40431050

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.

user_66464040 
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.

user_113826920

Borrowing from this answer, it seems like the cleanest solution is to use io.TextIOWrapper. I managed to solve this problem for myself as follows:

from io import TextIOWrapper

...

with open(filename, 'wb') as csvfile, TextIOWrapper(csvfile, encoding='utf-8', newline='') as wrapper:
    csvwriter = csv.writer(wrapper)
    for data_row in data:
        csvwriter.writerow(data_row)

The above answer is not compatible with Python 2. To have compatibility, I suppose one would simply need to wrap all the writing logic in an if block:

if sys.version_info < (3,):
    # Python 2 way of handling CSVs
else:
    # The above logic
user_1719930

I used writerow

def write_csv(writer, var1, var2, var3, var4):
    """
    write four variables into a csv file
    """
    writer.writerow([var1, var2, var3, var4])      
        
numbers=set([1,2,3,4,5,6,7,2,4,6,8,10,12,14,16])
rules = list(permutations(numbers, 4))
#print(rules)
selection=[]
with open("count.csv", 'w',newline='') as csvfile:
    writer = csv.writer(csvfile)

    for rule in rules:
        number1,number2,number3,number4=rule
        if ((number1+number2+number3+number4)%5==0):
            #print(rule)
            selection.append(rule)
            write_csv(writer,number1,number2,number3,number4)
user_40011770

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)
user_12515920
Related