BUG: Excel import crashes on simple file

[apacha]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
file = pd.read_excel("/Users/alex/Desktop/BrokenExcel.xlsx", sheet_name=None, header=1)

Issue Description

Loading the attached file produces an exception and crashes in 1.5.0 but worked in 1.4.3.

venv/bin/python /Users/alex/Library/Application Support/JetBrains/PyCharm2022.2/scratches/scratch_5.py 
Traceback (most recent call last):
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/python_parser.py", line 701, in _next_line
    line = self._check_comments([self.data[self.pos]])[0]
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/python_parser.py", line 386, in _infer_columns
    line = self._next_line()
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/python_parser.py", line 714, in _next_line
    raise StopIteration
StopIteration

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/alex/Library/Application Support/JetBrains/PyCharm2022.2/scratches/scratch_5.py", line 2, in <module>
    file = pd.read_excel("/Users/alex/Desktop/BrokenExcel.xlsx", sheet_name=None, header=1)
  File "venv/lib/python3.9/site-packages/pandas/util/_decorators.py", line 211, in wrapper
    return func(*args, **kwargs)
  File "venv/lib/python3.9/site-packages/pandas/util/_decorators.py", line 317, in wrapper
    return func(*args, **kwargs)
  File "venv/lib/python3.9/site-packages/pandas/io/excel/_base.py", line 491, in read_excel
    data = io.parse(
  File "venv/lib/python3.9/site-packages/pandas/io/excel/_base.py", line 1711, in parse
    return self._reader.parse(
  File "venv/lib/python3.9/site-packages/pandas/io/excel/_base.py", line 867, in parse
    parser = TextParser(
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 1870, in TextParser
    return TextFileReader(*args, **kwds)
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 1442, in __init__
    self._engine = self._make_engine(f, self.engine)
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py", line 1747, in _make_engine
    return mapping[engine](f, **self.options)
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/python_parser.py", line 123, in __init__
    ) = self._infer_columns()
  File "venv/lib/python3.9/site-packages/pandas/io/parsers/python_parser.py", line 396, in _infer_columns
    raise ValueError(
ValueError: Passed header=[1], len of 1, but only 1 lines in file

Process finished with exit code 1

Expected Behavior

Loading of the Excel file (as in 1.4.3)

Installed Versions

INSTALLED VERSIONS

commit : 87cfe4e
python : 3.9.13.final.0
python-bits : 64
OS : Darwin
OS-release : 21.6.0
Version : Darwin Kernel Version 21.6.0: Mon Aug 22 20:17:10 PDT 2022; root:xnu-8020.140.49~2/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : en_US.UTF-8
pandas : 1.5.0
numpy : 1.23.3
pytz : 2022.1
dateutil : 2.8.2
setuptools : 62.6.0
pip : 22.2.2
Cython : None
pytest : 7.1.3
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.1
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.9.0
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : None

[apacha]

Here is the Excel file in question for reproducing the problem
BrokenExcel.xlsx

[apacha]

On a second thought, I'm not sure what the expected behavior should be and why it didn't crash in 1.4.3?

[phofl]

c3b4af9ea969122055af364389fbb3093c4c78d2 is the first bad commit
commit c3b4af9ea969122055af364389fbb3093c4c78d2
Author: Patrick Hoefler <[email protected]>
Date:   Fri Jun 24 23:10:10 2022 +0200

    BUG: read_csv ignoring non existing header rows for python engine (#47493)

Previously, these rows were just ignored, which returned weird results. we are strict when validating the header rows since 1.5. Why specifying non-existing header rows at all?

So the new behaviour is expected.

[apacha]

Alright, thanks for the info!

[apacha]

@phofl Sorry for re-opening, but I'm a bit torn apart by this new behavior after trying to fix everything on our side, it seems it will be impossible for us to use Pandas 1.5.X because of this problem: We regularly have Excel documents with multiple sheets. Some of these sheets might be empty or not have a header - that's OK. There is only one header parameter, so if the sheets in which I am interested in have headers in the second row, I have to specify this parameter. Previously, it was all good, as we just ignored sheets that violated this, or left them empty. Now I'm getting ValueError: Passed header=[1], len of 1, but only 1 lines in file which breaks lots of our workflows, as Excel files that were read just fine in the last version can't be opened anymore.

My problem with the current solution is:

• It breaks compatibility in production in a really nasty way because it is not immediately clear WHY this is failing, unless you have an extremely simplified file like the attached test-file. For real Excel documents ValueError: Passed header=[1], len of 1, but only 1 lines in file doesn't mean anything at all. Moreover the first thing you see is IndexError: list index out of range, which gives even less information.
• The provided message does not indicate which sheet is affected, thus making it really hard to locate the problem in the Excel file.
• We are going from "Do nothing"-behavior to "We completely fail"-behavior instead of just issuing a warning but still loading the remaining sheets from the Excel document (which would be acceptable, though not great).

[MarcoGorelli]

The provided message does not indicate which sheet is affected, thus making it really hard to locate the problem in the Excel file.

Sure, an error message indicating which sheet this was in might be good

just issuing a warning but still loading the remaining sheets

Not overly keen on this kind of behaviour - why not do something like

sheet_names = pd.ExcelFile("/Users/alex/Desktop/BrokenExcel.xlsx").sheet_names
output = {}
for sheet_name in sheet_names:
    try:
        df = pd.read_excel("/Users/alex/Desktop/BrokenExcel.xlsx", sheet_name=sheet_name, header=1)
    except ValueError:
        continue
    output[sheet_name] = df

?

[apacha]

@MarcoGorelli Yes, having a more descriptive error message would be fantastic. Thanks also for the proposed solution - I will change our code to read the sheets like this! 🙏

[aaossa]

Can I work on this issue? I can start by exploring the changes required to include the name of the offending sheet in the error message

EDIT: I tried to avoid modifying a large portion of code while also respecting the responsibility of each of the parser classes. Also, this seems to generalize to other ValueError that may be raised:

diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index f555e7c5f5..3b12868144 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -897,6 +897,10 @@ class BaseExcelReader(metaclass=abc.ABCMeta):
                             asheetname
                         ].columns.set_names(header_names)

+            except ValueError as err:
+                err.args = (f"{err.args[0]} (Sheet: {asheetname})", *err.args[1:])
+                raise err.with_traceback(err.__traceback__)
+
             except EmptyDataError:
                 # No Data, return an empty DataFrame
                 output[asheetname] = DataFrame()

This results in the following exception message:

ValueError: Passed header=[1], len of 1, but only 1 lines in file (Sheet: Sheet1)

Full traceback

Traceback (most recent call last):
  File ".../pandas/io/parsers/python_parser.py", line 700, in _next_line
    line = self._check_comments([self.data[self.pos]])[0]
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File ".../pandas/io/parsers/python_parser.py", line 385, in _infer_columns
    line = self._next_line()
  File ".../pandas/io/parsers/python_parser.py", line 713, in _next_line
    raise StopIteration
StopIteration

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "main.py", line 2, in <module>
    file = pd.read_excel("BrokenExcel.xlsx", sheet_name=None, header=1)
  File ".../pandas/util/_decorators.py", line 211, in wrapper
    return func(*args, **kwargs)
  File ".../pandas/util/_decorators.py", line 331, in wrapper
    return func(*args, **kwargs)
  File ".../pandas/io/excel/_base.py", line 491, in read_excel
    data = io.parse(
  File ".../pandas/io/excel/_base.py", line 1740, in parse
    return self._reader.parse(
  File ".../pandas/io/excel/_base.py", line 902, in parse
    raise err.with_traceback(err.__traceback__)
  File ".../pandas/io/excel/_base.py", line 867, in parse
    parser = TextParser(
  File ".../pandas/io/parsers/readers.py", line 1899, in TextParser
    return TextFileReader(*args, **kwds)
  File ".../pandas/io/parsers/readers.py", line 1460, in __init__
    self._engine = self._make_engine(f, self.engine)
  File ".../pandas/io/parsers/readers.py", line 1771, in _make_engine
    return mapping[engine](f, **self.options)
  File ".../pandas/io/parsers/python_parser.py", line 122, in __init__
    ) = self._infer_columns()
  File ".../pandas/io/parsers/python_parser.py", line 395, in _infer_columns
    raise ValueError(
ValueError: Passed header=[1], len of 1, but only 1 lines in file (Sheet: Sheet1)

[MarcoGorelli]

nice - nitpick would to have sheet: rather than Sheet: , but that seems like a minimal change that would be useful to users - want to open a PR?

[aaossa]

nice - nitpick would to have sheet: rather than Sheet: , but that seems like a minimal change that would be useful to users - want to open a PR?

Sure, I'll apply the change and make the PR soon

[aaossa]

@MarcoGorelli , the linked PR #49186 is ready for review 👌 BTW, should this issue be renamed? It ended up not being a bug but a feature request it seems