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.

              /wp-content/uploads/2016/07/1_993612.png

          $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.

          /wp-content/uploads/2016/07/2_993613.png

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

            /wp-content/uploads/2016/07/3_993644.png

Step 5: Here SHEET_LIST EXCEL file properties are –

            /wp-content/uploads/2016/07/4_993645.png

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.

2 Comments

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

  1. DILIP KUMAR

    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

    (0) 

Leave a Reply