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.
Small and crisp, Good job Gokul !!!
Hi Gokul Gawande
Good explanation but i’m getting only last sheet data
have 4 sheets each have 3 records ,total 12 records but i’m getting only 4th sheet data
Hi Dilip,
Check if your target table is truncate and load or drop and recreate.
Regards,
Gokul
Hi Gokul,
I have a requirement to read mutliple excel files. I tried using wildcard but am getting error. Can you advise how to achieve this requirement.
Thanks