Skip to Content

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.

To report this post you need to login first.


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

  1. Former Member

    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

  2. Former Member

    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.



Leave a Reply