How can i read multiple excel sheets (Case insensitive) using read_excel function in Python

Question

I am using below code to read excel sheets from an excel file

df=pd.read_excel(ExcelFile,sheet_name="Sheet1")

What if i have 10 excel files with multiple tabs for example

Sheet1, Sheet2, Sheet3 and in some files

sheet names are in Capital for example "SHEET1", in this case how can i read those sheet names.

Maybe this will help you [link](https://stackoverflow.com/a/26521726/12128167) — Han, Nov 08 '19 at 07:17

Pyd · Answer 1 · 2019-11-08T08:45:24.953

1

you need sheet_name=None

dfs = pd.read_excel('filename.xlsx',sheet_name=None)

This will return a dictionary where key will be sheet_names and value will be dataframes.

you can see all the sheet names by,

dfs.keys()

to retrieve specific sheet data,

df = dfs['sheet_name']

edited Nov 08 '19 at 08:45

answered Nov 08 '19 at 07:18

Pyd

score 1 · Answer 2 · answered Nov 08 '19 at 07:18

based on the pandas read_excel documentation you can give the sheet names as a list. so you can give the sheet names like this:

sheet_names = ['sheet1','sheet2','sheet3']
df=pd.read_excel(ExcelFile,sheet_name=sheet_names)

if you give sheet_name = None It will read all the sheets.

2 Answers2