Creating csv files from Excel file

[deanhystad]

What did you name the module (file)? Remember that case is important, and convention is to use all lower case for modules.

I would package it like this:

# shebang?

import openpyxl
import pandas as pd
from pathlib import Path

def read_excel_sheet(workbook, sheet=0, start=None, end=None):
    """Read sheet from excel workbook.  Return dataframe
 
    workbook : Name of excel file to read.
    sheet    : Name of sheet in workbook to convert.  Defaults to first sheet
    start    : Start reading at this row.  Default is start reading at row 0.
    end      : Stop reading at this row.  Default is read to end.
    """
    skiprows = max(0, start - 1) if start is not None else None
    nrows = end - start if start and end else None
    return pd.read_excel(workbook, sheet_name=sheet, skiprows=skiprows, nrows=nrows)


def excel_sheet_to_csv(workbook, sheet=0, csvfile=None, start=None, end=None, index=False):
    """Convert excel spreadsheet (.xlsx) to csv file
 
    workbook : Name of excel file to read.
    sheet    : Name of sheet in workbook to convert.  Defaults to first sheet
    csvfile  : Name of csv file to write.  Default to workbook with .csv extension
    start    : Start reading at this row.  Default is start reading at row 0.
    end      : Stop reading at this row.  Default is read to end.
    index    : Write index column if True.  Default is False
    """
    if csvfile is None:
        csvfile = Path(workbook).with_suffix(".csv")
    df = read_excel_sheet(workbook, sheet, start, end)
    df.to_csv(csvfile, index=index)


def excel_to_csv(workbook, csvfile=None):
    """Convert excel workbook (.xlsx) to csv file(s)
 
    workbook : Name of excel file to read.
    csvfile  : Name of csv file to write.  Default to workbook with .csv extension
    """
    if csvfile is None:
        csvfile = Path(workbook).with_suffix(".csv")

    sheets = openpyxl.load_workbook(workbook).sheetnames
    if len(sheets) == 1:
        excel_sheet_to_csv(workbook, 0, csvfile)
    else:
        path = Path(csvfile)
        name, ext = path.name.split(".")
        for sheet in sheets:
            excel_sheet_to_csv(
                workbook,
                sheet,
                path.parent/f"{name}_{sheet}.{ext}")


if __name__ == "__main__":
    import sys
    excel_to_csv(*sys.argv[1:])

This gives you the same excel_to_csv function that you already have (now called excel_sheet_to_csv). It gives you a new function (named excel_to_csv) that converts all the sheets in a workbook to csv files and another that reads a sheet from a workbook and returns it as a dataframe.

And for good measure, you can add a shebang, pass in command line arguments, and run it like a program.