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    gt   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

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

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  lt   3          Python 2 way of handling CSVs else        The above logic

User · 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   https   docs python org 2 library csv html csv writer https   docs python org 3 library csv html csv writer

User · Answer

Note  It seems this is not the preferred solution because of how the extra line was being added on a Windows system   As stated in the python document      If csvfile is a file object  it must be opened with the    b    flag on platforms where that makes a difference    Windows is one such platform where that makes a difference   While changing the line terminator as I described below may have fixed the problem  the problem could be avoided altogether by opening the file in binary mode   One might say this solution is more  elegent     Fiddling  with the line terminator would have likely resulted in unportable code between systems in this case  where opening a file in binary mode on a unix system results in no effect   ie  it results in cross system compatible code   From Python Docs       On Windows   b  appended to the mode   opens the file in binary mode  so   there are also modes like  rb    wb     and  r b   Python on Windows makes a   distinction between text and binary   files  the end-of-line characters in   text files are automatically altered   slightly when data is read or written    This behind-the-scenes modification to   file data is fine for ASCII text   files  but it   ll corrupt binary data   like that in JPEG or EXE files  Be   very careful to use binary mode when   reading and writing such files  On   Unix  it doesn   t hurt to append a  b    to the mode  so you can use it   platform-independently for all binary   files    Original   As part of optional paramaters for the csv writer if you are getting extra blank lines you may have to change the lineterminator  info here    Example below adapated from the python page csv docs   Change it from   n  to whatever it should be   As this is just a stab in the dark at the problem this may or may not work  but it s my best guess    gt  gt  gt  import csv  gt  gt  gt  spamWriter   csv writer open  eggs csv    w    lineterminator   n    gt  gt  gt  spamWriter writerow   Spam     5     Baked Beans     gt  gt  gt  spamWriter writerow   Spam    Lovely Spam    Wonderful Spam

User · Answer

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 · Answer

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

[python] CSV file written with Python has blank lines between each row

Examples related to python

Examples related to windows

Examples related to csv