To check out the cssv module i wrote a small programm which takes all xlsx files in a folder and converts them into csv files.

Let me know what you would improve on this program.


All xlsx files in the folder of the script get converted into csv files
(One for each sheet).
The names are created as excel filename + sheet name.

import csv
import os
from pathlib import Path
import openpyxl

def make_csv_filename(filename_excel: str, sheet_name: str) -> str:
    Make a filename out of the filename of the excel file and
    the corresponding sheet
    return Path(filename_excel).resolve().stem  + '_' + sheet_name + '.csv'

def xlsx_to_csv():
    """Main loop"""
    target_folder: str = 'csv'
    os.makedirs(target_folder, exist_ok=True)

    for filename in os.listdir('.'):
        if not filename.endswith('.xlsx'):

        workbook = openpyxl.load_workbook(filename)

        for sheet_name in workbook.sheetnames:
            sheet = workbook[sheet_name]
            csv_filename = make_csv_filename(filename, sheet_name)
            csv_path: Path = Path(target_folder, csv_filename)

            with open(csv_path, 'w', newline='') as csv_file:
                csv_writer = csv.writer(csv_file)

                for row_number in range(1, sheet.max_row + 1):
                    row_data = []
                    for column_number in range(1, sheet.max_column + 1):
                        cell_data = sheet.cell(
                            column=column_number, row=row_number).value


if __name__ == "__main__":

1 Answer 1


Good job with the type annotations! Here are a few things you could simplify/improve:

  • instead of listdir and an extra file extension check, you could use glob.iglob():

    for filename in glob.iglob('./**/*.xlsx'):
  • to improve excel file read performance and memory consumption, you could use read_only=True mode

  • instead of iterating over sheetnames and getting a sheet by name, you could iterate over available worksheets directly:

    for sheet in workbook.worksheets:
  • iter_rows() should improve the way you read the sheet cells

As a side note and as an idea for an alternative solution: what if you transition the data through a pandas.DataFrame, pandas has .read_excel() and .to_csv() methods.

  • 1
    \$\begingroup\$ Instead of glob.iglob, there is also pathlib.Path.glob, since OP is already using Path. \$\endgroup\$
    – Graipher
    Jan 22, 2019 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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