Automating Dataframe Extracting From Different Workbook Of Excel File Using Python

Below is the script that I am using For reading file excel = pd.ExcelFile('data.xlsx') To get the different sheets I am using excel.sheet_names Let say the sheet_names are [A,B,C

Solution 1:

I think you it would be better to use loop with locals() see my code below. the reason why "locals()[a]" is used instead of a directly is that if you use "a=pd.read_excel()", only variable a will has recent sheet of data.xlsx, H sheet, and there would be no df_A, df_B, ... df_H variables

for i in pd.ExcelFile('data.xlsx').sheet_names:
 
    print(i)
    a='df_'+i
    locals()[a]=pd.read_excel('data.xlsx', sheet_name=i, header=0)
    print(locals()[a])