Python: Converting multiple files from xls to csv

Question

I'm trying to write a script in Python 2.7 that would convert all .xls and .xlsx files in the current directory into .csv with preserving their original file names.

With help from other similar questions here (sadly, not sure who to credit for the pieces of code I borrowed), here's what I've got so far:

import xlrd
import csv
import os

def csv_from_excel(xlfile):
     wb = xlrd.open_workbook(xlfile)
     sh = wb.sheet_by_index(0)
     your_csv_file = open(os.path.splitext(sxlfile)[0], 'wb')
     wr = csv.writer(your_csv_file, dialect='excel', quoting=csv.QUOTE_ALL)
     for rownum in xrange(sh.nrows):
         wr.writerow(sh.row_values(rownum))
     your_csv_file.close()

 for file in os.listdir(os.getcwd()):
      if file.lower().endswith(('.xls','.xlsx')):
         csv_from_excel(file)

I have two questions:

1) I can't figure out why the program when run, only converts one file and doesn't iterate through all files in the current directory.

2) I can't figure out how to keep the original filename through the conversion. I.e. that an output file has the same name as an input.

Thank you

gabra · Accepted Answer · 2016-09-25 12:22:19Z

5

One possible solution would be using glob and pandas.

excel_files = glob('*xls*')

 for excel in excel_files:
    out = excel.split('.')[0]+'.csv'
    df = pd.read_excel(excel, 'Sheet1')
    df.to_csv(out)

answered Sep 25, 2016 at 12:22

gabra

10.8k4 gold badges32 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Python: Converting multiple files from xls to csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related