Pandas Read_Excel Throws Valueerror: Value Does Not Match Pattern

I want to read an excel file as pandas Dataframe

import pandas as pd
import os

path = r"my/path/to/file"
file = "myFile.xlsx"

df = pd.read_excel(os.path.join(path, file))

and receive following error:

ValueError: Value does not match pattern ^[$]?([A-Za-z]{1,3})[$]?(\d+)(:[$]?([A-Za-z]{1,3})[$]?(\d+)?)?$|^[A-Za-z]{1,3}:[A-Za-z]{1,3}$

The excel file has per default an active filter. If I perform a workaround using the xlwings library I can read the excel file as a Dataframe afterwards:

#Start Workaround
wb = xw.Book(os.path.join(path, file))

if wb.sheets["Sheet1"].api.AutoFilterMode == True:
      wb.sheets["Sheet1"].api.AutoFilterMode = False

wb.save()
wb.close()
#End Workaround

df = pd.read_excel(os.path.join(path, file)) #works now

Since a security update I need to confirm manually a Dialog Box after opening the file with xlwings. Does anyone know how one can read the excel directly into a Dataframe without using the workaround?

FYI I can't replicate the error with another excel file where I save it with an active filter...

3
Elena Rostova

Elena Rostova

Lead Health, Wellness & Medical Journalist

Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.

Share this article