Pandas read_excel returns xlrd dependency error only for a specific file

user_640251 0
Viewed 195

I have a python script which reads a number of Excel files (.xlsx) with no issue, thus:

df_file1 = pd.read_excel(myfile1, mysheet)

However, I then tried to read another similar file using similar code and received a dependency error:

ImportError: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

The environment I'm working in is quite locked down and I'm not sure I can install xlrd. I can't see anything that makes this particular file different to the files I've previously loaded. It was originally a .xlsb that I re-saved as a .xlsx (because, similarly, I can't install pyxlsb). However, other files I have successfully opened were similarly converted.

I'm wondering what might be different about a particular file that would require xlrd to open it when other similar files do not.

(I don't believe this is a duplicate of other similar questions about xlrd dependency as this is about determining what criteria require xlrd for a particular file)

pandas xlsx python-3.x
1 Answers

From the read_excel documentation, xlrd is used when no engine is specified and the file is in .xls format. However, the file was definitely saved in .xlsx format.

Explicitly specifying the openpyxl engine:

df_file1 = pd.read_excel(myfile1, mysheet, engine='openpyxl')

returned the error BadZipFile: File is not a zip file

The file had been correctly saved and was not corrupted.
However, the original .xlsb file had been password protected. When re-saving in .xlsx format, Excel had automatically applied the same password protection.
Thus the actual issue was that the file had been inadvertently password protected.

Presumably read_excel, having failed to open the file with the default engine, could not identify it as .xlsx format, tried alternative engines and then failed when no usable engine could be found.

Re-saving the file with the password protection removed allowed it to be successfully using the original read_excel statement, with the default engine and no need for xlrd.

Hope this might help anyone else who encounters a similar issue with a particular file.

user_6402510
Related