Solved

Python csv creating extra carriage return

Posted on 2011-03-22
8
1,948 Views
Last Modified: 2012-05-11
I'm creating a comma delimited file using Python's csv module.  I'm writing rows by using the csvwriter.writerow(row)

When I do that and open the resulting file in MS Excel, I'm getting an extra blank line in between each row.  Looking at the hex of the file, there are two carriage returns and a line feed CR CR LF at the end of every row.

How can I stop the extra carriage return when using writerow() ?  

I'm certain my data does not contain a carriage return.  Also, when I run the same application under linux, the extra carriage return is not there and I'm able to open the file in Excel without problem.

I'm using Python 2.6.

Thanks.
0
Comment
Question by:JohnSantaFe
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 41

Expert Comment

by:HonorGod
ID: 35193857
We would have to see the code you use to write your output.
0
 
LVL 31

Expert Comment

by:farzanj
ID: 35193885
Suppose it is contained in variable s
Use the following, to explicitly remove it.
s.replace('\n', '')
0
 

Author Comment

by:JohnSantaFe
ID: 35194535
The attached code is an example.  It creates the following output:

0,1,2,3,4,5,6,0

1,2,3,4,5,6,7,0

2,3,4,5,6,7,8,0

3,4,5,6,7,8,9,0

4,5,6,7,8,9,10,0

5,6,7,8,9,10,11,0

6,7,8,9,10,11,12,0

7,8,9,10,11,12,13,0

8,9,10,11,12,13,14,0

9,10,11,12,13,14,15,0


file_name1 = 'my_data.csv'
leu1_file = open(file_name1, 'w')
leu1_writer = csv.writer(leu1_file)
       entries = [0]*8
        for x in range(10):   #10 rows
            for y in range(7):  #create a row
                entries[y] = y + x
            leu1_writer.writerow(entries) #write the row

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 25

Accepted Solution

by:
clockwatcher earned 350 total points
ID: 35194861
You need to open the file you're writing in binary ('b') mode.

import csv
file_name1 = 'my_data.csv'
leu1_file = open(file_name1, 'wb')
leu1_writer = csv.writer(leu1_file)
entries = [0]*8
for x in range(10):   #10 rows
    for y in range(7):  #create a row
        entries[y] = y + x
    leu1_writer.writerow(entries) #write the row

Open in new window

0
 
LVL 28

Expert Comment

by:pepr
ID: 35197376
... and you should also close the file at the end.

leu1_file.close()
0
 
LVL 28

Assisted Solution

by:pepr
pepr earned 150 total points
ID: 35198611
As clockwatcher wrote.  The explanation is that when you write to a file opened in the text mode on Windows, the LF (the sequence \n) is automatically converted to CR LF (\r\n).  But the csv writer does not know how the file was opened. It assumes binary mode and sends CR LF.  Because of that you get CR (the first one, not converted) plus CR LF (from the converted LF).
0
 

Author Comment

by:JohnSantaFe
ID: 35204299
Changing to wb worked.  I wish the Python documentation had addressed the issue.
Also, the assumption that the file was opened as binary for a CSV file I think is a bad one.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35204391
The python documentation (http://docs.python.org/library/csv.html) does mention it:

csv.writer(csvfile[, dialect='excel'][, fmtparam])¶

    Return a writer object responsible for converting the user’s data into delimited strings on the given file-like object. csvfile can be any object with a write() method. If csvfile is a file object, it must be opened with the ‘b’ flag on platforms where that makes a difference.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A short article about problems I had with the new location API and permissions in Marshmallow
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question