pandas and Excel error – xlrd.biffh.XLRDError: Excel xlsx file; not supported is an error which occurs when you try to read csv or other files and even excel files using the xlrd library.
In the blog post I attempt to explain why this error takes place and how you can solve it, I will also add other solutions that could solve the error if possible.
Exploring pandas and Excel error – xlrd.biffh.XLRDError: Excel xlsx file; not supported
This is an error which occurs when you try to read csv or other files and even excel files using the xlrd library.
Do not mix between errors. Make sure the error message looks like the error message bellow after double checking.
#
return open_workbook(filepath_or_buffer)
File "/home/vcap/deps/0/python/lib/python3.8/site-packages/xlrd/__init__.py", line 170, in open_workbook
raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+'; not supported')
xlrd.biffh.XLRDError: Excel xlsx file; not supported
#
Bellow I will present multiple solutions some have worked for me and others have worked for other developers.
Solution : use openpyxl (Download and Install openpyxl)
The xlrd library cannot read csv files or any other files that are not xls files. The solution is to download, install and then use a package called openpyxl.
openpyxl is an open source effort to create a package that enables you to read and write natively from Python the Office Open XML format since there is no other way to do it.
Your pandas code should include openpyxl as the engine to read a file called mydata.xlsm for example.
#
df = pd.read_excel( os.path.join(APP_PATH, "Data", "mydata.xlsm"), engine='openpyxl',)
#
You can get openpyxl from the link provided by the team: Link
To read more about openpyxl, this is the official link to the openpyxl documentation.
https://openpyxl.readthedocs.io/
If the solutions above helped you, consider supporting us on Kofi, any help is appreciated.
Summing-up
The error above can be very confusing. The fix is simple and works for most cases, thank you for reading and cheers.
If you want to learn more about Python, please check out the Python Documentation : https://docs.python.org/3/