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...
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...