0

I would like to convert all the csv files in a directory to xls files. Nick T posted this code here How to batch convert .csv to .xls/xlsx

import os
import glob
import csv
import xlwt # from http://www.python-excel.org/

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    wb = xlwt.Workbook()
    ws = wb.add_sheet('data')
    with open(csvfile, 'rU') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, val in enumerate(row):
                ws.write(r, c, val)
    wb.save(csvfile + '.xls')

However when I run this I get an error coming from the last line.

UnicodeDecodeError: 'ascii' codec can't decode byte 0xef in position 0: ordinal not in range(128)

Anyone know how I can get rid of this? I am working in Terminal on a Mac.

3
  • Any non-ASCII characters in filenames? And which version of Python? V2 or V3?
    – xenoid
    Jul 10, 2017 at 18:04
  • No filenames are all just letters and numbers. Python V2 Jul 10, 2017 at 20:32
  • Then it's your data that contains non-ASCII characters.. By default xwlt only assumes ASCII, see here for a solution (if you are lucky the second one could be enough).
    – xenoid
    Jul 10, 2017 at 21:20

1 Answer 1

0

I updated this code so it will work on python 3.5 by getting two lines out of the loop (wb = xlwt.Workbook() and wb.save('output.xls') were inside the loop).

 import os
 import glob
 import csv
 import xlwt
 wb = xlwt.Workbook() # put the xlwt out of the loop 
 for csvfile in glob.glob(os.path.join('.', '*.csv')):
         fpath = csvfile.split("/", 1)
         fname = fpath[1].split(".", 1)
         ws = wb.add_sheet(fname[0])
         with open(csvfile, 'r') as f:
            reader = csv.reader(f)
            for r, row in enumerate(reader):
                for c, col in enumerate(row):
                    ws.write(r, c, col)

 #Save the results once the loop is done
 wb.save('output.xls')
2
  • Welcome to Super User, and thanks for helping with this question. Any chance you could add a couple of sentences to explain what you changed and why (for context)?
    – fixer1234
    Mar 19, 2018 at 21:50
  • I do not know why "wb = xlwt.Workbook()" and wb.save('output.xls') were inside the loop. Mar 20, 2018 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.