[python] xlrd.biffh.XLRDError: Excel xlsx file; not supported

I am trying to read a macro-enabled Excel worksheet using pandas.read_excel with the xlrd library. It's running fine in local, but when I try to push the same into PCF, I am getting this error:

2020-12-11T21:09:53.441+05:30 [APP/PROC/WEB/0] [ERR] df1=pd.read_excel(os.path.join(APP_PATH, os.path.join("Data", "aug_latest.xlsm")),sheet_name=None)

2020-12-11T21:09:53.441+05:30 [APP/PROC/WEB/0] [ERR] return open_workbook(filepath_or_buffer)
2020-12-11T21:09:53.441+05:30 [APP/PROC/WEB/0] [ERR] File "/home/vcap/deps/0/python/lib/python3.8/site-packages/xlrd/__init__.py", line 170, in open_workbook
2020-12-11T21:09:53.441+05:30 [APP/PROC/WEB/0] [ERR] raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+'; not supported')
2020-12-11T21:09:53.441+05:30 [APP/PROC/WEB/0] [ERR] xlrd.biffh.XLRDError: Excel xlsx file; not supported

How can I resolve this error?

This question is related to python pandas xlrd pcf

The answer is

As noted in the release email, linked to from the release tweet and noted in large orange warning that appears on the front page of the documentation, and less orange, but still present, in the readme on the repository and the release on pypi:

xlrd has explicitly removed support for anything other than xls files.

In your case, the solution is to:

  • make sure you are on a recent version of Pandas, at least 1.0.1, and preferably the latest release. 1.2 will make his even clearer.
  • install openpyxl: https://openpyxl.readthedocs.io/en/stable/
  • change your Pandas code to be:
    df1 = pd.read_excel(
         os.path.join(APP_PATH, "Data", "aug_latest.xlsm"),

The previous version, xlrd 1.2.0, may appear to work, but it could also expose you to potential security vulnerabilities. With that warning out of the way, if you still want to give it a go, type the following command:

pip install xlrd==1.2.0

Questions with python tag:

programming a servo thru a barometer Is there a way to view two blocks of code from the same file simultaneously in Sublime Text? python variable NameError Why my regexp for hyphenated words doesn't work? Comparing a variable with a string python not working when redirecting from bash script is it possible to add colors to python output? Get Public URL for File - Google Cloud Storage - App Engine (Python) Real time face detection OpenCV, Python xlrd.biffh.XLRDError: Excel xlsx file; not supported Could not load dynamic library 'cudart64_101.dll' on tensorflow CPU-only installation Upgrade to python 3.8 using conda Unable to allocate array with shape and data type How to fix error "ERROR: Command errored out with exit status 1: python." when trying to install django-heroku using pip How to prevent Google Colab from disconnecting? "UserWarning: Matplotlib is currently using agg, which is a non-GUI backend, so cannot show the figure." when plotting figure with pyplot on Pycharm How to fix 'Object arrays cannot be loaded when allow_pickle=False' for imdb.load_data() function? "E: Unable to locate package python-pip" on Ubuntu 18.04 Tensorflow 2.0 - AttributeError: module 'tensorflow' has no attribute 'Session' Jupyter Notebook not saving: '_xsrf' argument missing from post How to Install pip for python 3.7 on Ubuntu 18? Python: 'ModuleNotFoundError' when trying to import module from imported package OpenCV TypeError: Expected cv::UMat for argument 'src' - What is this? Requests (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.") Error in PyCharm requesting website How to setup virtual environment for Python in VS Code? Pylint "unresolved import" error in Visual Studio Code Pandas Merging 101 Numpy, multiply array with scalar What is the meaning of "Failed building wheel for X" in pip install? Selenium: WebDriverException:Chrome failed to start: crashed as google-chrome is no longer running so ChromeDriver is assuming that Chrome has crashed Could not install packages due to an EnvironmentError: [Errno 13] OpenCV !_src.empty() in function 'cvtColor' error ConvergenceWarning: Liblinear failed to converge, increase the number of iterations How to downgrade python from 3.7 to 3.6 I can't install pyaudio on Windows? How to solve "error: Microsoft Visual C++ 14.0 is required."? Iterating over arrays in Python 3 How do I install opencv using pip? How do I install Python packages in Google's Colab? How do I use TensorFlow GPU? How to upgrade Python version to 3.7? How to resolve TypeError: can only concatenate str (not "int") to str How can I install a previous version of Python 3 in macOS using homebrew? Flask at first run: Do not use the development server in a production environment TypeError: only integer scalar arrays can be converted to a scalar index with 1D numpy indices array What is the difference between Jupyter Notebook and JupyterLab? Pytesseract : "TesseractNotFound Error: tesseract is not installed or it's not in your path", how do I fix this? Could not install packages due to a "Environment error :[error 13]: permission denied : 'usr/local/bin/f2py'" How do I resolve a TesseractNotFoundError? Trying to merge 2 dataframes but get ValueError Authentication plugin 'caching_sha2_password' is not supported Python Pandas User Warning: Sorting because non-concatenation axis is not aligned

Questions with pandas tag:

xlrd.biffh.XLRDError: Excel xlsx file; not supported Pandas Merging 101 How to increase image size of pandas.DataFrame.plot in jupyter notebook? Trying to merge 2 dataframes but get ValueError Python Pandas User Warning: Sorting because non-concatenation axis is not aligned How to show all of columns name on pandas dataframe? Pandas/Python: Set value of one column based on value in another column Python Pandas - Find difference between two data frames Pandas get the most frequent values of a column Python convert object to float Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Pandas: ValueError: cannot convert float NaN to integer How to create a stacked bar chart for my DataFrame using seaborn? LabelEncoder: TypeError: '>' not supported between instances of 'float' and 'str' Display/Print one column from a DataFrame of Series in Pandas How to calculate 1st and 3rd quartiles? Counting unique values in a column in pandas dataframe like in Qlik? Binning column with python pandas convert array into DataFrame in Python Selection with .loc in python Set value to an entire column of a pandas dataframe Pandas create empty DataFrame with only column names Python: pandas merge multiple dataframes 'DataFrame' object has no attribute 'sort' Remove Unnamed columns in pandas dataframe Convert float64 column to int64 in Pandas Understanding inplace=True How to select rows with NaN in particular column? How to print a specific row of a pandas DataFrame? Pandas rename column by position? re.sub erroring with "Expected string or bytes-like object" Python Pandas iterate over rows and access column names Display rows with one or more NaN values in pandas dataframe Python "TypeError: unhashable type: 'slice'" for encoding categorical data Seaborn Barplot - Displaying Values ValueError: Wrong number of items passed - Meaning and suggestions? How to get row number in dataframe in Pandas? How to install pandas from pip on windows cmd? Pandas convert string to int Convert list into a pandas data frame Use .corr to get the correlation between two columns Why isn't this code to plot a histogram on a continuous value Pandas column working? How to add title to seaborn boxplot ValueError: Length of values does not match length of index | Pandas DataFrame.unique() How to save a new sheet in an existing excel file, using Pandas? matplotlib: plot multiple columns of pandas data frame on the bar chart Convert List to Pandas Dataframe Column TypeError: 'DataFrame' object is not callable Set order of columns in pandas dataframe Python Pandas - Missing required dependencies ['numpy'] 1

Questions with xlrd tag:

xlrd.biffh.XLRDError: Excel xlsx file; not supported Edit existing excel workbooks and sheets with xlrd and xlwt Read Excel File in Python Pandas: Looking up the list of sheets in an excel file python xlrd unsupported format, or corrupt file. writing to existing workbook using xlwt Create a .csv file with values from a Python list

Questions with pcf tag:

xlrd.biffh.XLRDError: Excel xlsx file; not supported