Skip to Content

How to read multiple sheet’s from excel

July 12, 2016 | 633 Views |
 loading multiple sheetmultiple excel sheetsap bods
Follow

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.

9 Comments

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

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

    (0) 
  3. Vinay Kumar

    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

    (0) 
    1. Suneel DS

      Hi Vinay,

      I have the same requirement as yours. can u please explain how to read multiple excel files from a shared directory?

      thanks in advance.

       

      (0) 
      1. Rajan Burad

        Hello Suneel,

        Use wait_for_file function to get the name of files and process each file one by one using a loop. Pass a parameter in file name section of format editor. Your parameter will contain the name of file to process.

        Let us know if it helps, if not then will help you with screenshots.

        Thanks,

        Rajan

        (0) 
        1. Suneel DS

          Hi Rajan,

          thanks for your quick reply.

          i want to load the files all at a time using wild characters * in file name and I want to put the shared folder path in directory place. does it work? if not please provide me the alternate solution .

          Thank you.

          (0) 
          1. Rajan Burad

            Hello Suneel,

            I cannot replicate your scenario as MS office isn’t configured on my job server right now. But I think you can use *.xls method to extract the list of files having the same structure and load the data in from all those files in one go.

            If this doesn’t work then go for this method:

            1. Use for wait for file function to extract list of files.
            2. Once you have the list using parameter load the file names in a temp table and generate a seq_id.
            3. Now you’ve all the list of file names in a temp table. Connect a script after that and initialise a counter and another variable to get the total list of files.
            4. Connect a while loop after it and place a script1 -> Dataflow -> Script2
            5. In script 1 get the name of the file using SEQ_ID and pass it to a variable and then to a parameter.
            6. Inside dataflow, you’ve your excel; pass the parameter in the file name.
            7. Your data will start loading in target table  ( Ensure you de-select the delete data before loading option)
            8. In script to initialize your counter value.

            This is another way through which you can process your files present in the shared path.

            Let us know if it works.

            Thanks,

            Rajan

            (0) 

Leave a Reply