Read each excel sheet as a different dataframe in Python

Question

I have an excel file with 40 sheet_names. I want to read each sheet to a different dataframe, so I can export an xlsx file for each sheet. Instead of writing all the sheet names one by one, I want to create a loop that will get all sheet names and add them as a variable in the "sheet_name" option of "pandas_read_excel"

I am trying to avoid this:

df1 = pd.read_excel(r'C:\Users\filename.xlsx', sheet_name= 'Sheet1');
df2 = pd.read_excel(r'C:\Users\filename.xlsx', sheet_name= 'Sheet2');
....
df40 = pd.read_excel(r'C:\Users\filename.xlsx', sheet_name= 'Sheet40');

thank you all guys

Just use dfs = pd.read_excel('filename.xlsx', sheet_name=None) to create a dictionary of sheet_name->dataframe ? — Jon Clements
– Jon Clements, Commented Jul 4, 2022 at 9:28
Does this answer your question? Loop in order to create several DataFrames for each sheet in an Excel file — T C Molenaar
– T C Molenaar, Commented Jul 4, 2022 at 9:30

Rohit Pahuja · Accepted Answer · 2022-07-04 11:17:02Z

6

Specifying sheet_name as None with read_excel reads all worksheets and returns a dict of DataFrames.

import pandas as pd

file = 'C:\Users\filename.xlsx'
xl = pd.read_excel(file, sheet_name=None)
sheets = xl.keys()

for sheet in sheets:
    xl[sheet].to_excel(f"{sheet}.xlsx")

edited Jul 4, 2022 at 11:17

answered Jul 4, 2022 at 9:40

Rohit Pahuja

6335 silver badges15 bronze badges

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

3 Comments

René Over a year ago

In general should the of exec be avoided. There are often clearer, more direct ways to get the same effect. More info: stackoverflow.com/questions/1933451/…

Rohit Pahuja Over a year ago

Thanks for suggesting the resource, I missed the export part in the question anyway.

I_m_Possible Over a year ago

I modified the export path and it worked. Thank you

René · Accepted Answer · 2022-07-04 10:15:36Z

2

I think this is what you are looking for.

import pandas as pd
xlsx = pd.read_excel('file.xlsx', sheet_name=None, header=None)
for sheet in xlsx.keys(): xlsx[sheet].to_excel(sheet+'.xlsx', header=False, index=False)

answered Jul 4, 2022 at 10:15

René

4,9195 gold badges29 silver badges59 bronze badges

Collectives™ on Stack Overflow

Read each excel sheet as a different dataframe in Python

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related