I have tons of .xlsm files that I have to load. Each Excel file has 6 sheets. Because of that, I'm opening each Excel file like this, using pandas: After each iteration I am passing the df to other function and do some stuff with it. I am using pd.ExcelFile to load the file into memory just once and then

Ignore UserWarning from openpyxl using pandas

I have tons of .xlsm files that I have to load. Each Excel file has 6 sheets. Because of that, I’m opening each Excel file like this, using pandas:

for excel_file in files_list:
    with pd.ExcelFile(excel_file, engine = "openpyxl") as f:
        df1 = pd.read_excel(f, "Sheet1")
        df2 = pd.read_excel(f, "Sheet2")
        df3 = pd.read_excel(f, "Sheet3")
        ...

After each iteration I am passing the df to other function and do some stuff with it. I am using pd.ExcelFile to load the file into memory just once and then separate it on DataFrames.

However, when doing this, I am getting the following warning:

/opt/anaconda3/lib/python3.8/site-packages/openpyxl/worksheet/_reader.py:300: UserWarning: Data Validation extension is not supported and will be removed
warn(msg)

No matter the warning, the information is loaded correctly from the Excel file and no data is missing. It takes about 0.8s to load each Excel file and all of its sheets into df. If I use the default engine on pandas to load each Excel file, the warning goes away, but the time it takes for each file goes up to 5 or even 6 seconds.

I saw this post, but there wasn’t an answer on how to remove the warning, which is what I need, as everything’s working correctly.

How can I disable said UserWarning?

Answer

You can do this using warnings core module:

import warnings

warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

You can also specify the particular module you’d like to silence warnings for by adding an argument module="openpyxl".

Advertisement

Answer