3
\$\begingroup\$

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.

xlsx_tp_csv

"""
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'):
            continue

        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
                        row_data.append(cell_data)

                    csv_writer.writerow(row_data)

if __name__ == "__main__":
    xlsx_to_csv()
\$\endgroup\$
4
\$\begingroup\$

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.

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

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.