How to read multiple sheet’s from excel
Step 1: Take a script and define a global variable $G_SHEET_NO and assign it as 1 and a sql command to delete the data from table before loading.
$G_SHEET_NO = 1;
sql(‘SOURCE_DB’, ‘delete TBL_TARGET_EXCEL’);
Step 2: While loop condition will be $G_SHEET_NO <= 3 (In my case there are 3 sheet’s you can give any number here or to make it dynamic use global variable and pass the value to that variable at run time.
Step 3: First script in above picture will have code like below:
$G_LIST_SHEET = ‘Sheet’||$G_SHEET_NO;
print( ‘LOADING DATA FROM :’|| $G_LIST_SHEET);
Note: we’ll take another variable here ‘$G_LIST_SHEET’ and print function here is to print the sheet no in trace log that which sheet we are loading right now.
Step 4: Then Workflow ‘WF_SHEET_LIST’ will have a Dataflow
Step 5: Here SHEET_LIST EXCEL file properties are –
Step 6: Now the last script in while loop will have code like –
$G_SHEET_NO = $G_SHEET_NO + 1;
So this will just increment the sheet no and next it will load data from second sheet then third and so on.
Tip: Format of data should be the same for all sheets you want to load data from.