python pandas read_excel engine=openpyxl not closing file

user_10016858 0
Viewed 5378

I am loading a dataframe into pandas using following:

import pandas as pd

df_factor_histories=pd.read_excel("./eco_factor/eco_factor_test_data_builder.xlsx",
                                  engine='openpyxl', sheet_name=0)

engine=openpyxl is required to enable read_excel to support newer Excel file formats (specifically in my case .xlsx rather than jusy .xls).

The dataframe loads just fine but the file is left open:

import psutil

p = psutil.Process()
print(p.open_files())

OUTPUT
[popenfile(path='C:\\Users\\xx\\.ipython\\profile_default\\history.sqlite', fd=-1), 
popenfile(path='C:\\Windows\\System32\\en-US\\KernelBase.dll.mui', fd=-1), 
popenfile(path='C:\\Windows\\System32\\en-US\\kernel32.dll.mui', fd=-1), 
popenfile(path='D:\\xxxxx\\data modelling\\eco_factor\\eco_factor_test_data_builder.xlsx', fd=-1)]

This Github Post suggests the bug is fixed - but not for me (running Anaconda/Jupyter). Relevant versions I am running:

numpy                         1.19.2
openpyxl                      3.0.5
pandas                        1.1.3
Python 3.7.4

I would appreciate some suggestions on how to close the files/best work around this, thanks

file-handling openpyxl pandas python
2 Answers

I suggest to remove engine='openpyxl' from your code. It isn't actually needed. I use the pd.read_excel without it and it works just fine even for .xlsx format.

Removing this will cause the default behavior for the engine parameter to take over. The engine will know which engine to use: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html#pandas.read_excel

engine : str, default None
If io is not a buffer or path, this must be set to identify io. Supported engines: “xlrd”, “openpyxl”, “odf”, “pyxlsb”. Engine compatibility :

“xlrd” supports old-style Excel files (.xls).

“openpyxl” supports newer Excel file formats.

“odf” supports OpenDocument file formats (.odf, .ods, .odt).

“pyxlsb” supports Binary Excel files.

Changed in version 1.2.0: The engine xlrd now only supports old-style .xls files. When engine=None, the following logic will be used to determine the engine:

If path_or_buffer is an OpenDocument format (.odf, .ods, .odt), then odf will be used.

Otherwise if path_or_buffer is an xls format, xlrd will be used.

Otherwise if openpyxl is installed, then openpyxl will be used.

Otherwise if xlrd >= 2.0 is installed, a ValueError will be raised.

Otherwise xlrd will be used and a FutureWarning will be raised. This case will raise a ValueError in a future version of pandas.
user_100079580

I was facing the same issue,

When i read the excel file using pandas with engine=openpyxl. It is not being closed. when i try to archive/ move the excel file using python, it was giving error,

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process

Also, once it is read by pandas, we are not able to edit or any other operation on excel file using Excel tool.

Following solution worked for me. i am using:

python version 3.6.8.

pandas==0.25.1

openpyxl==3.0.7

import io
import pandas as pd

with open('path/to/input_excel_file.xlsx', "rb") as f:
    file_io_obj = io.BytesIO(f.read())


df_input_file = pd.read_excel(file_io_obj, engine='openpyxl', sheet_name=None)
user_74393910
Related