Format modified during Python csv to xls conversion

Question

My goal is to convert csv files localted in a directory to xls files.

My csv files are notably composed of a row "mark" (e.g 1.0000, 2.0000 ...) and a row "date" (e.g 26/04/2013). The format of these two rows are very important for me.

I use the following code :

import sys, csv, xlwt, glob, os
import shutil

def cont_directory ():
    return glob.glob('/home/julien/excel/csv/*.csv')
liste = cont_directory()

try: 
    for i in liste:
        f=open(i, 'rb')
        g = csv.reader ((f), delimiter = ";")
        workbook=xlwt.Workbook()
        sheet= xlwt.Workbook()


        sheet = workbook.add_sheet("To be modified")

        for rowi, row in enumerate(g):
            for coli, value in enumerate(row):
                sheet.write(rowi,coli,value)
        workbook.save(i + ".xls")

except: 
    print "epic_fail_Conversion", sys.exc_info()


for i in glob.glob ('/home/julien/excel/csv/*.xls'):
    shutil.copy2 (i, '/home/julien/excel/xls')

try:
    for j in glob.glob('/home/julien/excel/xls/*.xls'):
        os.rename (j, j.replace ('.csv', ''))

except: 
       print "epic_fail_Conversion", sys.exc_info()


print "End"

That code works pretty well and I have my new excel files.

The problem is that my rows have been modified during that conversion. For example the content of the row "mark" is 1 instead of 1.00000. Moreover, the content of the row "date" is 2013/04/26 instead of 26/04/2013.

Do you know what could I do, in order to keep the initial format rows of my csv files ?

Thank you very much.

This blog entry may help: youlikeprogramming.com/2011/04/… — ditkin
– ditkin, Commented Apr 25, 2013 at 22:37

MichaelJCox · Accepted Answer · 2013-04-26 00:04:38Z

1

You could define styles for the dates and numbers and then use a conditional to apply the style. Something like:

datestyle = xlwt.XFStyle()
datestyle.num_format_str = 'D/M/YYYY' 

numstyle = xlwt.XFStyle()
numstyle.num_format_str = '#,##0.0000'

....

for rowi, row in enumerate(g):
            for coli, value in enumerate(row):
                if coli == 0:  #or wherever your date is, if it's in a fixed spot
                    sheet.write(rowi,coli,value, datestyle)
                elif coli == 1: #or wherever your number is
                    sheet.write(rowi,coli,value, numstyle)
                else:
                    sheet.write(rowi,coli,value)

Sorry if it's not quite right, I'm running out the door as I write it up. But hopefully it gets you in the right direction.

answered Apr 26, 2013 at 0:04

MichaelJCox

7547 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julien Over a year ago

Thank you to both of you ! It finally worked by using the "encoding = 'ascii'" clause.

Collectives™ on Stack Overflow

Format modified during Python csv to xls conversion

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related