Mass converting xlsb to csv consuming too much memory using win32com

Question

I skeletonized some code to convert certain sheets of xlsb files to csv files and am using this function to iterate over a path list and converting >200 files to csv.


import os
import win32com.client
import subprocess
from pathlib import Path, PurePath


def xl_file_to_csv(xl_file_path, save_path_raw, worksheet_name):
    """
    Open a workbook and save sheet as csv files
    :param xl_file_path: workbook path
    :return: csv file name
    :save_path_raw: raw string save path
    :worksheet_name: raw string worksheet name
    :output_csv_path: Outputs csv path
    """

    #
    FNULL = open(os.devnull, 'w')

    # Sets save_path as Path class and returns file name w/o suffix from path and initializes file names

    save_path = save_path_raw
    file_name = Path(xl_file_path).stem
    file_name = file_name + ' - ' + worksheet_name + '.csv'
    output_csv_path = save_path / file_name

    # Checks if current file exists and calls kill excel process just in case
    exist_check = os.path.isfile(save_path / file_name)

    if exist_check: 
        pass
        subprocess.call('taskkill.exe /f /im EXCEL.EXE', stdout=FNULL, 
                         stderr=subprocess.STDOUT)
    else:
        # kills excel.exe process just in case
        subprocess.call('taskkill.exe /f /im EXCEL.EXE', stdout=FNULL, 
                        stderr=subprocess.STDOUT)

        # Opens and initializes excel
        xl_app = win32com.client.Dispatch("Excel.Application")
        xl_app.Visible = 0
        xl_app.DisplayAlerts = 0

        # Opens workbook and work sheet
        work_book = xl_app.Workbooks.Open(xl_file_path)
        work_sheet = work_book.Worksheets(worksheet_name)

        # Saves as csv in excel and quits
        work_sheet.SaveAs(output_csv_path, 6)
        work_book.Close(SaveChanges=0)
        xl_app.Quit()

        # kills excel.exe process
        subprocess.call('taskkill.exe /f /im EXCEL.EXE', stdout=FNULL, 
                        stderr=subprocess.STDOUT)
    return output_csv_path

However, this function seems to leak memory fairly severely (without the subprocess function), so I am using subprocess to kill EXCEL.EXE every iteration as xl_app.Quit() doesn't seem to end the excel process, which seems like an extreme fix. I was wondering if there was a better way around this issue?

EDIT: The function is being called in a for loop like:

for index, row in df.interrows():
     output_csv_path = xl_file_to_csv(row['path'], save_path, 'Worksheet')

You should be wrapping your COM processing in `try/except`. See example [here](https://stackoverflow.com/a/38948460/1422451). And try releasing object from memory: `xl_app = None; del xl_app`. Finally, launch the Excel app **once** and open/close needed workbooks. Please show how you are calling this method not just method assignment. In a loop? — Parfait, Jun 11 '19 at 19:40

Mass converting xlsb to csv consuming too much memory using win32com

0 Answers0