pandas pd.read_excel() returning empty dictionary

0
votes

I am a novice Python programmer and I am having an issue loading an xlsx workbook with the pd.read_excel() function. The pandas read_excel documentation says that specifying 'sheet_name = None' should return "All sheets as a dictionary of DataFrames", however I am getting an empty dictionary back:

template_workbook = pd.read_excel(template_path, sheet_name=None, index_col=None)
template_workbook

Returns:

OrderedDict()

When I try to print the worksheet names in the dictionary:

template_workbook.sheet_name

Returns:

AttributeErrorTraceback (most recent call last) 
<ipython-input-67 e76a0b915981> in <module>()
----> 1 template_workbook.sheet_name
AttributeError: 'OrderedDict' object has no attribute 'sheet_name'

It is not clear to me why the worksheets are not being listed in the output dictionary. Any tips are greatly appreciated.

I have 26 tabs/sheets, and am trying to fill 23 using the tab names for indexing. Here is a snip of one tab in the workbook - the fields in yellow are the ones to be filled after indexing by tab name

2
pythonexcelpandas
The code works for me using an excel file with 2 tabs. Can you sow an example of your sheets? and what is the exact template path? – Bram van Hout
Hi there! Thank you for trying that. I updated the question to include a snip of the sheets. The exact template path is: template_path = 'C:\\Users\\...\\...\\...\\RSV\\Input_Data\\RSV_output_template.xlsx' – e.stearns

2 Answers

0
votes

When you use read_excel with multiple sheets, pandas will return a dictionary:

Returns: DataFrame or Dict of DataFrames

If you have an dictionary, you can use the .keys() method to see the file tabs, as in:

print(template_workbook.keys())
0
votes

I found this post through Google as I ran into this same problem. Unfortunately, no errors were thrown which is not very helpful, so I'm posting this answer to help the next person who might find this.

The read_excel function in Pandas doesn't exhaustively support ALL Excel functionality. This means if you are using some advanced Excel functionality (named ranges) your data might not be parsed correctly when Pandas tries to read your Excel data.

I tried to simplify my Excel file as much as possible which still didn't work, so I created a new Excel Workbook and copied my data in sheet by sheet. This ended up working for me.

So my advice is to keep your Excel file as simple as possible and you'll probably be able to import it with Pandas. If you send over your exact Excel file I'm happy to help debug (I know this is coming years after the question though).