Pandas read_excel returning 'not enough values to unpack (expected 2, got 1)'

Question

My problem is pretty simple, I'm just trying to read a locally located excel file into a dataframe using pandas.

filename = 'test.xlsx'
df = pd.read_excel(filename)

The xlsx file has multiple sheets, but I get the same "ValueError: not enough values to unpack (expected 2, got 1)" even when specifying sheetname.

Traceback (most recent call last):

File "", line 1, in pd.read_excel(filename)

File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel.py", line 200, in read_excel io = ExcelFile(io, engine=engine)

File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel.py", line 257, in init self.book = xlrd.open_workbook(io)

File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd__init__.py", line 422, in open_workbook ragged_rows=ragged_rows,

File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\xlsx.py", line 833, in open_workbook_2007_xml x12sheet.process_stream(zflo, heading)

File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\xlsx.py", line 553, in own_process_stream self.do_merge_cell(elem)

File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\xlsx.py", line 609, in do_merge_cell first_cell_ref, last_cell_ref = ref.split(':')

ValueError: not enough values to unpack (expected 2, got 1)

Edit: I created a new Excel file, and copied two of the tabs from the original file over to the new Excel file. Pandas read_excel worked with the new file. However, I want to be able to work from the original.

Answer 1

I still don't fully understand the mechanics of this issue, but I was able to work around it. I used urlopen from urllib.requests to download the file locally, and used openpyxl to load the workbook, and then loaded the sheet I wanted. The sheet has 5 rows of merged cells at the top and 5 rows of merged cells at the bottom. I then un-merged just the last 5 rows of merged cells using the unmerge_cells method. I then saved the workbook again with the modified sheet. I was then able to run pd.read_excel with the skiprows=5 parameter. It worked this way.