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.

4 Comments

You must be Logged on to comment or reply to a post.

Santosh G. July 13, 2016 at 7:55 am

Small and crisp, Good job Gokul !!!

like (0)
DILIP KUMAR May 24, 2017 at 4:40 am

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

like (0)
1. Gokul Gawande Post authorJune 6, 2017 at 11:04 am
  
  Hi Dilip,
  
  Check if your target table is truncate and load or drop and recreate.
  
  Regards,
  
  Gokul
  
  like (0)
Vinay Kumar March 27, 2018 at 4:30 pm

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

like (0)