问题背景:
工作中大部使用Pandas分处理的数据都是以csv后缀结尾的文件,但是突然换成xlsx后缀的表格之后,出现的一些错误。
问题现象:
XLRDError: Excel xlsx file; not supported
/usr/local/lib/python3.6/site-packages/xlrd/__init__.py in open_workbook(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows, ignore_workbook_corruption)168# files that xlrd can parse don't start with the expected signature.169if file_format and file_format != 'xls':--> 170 raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+'; not supported')171 172bk = open_workbook_xls(XLRDError: Excel xlsx file; not supported
import pandas as pdPATH = '/tmp/MSD0921.xlsx'dataframe = pd.read_excel(PATH, engine=None)
查看源码:
在pandas的base.py文件的ExcelFile类下边有这么一段描述,大概在1117-1127行。
engine : str, default NoneIf io is not a buffer or path, this must be set to identify io.Supported engines: ``xlrd``, ``openpyxl``, ``odf``, ``pyxlsb``Engine compatibility :- ``xlrd`` supports old-style Excel files (.xls).- ``openpyxl`` supports newer Excel file formats.- ``odf`` supports OpenDocument file formats (.odf, .ods, .odt).- ``pyxlsb`` supports Binary Excel files... versionchanged:: 1.2.0The engine `xlrd <https://xlrd.readthedocs.io/en/latest/>`_now only supports old-style ``.xls`` files.When ``engine=None``, the following logic will beused to determine the engine:
源码解读:
大概的意思是,pandas 读取excel表格只支持这四种引擎,xlrd,openpyxl,odf,pyxlsb如果不是这四种就会抛出异常。如果engine=None的话,默认的就是xlrd引擎,只支持老式的Excel文件,.xls后缀的
代码修改:
只需要将引擎换成openpyxl即可
import pandas as pdPATH = '/tmp/MSD0921.xlsx'dataframe = pd.read_excel(PATH,engine='openpyxl')
总结:关于第三方库代码的问题,要学去看源码,分析问题所在,大部分问题都能够解决