CODEWITHSUNDEEP
pandasopenpyxlxlrdpython-3.11
Fernando S. Peregrino
Fernando S. Peregrino

Reputation: 515

Unable to read an Excel file using Pandas

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

Upvotes: 10

Views: 6791

Answers (3)

Moein Hasani
Moein Hasani

Reputation: 91

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.

Upvotes: 1

Semen Shutenko
Semen Shutenko

Reputation: 19

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

Upvotes: 1

LCheng
LCheng

Reputation: 400

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.

Upvotes: 17

Related Questions