英文:
Unable to read an Excel file using Pandas
问题
我可以像往常一样从pandas读取Excel文件：
df = pd.read_excel(join("./data", file_name), sheet_name="Sheet1")
我收到了以下错误：
ValueError: 值必须是数字或包含通配符的字符串
我做错了什么？
我正在使用：Pandas 1.5.3 + Python 3.11.0 + xlrd 2.0.1
英文:
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
答案1
得分: 16
我遇到了相同的问题，然后意识到我正在阅读的表格处于“筛选”模式。一旦我取消选择“排序和筛选”，read_excel函数就可以正常工作。
英文:
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.
答案2
得分: 0
在我的情况下，无法从项目中移除筛选器。我期望能够捕获"valueError"异常，然后使用xlrd打开文件并以.csv格式写入临时目录，之后使用pandas作为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')
...文件处理代码
要使用xlrd打开.xlsx文件，我使用了版本1.2.0。
xlrd == 1.2.0
英文:
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
答案3
得分: 0
"Sort & Filter" 是 Excel 查看器中的一个选项。如果你正在使用 Microsoft Excel，你可以转到选项卡“主页”，然后在选项卡的右侧找到“Sort & Filter”，从那里选择“Clear”。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论