read_excel fails to read excel file when last sheet is empty and sheetname=None · Issue #11711 · pandas-dev/pandas (original) (raw)

Pandas fails to load an excel file as a dict fo dataframe when the last sheet is empty when sheetname=None.
Deleting the last sheet manually fix the problem.
Maybe Pandas should be improved to be robust to this common case.
Also I post this to raise awareness of this issue in case someone is scratching his head wondering why pandas is not loading his excel file

In [151]:

df_dict=pd.read_excel('D:\trash_test.xlsx',sheetname=None)

print df_dict.keys() # pandas fails to load the file

​

df=pd.read_excel('D:\trash_test.xlsx',sheetname='a_test')

print df # pandas loads first sheet normally

df=pd.read_excel('D:\trash_test.xlsx',sheetname='a_test')

print df # pandas loads second sheet normally

df=pd.read_excel('D:\trash_test.xlsx',sheetname='not_default')

print df # pandas fails to load 'not_default' the last sheet which is empty (to be expected?)

Index([], dtype='object')
Empty DataFrame
Columns: [some_stuff, 1]
Index: []
Empty DataFrame
Columns: [some_stuff, 1]
Index: []

XLRDError Traceback (most recent call last)
in ()
6 df=pd.read_excel('D:\trash_test.xlsx',sheetname='a_test')
7 print df # pandas loads second sheet normally
----> 8 df=pd.read_excel('D:\trash_test.xlsx',sheetname='not_default')
9 print df # pandas fails to load 'not_default' the last sheet which is empty (to be expected?)

C:\Anaconda\lib\site-packages\pandas\io\excel.pyc in read_excel(io, sheetname, header, skiprows, skip_footer, index_col, parse_cols, parse_dates, date_parser, na_values, thousands, convert_float, has_index_names, converters, engine, *_kwds)
168 date_parser=date_parser, na_values=na_values, thousands=thousands,
169 convert_float=convert_float, has_index_names=has_index_names,
--> 170 skip_footer=skip_footer, converters=converters, *_kwds)
171
172 class ExcelFile(object):

C:\Anaconda\lib\site-packages\pandas\io\excel.pyc in _parse_excel(self, sheetname, header, skiprows, skip_footer, index_col, has_index_names, parse_cols, parse_dates, date_parser, na_values, thousands, convert_float, verbose, **kwds)
370
371 if isinstance(asheetname, compat.string_types):
--> 372 sheet = self.book.sheet_by_name(asheetname)
373 else: # assume an integer if not a string
374 sheet = self.book.sheet_by_index(asheetname)

C:\Anaconda\lib\site-packages\xlrd\book.pyc in sheet_by_name(self, sheet_name)
439 sheetx = self._sheet_names.index(sheet_name)
440 except ValueError:
--> 441 raise XLRDError('No sheet named <%r>' % sheet_name)
442 return self.sheet_by_index(sheetx)
443

XLRDError: No sheet named <'not_default'>