Python: Converting multiple files from xls to csv
<p>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.</p>

<p>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:</p>

<pre><code>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)
</code></pre>

<p>I have two questions:</p>

<p>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. </p>

<p>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.</p>

<p>Thank you</p>

<p>One possible solution would be using <code>glob</code> and <a href="http://pandas.pydata.org/pandas-docs/stable/io.html" rel="nofollow"><code>pandas</code></a>.</p>

<pre><code>excel_files = glob('*xls*')

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

python excel csv