BUG: read_excel() using openpyxl engine header argument not working as expected · Issue #38956 · pandas-dev/pandas (original) (raw)

Code Sample, a copy-pastable example

I am happy to provide the file I am using privately, offline if necessary. This would be necessary to directly reproduce this error.

import pandas as pd
df = pd.read_excel(myfile.xlsx, engine='openpyxl', header=2)

Throws error:
...ValueError: Passed header=2 but only 2 lines in file

Problem description

I changed my read_excel() code to be now using engine='openpyxl with the 1.2 read_excel() changes, but the header argument is no longer working as it was before. I am trying to read in a .xlsx file that has 2 initial rows that should be skipped, and the 3rd row contains the headers. Thus, I am passing header=2, which should automatically skip the first two rows and use the 3rd (0-indexed) row as the headers. However, I am getting the following error: ValueError: Passed header=2 but only 2 lines in file. I have confirmed multiple times that this file contains more than 2 lines - it contains thousands (happy to provide this file offline if necessary). I was able to previously run this code with the same file (before the xlrd deprecation, using the same header specification. With the openpyxl changes in 1.2, should we be using the header argument any differently? Thank you.

This is related to changes in #35029.

Expected Output

Intended behavior would be that the .xlsx file is successfully read in as a dataframe using headers in row 3 and does not throw the above error.

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.8.6.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 19.6.0
machine          : x86_64
processor        : i386
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.0.3
numpy            : 1.19.4
pytz             : 2020.5
dateutil         : 2.8.1
pip              : 20.3.3
setuptools       : 50.3.2
Cython           : None
pytest           : 4.3.1
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : 2.11.2
IPython          : 7.19.0
pandas_datareader: None
bs4              : None
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : None
matplotlib       : None
numexpr          : None
odfpy            : None
openpyxl         : 3.0.5
pandas_gbq       : None
pyarrow          : None
pytables         : None
pytest           : 4.3.1
pyxlsb           : None
s3fs             : 0.4.2
scipy            : None
sqlalchemy       : None
tables           : None
tabulate         : None
xarray           : None
xlrd             : 2.0.1
xlwt             : None
xlsxwriter       : None
numba            : None