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 gets you started. — Siddharth Rout
– Siddharth Rout, Commented Sep 12, 2020 at 6: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 Goyani
– Dishin Goyani, Commented Sep 12, 2020 at 6:31

sammywemmy · Accepted Answer · 2020-09-12 06:26:00Z

3

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')])

answered Sep 12, 2020 at 6:26

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

unbe_ing · Accepted Answer · 2020-09-12 06:25:50Z

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, 2020 at 6:25

unbe_ing

1957 bronze badges

1 Comment

Algebra8 Over a year ago

I'd recommend making the except catch specific exceptions. That's too broad.

mazore · Accepted Answer · 2020-09-12 06:26:27Z

0

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'

answered Sep 12, 2020 at 6:26

mazore

1,0447 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to read excel sheets with varying names in Python

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related