Python: Loop through folders, convert XLSX files to CSV removing non ASCII

Question

As the subject suggests:

• Loop recursively through folders
• All files are XLSX
• Remove all non ASCII characters
• Save as a CSV

Can it be improved with regards to speed? Would using openpyxl help?

import os
import xlrd
import csv

for subdir, dirs, files in os.walk("C:\Users\Alan\Downloads\Knowledge"):
    for file in files:
        filepath = subdir + os.sep + file

        wb = xlrd.open_workbook(filepath)
        sh = wb.sheet_by_index(0)
        csv_file = open(filepath.replace(".xlsx","")+'_csv.csv','wb')
        wr = csv.writer(csv_file,quoting=csv.QUOTE_ALL)

        for rownum in xrange(sh.nrows):
            wr.writerow([unicode(val).encode('ascii','ignore') for val in sh.row_values(rownum)])

        csv_file.close()

Answer 1

I'd have a couple of suggestions which you may want to follow:

1. Use openpyxl module to read xlsx documents, and the csv module to write.

An example might look like this:

import openpyxl
import csv

wb = openpyxl.load_workbook('test.xlsx')
sh = wb.get_active_sheet()
with open('test.csv', 'wb') as f:
    c = csv.writer(f)
    for r in sh.rows:
        # here, you can also filter non-ascii characters
        c.writerow([cell.value for cell in r])

2. Don't use os.sep.

From docs:

[...] Note that knowing this is not sufficient to be able to parse or concatenate path names...

Instead, you could use normpath from os.path to normalize the path.

From the docs:

Normalize a pathname by collapsing redundant separators and up-level references so that A//B, A/B/, A/./B and A/foo/../B all become A/B. This string manipulation may change the meaning of a path that contains symbolic links. On Windows, it converts forward slashes to backward slashes.

3. Don't use + separator to join certain paths. Instead, just use os.path.join().

As for the speed improvement, you'll need to go through all the subfolders in your parent folder and find all the .xslx files. Perhaps skipping non-xlsx files will bring a bit of speed: str.endswith('.xslx').