I can read an Excel file from pandas as usual:
df = pd.read_excel(join("./data", file_name) , sheet_name="Sheet1")
I got the following error:
ValueError: Value must be either numerical or a string containing a wildcard
What I'm doing wrong?
I'm using: Pandas 1.5.3 + python 3.11.0 + xlrd 2.0.1
I got the same issue and then realized that the sheet I was reading is in "filtering" mode. Once I deselect "sort&filter", the read_excel function works.
In my case, I couldn't remove filters from the project. Instead of manually removing filters from files, I was expecting a "valueError" exception, then opening the file with xlrd and writing to a temporary directory in .csv format, then opening with pandas as csv
try:
excel_data_df = pd.read_excel()
except ValueError:
with tempfile.TemporaryDirectory() as tmpdir:
workbook = xlrd.open_workbook(f"../registry_dir/{file_name}")
worksheet = workbook.sheet_by_index(0)
with open(f'{tmpdir}/{file_name}.csv', 'w', newline='') as file:
writer = csv.writer(file)
for row_num in range(worksheet.nrows):
writer.writerow([data for data in worksheet.row_values(row_num)])
excel_data_df = pd.read_csv(f'{tmpdir}/{file_name}.csv')
...file processing code
To use xlrd to open .xlsx files I used version 1.2.0
xrld == 1.2.0
For people like me who are wondering what sort and filter is, it is an option in your Excel viewer. If you are using Microsoft Excel, you can go to the tab "Home" and then to the right side of the tab, you can find Sort & Filter, from there select Clear.
