0

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

I_m_Possible
  • 31
  • 2
  • 7
  • 3
    Just use `dfs = pd.read_excel('filename.xlsx', sheet_name=None)` to create a dictionary of sheet_name->dataframe ? – Jon Clements Jul 04 '22 at 09:28
  • Does this answer your question? [Loop in order to create several DataFrames for each sheet in an Excel file](https://stackoverflow.com/questions/60431442/loop-in-order-to-create-several-dataframes-for-each-sheet-in-an-excel-file) – T C Molenaar Jul 04 '22 at 09:30
  • see if it helps https://stackoverflow.com/questions/17977540/pandas-looking-up-the-list-of-sheets-in-an-excel-file – Vivek Singh Jul 04 '22 at 09:39

2 Answers2

5

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")
Rohit P
  • 613
  • 5
  • 13
  • 2
    In general should the of `exec` be avoided. There are often clearer, more direct ways to get the same effect. More info: https://stackoverflow.com/questions/1933451/why-should-exec-and-eval-be-avoided – René Jul 04 '22 at 10:29
  • Thanks for suggesting the resource, I missed the export part in the question anyway. – Rohit P Jul 04 '22 at 11:15
  • I modified the export path and it worked. Thank you – I_m_Possible Jul 05 '22 at 07:59
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)
René
  • 4,594
  • 5
  • 23
  • 52