How to read excel sheets with varying names in Python

Question

I am new to python and I am currently trying to create a dataframe by reading specific sheet from about 20 spreadsheets. However, some of the spreadsheets have a different name to the tab I want to get data from.

I know that the tabs are named 'A' or 'AA'

I would like to know if there is a way I can pass the two names in the pandas read_excel as the names vary in some sheet. I am essentially looking for something like

df = pd.read_excel('file.xlsx', sheet_name = 'A' or 'AA')

if I pass the two names with an or, it returns an error. Is there a way for read_excel function to get data from a sheet named either 'A' or 'AA' in the excel file?

Thank you.

I do not work with python but I guess the logic would be get the sheet names from the excel file and then checking if it has "A" or "AA" and then passing that name? See if [THIS](https://stackoverflow.com/questions/12250024/how-to-obtain-sheet-names-from-xls-files-without-loading-the-whole-file) gets you started. — Siddharth Rout, Sep 12 '20 at 06:23
Are those sheets containing only single tab in each? can you use index instead name like `pd.read_excel('file.xlsx', sheet_name = 0)` — Dishin H Goyani, Sep 12 '20 at 06:31

score 3 · Answer 1 · answered Sep 12 '20 at 06:26

Try and see if this works:

# this reads in the data and allows access to the sheet names
# also memory efficient
xls = pd.ExcelFile('path_to_file.xls')

# iterate to find sheet name that matches 
data = pd.read_excel(xls, sheet_name = [name for name in xls.sheet_names
                                        if name in ('A','AA')])

score 0 · Accepted Answer · answered Sep 12 '20 at 06:25

0

You could use the try except method, to first try opening sheet A and if that fails try opening sheet AA:

try:
    df = pd.read_excel('file.xlsx', 'A')
except:
    df = pd.read_excel('file.xlsx', 'AA')

This won't have the desired outcome if there is a workbook with both an A and an AA sheet name, or if there is a workbook without either sheet names.

answered Sep 12 '20 at 06:25

jwjhdev

195
7

I'd recommend making the `except` catch specific exceptions. That's too broad. – Algebra8 Sep 12 '20 at 07:00

score 0 · Answer 3 · answered Sep 12 '20 at 06:26

The or operator does something completely different to do with booleans.

Try this

try:
    df = pd.read_excel('file.xlsx', sheet_name='A')
except:
    df = pd.read_excel('file.xlsx', sheet_name='AA')

This will try to open a sheet with the name 'A', and if there is none and pandas throws an error, it will open the sheet named 'AA'

How to read excel sheets with varying names in Python

3 Answers3