1. Please define two global variables one to store sheet name and second one to store total no of sheets. We will also have one more local variable for counter.
initialize $L_SHEET_COUNT = 1 in initializing_SCR.
2. Now drag While and add DF in which you have made excel sheet as source. Add two more script one before the DF and one after the DF.
As you can see condition in WHILE $L_SHEET_COUNT <= $G_Total_Sheet where $L_SHEET_COUNT = 1 and $G_Total_Sheet = 4 (because I have 4 sheets)
3. in Sheet_Name_SCR write below code
$G_SHEET_NAME = ‘sheet’||$L_SHEET_COUNT;
print(‘Loading ‘|| ‘Sheet’||$L_SHEET_COUNT);
4. increment $L_SHEET_COUNT = $L_SHEET_COUNT + 1 in increment_SCR.
5. Defining File Format.
Right Click on Excel Workbooks, select New and then create file format. Above screen shows how can you do it.
Make sure you have checked use first row values as column name and choosing worksheet option and passing parameter Global Variable which you have initialized in Sheet_Name_SCR.
Please do like if it helps.