Skip to Content
Author's profile photo Gokul Gawande

How to read multiple sheet’s from excel

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.

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Santosh G.
      Santosh G.

      Small and crisp, Good job Gokul !!!

      Author's profile photo Reddy KUMAR
      Reddy 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

      Author's profile photo Gokul Gawande
      Gokul Gawande
      Blog Post Author

       

      Hi Dilip,

      Check if your target table is truncate and load or drop and recreate.

      Regards,

      Gokul

      Author's profile photo Vinay Kumar
      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

      Author's profile photo Suneel DS
      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.

       

      Author's profile photo Rajan Burad
      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

      Author's profile photo Suneel DS
      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.

      Author's profile photo Rajan Burad
      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

      Author's profile photo Vinay Kumar
      Vinay Kumar

      Sounds Interesting to me.  I will try this!

      thank you

      Author's profile photo Vinay Kumar
      Vinay Kumar

      I am rare to SCN, so sorry to late reply.

      I noticed reading multiple excel files does not seem to be possible in BODS, but text and csv files works for wild card file extension.

      Author's profile photo Rajan Burad
      Rajan Burad

      Hi Gokul,

      Short and clear explanation. Nice one 🙂

      Author's profile photo Murali Krishna
      Murali Krishna

      Hi Gokul Gawande,

      Helpful information. I have the same requirement but my worksheet is having names such as sales, Promotion, TimeCluster and so on. Here you provided the extract from sheet1, sheet2 etc.

      How to load if we have a worksheet having different names?

      Thanks,

      Murali

      Author's profile photo Arun Sasi Maliyakkal
      Arun Sasi Maliyakkal

      Excellent Post Gokul!! Keep it up

      Author's profile photo Hitesh Agashe
      Hitesh Agashe

      Hi Gokul,

      It is an informative blog. I have a doubt that what if there are number of sheets available.

      So in that case we can not count manually and assign it in while condition.

      Will you please share how can that be achieved by declaring the variable and assigning that variable a value of total number of sheets presents?

      This will automate the process instead of counting the sheets manually,

      Thanks.