i want to read multiple sheets in an excel doc dynamically and store them in dataframes like sheet1, sheet2, sheet3, sheet4, sheet5 dynamically... The code should sense the number of sheets and store them dynamically in the dataframes with the given naming condition
with an excel workbook containing the following table twice, once in Sheet1 and again in Sheet2:
col1 col2
0 a 1
1 b 2
2 c 3
You can get a dictionary containing each df as a value with its sheet_name as a key, by setting sheet_name to None in the call to pd.read_excel
book = pd.read_excel(r"Data\Test_Book.xlsx", sheet_name= None)
book
Out:
{'Sheet1':
col1 col2
0 a 1
1 b 2
2 c 3,
'Sheet2':
col1 col2
0 a 1
1 b 2
2 c 3}
If you don't know the names of the sheets, and you want more flexibility when reading them, you can also pass the path to the pd.ExcelFile class. Which provides the sheet_names attribute, like so:
file = pd.ExcelFile(r"Data\Test_Book.xlsx")
file.sheet_names
Out: ['Sheet1', 'Sheet2']
then calling pd.ExcelFile.parse is equivalent to calling read_excel and you can simply pass the desired sheet_name as an argument
file.parse("Sheet1")
Out:
col1 col2
0 a 1
1 b 2
2 c 3
