Python: Pandas read_excel cannot open .xls file, ValueError: File is not a recognized excel file

Question

Problem:

I got an error when I tried to open a product.xls with pd.read_excel ("NDC database file - Excel version (zip format)" downloaded from https://www.fda.gov/drugs/drug-approvals-and-databases/national-drug-code-directory)

df_product = pd.read_excel("tmp/Presentaciones.xls")

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/pandas/util/_decorators.py", line 299, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/pandas/io/excel/_base.py", line 336, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
  File "/usr/local/lib/python3.9/site-packages/pandas/io/excel/_base.py", line 1071, in __init__
    ext = inspect_excel_format(
  File "/usr/local/lib/python3.9/site-packages/pandas/io/excel/_base.py", line 965, in inspect_excel_format
    raise ValueError("File is not a recognized excel file")
ValueError: File is not a recognized excel file

MY env:

pandas version: 1.2.4
xlrd version: 2.0.1
openpyxl version: 3.0.7

Thanks in advance.

I downloaded file and tried to read it with different engines and it gave error every time. If there is no solution, you can open the file in MS Excel and save as CSV, then you can read it with read_csv — Tugay
– Tugay, Commented Jun 17, 2021 at 12:03
The problem is that the files are not Excel (neither BIFF nor OOXML) at all but CSV (character separated, here using tabs as separators). Just rename the files and use read_csv with the relevant options. And report the problem to the website owner so that they can at least correct the labels! — Charlie Clark
– Charlie Clark, Commented Jun 17, 2021 at 12:24
@CharlieClark I tested it after renaming to csv directly, it works now with read csv. Thank you — Mapotofu
– Mapotofu, Commented Jun 17, 2021 at 12:33

Alakananda Giridhar · Accepted Answer · 2021-09-01 04:11:12Z

3

I had a similar issue, where I had to read and combine a bunch of .xls files in a folder into one single dataframe. Turns out the error arose because .txt files were forcibly saved as .xls files. This also generated an error in excel upon attempting to open the file, which said

"The file format and extension of 'filename.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"

Doing the following resolved it for me:

import glob 
import os 
import pandas as pd

path = r'C:\tmp' ## use your path

all_files = glob.glob(os.path.join(path, "*.xls"))
df_from_each_file = (pd.read_csv(f, delimiter = "\t") for f in all_files) ## reading the files using csv reader with tab delimiter
df1   = pd.concat(df_from_each_file, ignore_index=True)  ## concatenating all the individual files

If pd.read_csv doesn't work, you can also experimentally attempt to check which file reader on python is able to read your original file format.

P.S: Edited based on the comment from Yona

edited Sep 1, 2021 at 4:11

answered Jul 12, 2021 at 9:46

Alakananda Giridhar

436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yona bendelac Over a year ago

Alakananda Giridhar, your solution it working for me too. But if you simply read the xls file with read_csv it work too. You don't really need to change the extension to txt before it.

Collectives™ on Stack Overflow

Python: Pandas read_excel cannot open .xls file, ValueError: File is not a recognized excel file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related