Converting xls to csv in python

Question

I've successfully converted xls to csv, but I've a problem. It seems normal in the csv file itself but when I run the output, everything is wrong.

changeWb = xlrd.open_workbook('test.xls')
sh = changeWb.sheet_by_name('detail')
csv_file = open('test2.csv', 'wb')
wr = csv.writer(csv_file, quoting=csv.QUOTE_NONE)
for rownum in xrange(sh.nrows):
    wr.writerow([unicode(entry).encode("utf-8") for entry in sh.row_values(rownum)])
csv_file.close()

I don't have any csv files at the start, opening a csv_file is actually opening a new file freshly. The old output was:

['001', '1387533864.0', '1387575618.0']
['003', '1387534098.0', '1387573600.0']

I need something like:

['1', '1387533864', '1387575618']    
['3', '1387534098', '1387573600']

The bottom output is after I goes into the file, and save as csv(comma delimted). Please advice on how I can get the bottom result using python.

falsetru · Accepted Answer · 2013-12-26 03:29:12Z

2

Conver the values to int first:

wr.writerow(sh.row_values(0))
for rownum in xrange(1, sh.nrows):
    wr.writerow([str(int(entry)) for entry in sh.row_values(rownum)])
    #                ^^^

edited Dec 26, 2013 at 3:29

answered Dec 26, 2013 at 3:18

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

8 Comments

Vick Peh Over a year ago

Hi, thanks for the reply. My row 1 is actually string, followed by int from row 2 onwards, so I can't convert all values into int

falsetru Over a year ago

@VickPeh, Write header outside the loop. I updated the answer code.

Vick Peh Over a year ago

this is the error I received: ValueError: invalid literal for int() with base 10: '1387533864.0'

falsetru Over a year ago

@VickPeh, How about str(int(float(entry))) instead of str(int(entry))?

falsetru Over a year ago

@VickPeh, If entry is all string, you can use entry.partition('.')[0]

|

Collectives™ on Stack Overflow

Converting xls to csv in python

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related