'pandas read excel: IndexError: list index out of range
I am trying to use pandas (pd.read_excel) to import excel file to python. Yet it gives me the error "IndexError: list index out of range".
I have checked the excel file has no security lock and it's XLSX and not Strict XML Open Spreadsheet.
I can import it if I just copy all the tabs to another excel, but I just cannot import the original excel file.
Is there any reason for it? Cause I have like hundreds of excels to import and I just can't copy all the tabs for all excel files one by one.
Really thanks if anyone could help me out of it!
the code is just simple as below
import pandas as pd
df = pd.read_excel('sample.xlsx', sheet_name = 0)
And the following error message appear
---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-5-f5977f03f042> in <module>
1 import pandas as pd
2
----> 3 df = pd.read_excel('sample.xlsx', sheet_name = 0)
~\Anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
297 )
298 warnings.warn(msg, FutureWarning, stacklevel=stacklevel)
--> 299 return func(*args, **kwargs)
300
301 return wrapper
~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
334 if not isinstance(io, ExcelFile):
335 should_close = True
--> 336 io = ExcelFile(io, storage_options=storage_options, engine=engine)
337 elif engine and engine != io.engine:
338 raise ValueError(
~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in __init__(self, path_or_buffer, engine, storage_options)
1129 self.storage_options = storage_options
1130
-> 1131 self._reader = self._engines[engine](self._io, storage_options=storage_options)
1132
1133 def __fspath__(self):
~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in __init__(self, filepath_or_buffer, storage_options)
473 """
474 import_optional_dependency("openpyxl")
--> 475 super().__init__(filepath_or_buffer, storage_options=storage_options)
476
477 @property
~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in __init__(self, filepath_or_buffer, storage_options)
389 # N.B. xlrd.Book has a read attribute too
390 self.handles.handle.seek(0)
--> 391 self.book = self.load_workbook(self.handles.handle)
392 elif isinstance(self.handles.handle, bytes):
393 self.book = self.load_workbook(BytesIO(self.handles.handle))
~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in load_workbook(self, filepath_or_buffer)
484 from openpyxl import load_workbook
485
--> 486 return load_workbook(
487 filepath_or_buffer, read_only=True, data_only=True, keep_links=False
488 )
~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
315 reader = ExcelReader(filename, read_only, keep_vba,
316 data_only, keep_links)
--> 317 reader.read()
318 return reader.wb
~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in read(self)
279 self.read_properties()
280 self.read_theme()
--> 281 apply_stylesheet(self.archive, self.wb)
282 self.read_worksheets()
283 self.parser.assign_names()
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in apply_stylesheet(archive, wb)
196
197 node = fromstring(src)
--> 198 stylesheet = Stylesheet.from_tree(node)
199
200 wb._borders = IndexedList(stylesheet.borders)
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in from_tree(cls, node)
101 for k in attrs:
102 del node.attrib[k]
--> 103 return super(Stylesheet, cls).from_tree(node)
104
105
~\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
101 attrib[tag] = obj
102
--> 103 return cls(**attrib)
104
105
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in __init__(self, numFmts, fonts, fills, borders, cellStyleXfs, cellXfs, cellStyles, dxfs, tableStyles, colors, extLst)
92 self.protections = self.cellXfs.prots
93 self._normalise_numbers()
---> 94 self.named_styles = self._merge_named_styles()
95
96
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in _merge_named_styles(self)
112
113 for style in named_styles:
--> 114 self._expand_named_style(style)
115
116 return named_styles
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in _expand_named_style(self, named_style)
122 record
123 """
--> 124 xf = self.cellStyleXfs[named_style.xfId]
125 named_style.font = self.fonts[xf.fontId]
126 named_style.fill = self.fills[xf.fillId]
~\Anaconda3\lib\site-packages\openpyxl\styles\cell_style.py in __getitem__(self, idx)
183
184 def __getitem__(self, idx):
--> 185 return self.xf[idx]
186
187
IndexError: list index out of range
---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-5-f5977f03f042> in <module>
1 import pandas as pd
2
----> 3 df = pd.read_excel('sample.xlsx', sheet_name = 0)
~\Anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
297 )
298 warnings.warn(msg, FutureWarning, stacklevel=stacklevel)
--> 299 return func(*args, **kwargs)
300
301 return wrapper
~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
334 if not isinstance(io, ExcelFile):
335 should_close = True
--> 336 io = ExcelFile(io, storage_options=storage_options, engine=engine)
337 elif engine and engine != io.engine:
338 raise ValueError(
~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in __init__(self, path_or_buffer, engine, storage_options)
1129 self.storage_options = storage_options
1130
-> 1131 self._reader = self._engines[engine](self._io, storage_options=storage_options)
1132
1133 def __fspath__(self):
~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in __init__(self, filepath_or_buffer, storage_options)
473 """
474 import_optional_dependency("openpyxl")
--> 475 super().__init__(filepath_or_buffer, storage_options=storage_options)
476
477 @property
~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in __init__(self, filepath_or_buffer, storage_options)
389 # N.B. xlrd.Book has a read attribute too
390 self.handles.handle.seek(0)
--> 391 self.book = self.load_workbook(self.handles.handle)
392 elif isinstance(self.handles.handle, bytes):
393 self.book = self.load_workbook(BytesIO(self.handles.handle))
~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in load_workbook(self, filepath_or_buffer)
484 from openpyxl import load_workbook
485
--> 486 return load_workbook(
487 filepath_or_buffer, read_only=True, data_only=True, keep_links=False
488 )
~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
315 reader = ExcelReader(filename, read_only, keep_vba,
316 data_only, keep_links)
--> 317 reader.read()
318 return reader.wb
~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in read(self)
279 self.read_properties()
280 self.read_theme()
--> 281 apply_stylesheet(self.archive, self.wb)
282 self.read_worksheets()
283 self.parser.assign_names()
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in apply_stylesheet(archive, wb)
196
197 node = fromstring(src)
--> 198 stylesheet = Stylesheet.from_tree(node)
199
200 wb._borders = IndexedList(stylesheet.borders)
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in from_tree(cls, node)
101 for k in attrs:
102 del node.attrib[k]
--> 103 return super(Stylesheet, cls).from_tree(node)
104
105
~\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
101 attrib[tag] = obj
102
--> 103 return cls(**attrib)
104
105
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in __init__(self, numFmts, fonts, fills, borders, cellStyleXfs, cellXfs, cellStyles, dxfs, tableStyles, colors, extLst)
92 self.protections = self.cellXfs.prots
93 self._normalise_numbers()
---> 94 self.named_styles = self._merge_named_styles()
95
96
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in _merge_named_styles(self)
112
113 for style in named_styles:
--> 114 self._expand_named_style(style)
115
116 return named_styles
~\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py in _expand_named_style(self, named_style)
122 record
123 """
--> 124 xf = self.cellStyleXfs[named_style.xfId]
125 named_style.font = self.fonts[xf.fontId]
126 named_style.fill = self.fills[xf.fillId]
~\Anaconda3\lib\site-packages\openpyxl\styles\cell_style.py in __getitem__(self, idx)
183
184 def __getitem__(self, idx):
--> 185 return self.xf[idx]
186
187
IndexError: list index out of range
Solution 1:[1]
You'll get the Indexerror: list index out of range error when you try and access an item using a value that is out of the index range of the list and does not exist.
any way , Make sure you have the right type of Excel spreadsheets . I had the same Probleme and realized that I had saved it as a Strict XML Open Spreadsheet which still had the .xlsx extension.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | yassir ait el aizzi |