Skip to Content
Technical Articles

SAP Intelligent RPA: Read Data from Excel: Unknown Header

Introduction

This is a submission of the Enhance your bot building with templates blog post series.

In this post I will show you how to automate a process in which you will have to read an Excel file with an unknown number of rows and columns. The result of this tutorial will be a ready-to-use template that could be used anytime when reading such files.

 

What will we learn in this tutorial:

  • How to use the Excel Library;
  • How to read unknown number of rows and columns from Excel File;
  • How to use nested Loops;

 

Steps to follow:

If you are familiar with Excel Library, skip to step 4:

  1. Create a new workflow;

2. Import Excel Library Scripts;

3. Initialize the Excel Library;

4. Set the right Context

5. Create a nested Loop: one to iterate the rows, another to iterate the columns on each row;

6. Add Activities and functions from ‘Excel Lib’ category;

7. Use the read values;

 

Prerequisites:

  • Desktop Studio 1.0.9.16;
  • Microsoft Office;

Instructions:

1. Create a new workflow

Create a new project and give it a name.

Go to ‘Workflow’ perspective and create a new workflow.

2. Import Excel Library Scripts

In the work space of your new workflow you can now add activities from ‘Excel Lib’ Category, But, in order for your project to compile and run without errors, you first have to enable the Excel Library scripts in your project:

-> go to ‘Scripts’ perspective;

-> select ‘Project’ tab(bottom-left corner);

->right click on ‘GLOBAL’ from the Panel;

->select ‘Include library Script’: The ‘Add Library Script’ window pops;

-> enable ‘Excel integration’;

->click on ‘Save’;

 

 

3. Initialize the Excel Library

First Activity that we have to add in the workflow is ‘Initialize Excel‘ , which initializes Excel Library. This one is always used in pair with ‘End Excel‘ activity in order to close the Excel Library once you are done using it in your project. Both activities do not require parameters.

Second activity in the flowchart is ‘Open an existing Excel file‘ in which we will set as parameters the path and the name of the file that we want to read. The name of my excel file is Read_UnknownHeader.xlsx. Because the excel file i want to read was saved in the ‘log’ folder inside my project, I will use the ctx.options.path.log to acces the location:

 

4. Set the right Context

Now that we accessed our file, we can start reading its content:

The saved data should be saved in some variables, and for that we have to create the proper Context structure:

5. Create a nested Loop for reading the values:

In the next steps we will be reading the data row by row. For each row found, we will be reading again, cell by cell( for each column found in the file).

The Loop structure contains 3 elements:

  • Start Loop :  to determine the start of the loop;
  • Exit Loop( can be used at the beginning or the end ): to check the condition to exit the loop;
  • Loop to start: to limit the steps that should be executed in the loop;

In my example i chose to use ‘Exit Loop’ activity at the beginning of both loops:

  1. To iterate the rows, we will be using:
    • a Loop (Loop R);
    • ctx.excel.sheet.getLastRow() function from the Excel Library, in order to determine the number of rows inside the file. This value will be used to determine the number of times the Loop R will be executed.
  2. To iterate the columns, we will be using:
    • a Loop( Loop C);
    • ctx.excel.sheet.getLastColumn() function from the Excel Library, in order to determine the number of columns inside the file. This value will be used to determine the number of times the Loop C will be executed for each row.
    • Custom function that will return the letter of last column that is completed in my file;

 

To start the Loop R, I added the Start Loop and Exit Loop activities. The later one requires as parameter the condition based on which the bot will end the loop: in my example, i want to exit the loop when the iterator, sc.localData.Startloop1(is automatically created) will reach the same value as the number of rows to be read from the file:

 

Pay attention to the name of the iterator that is generated! Depending on the number of loops you include in your workflow, the name can differ.

Immediately after, I will start Loop C. The condition to exit Loop C is when the iterator, sc.localData.Startloop2 in this case, will reach the number of columns in my file:

 

 

6. Add Activities and functions from ‘Excel Lib’ category

We already added the rules based on which the bot will be able to access each value inside the Excel File. Now we have to add the activities that store those values inside the Context, in order to use them according to our scenario.

I grouped these activities in a Sequence activity from Flow category, in which i can add the rest of my actions:

Inside the Sequence i added an If condition to check if a new row is found. If the condition is met, a new Values Array in my context must be initialized, in which to store data inside:

Next step is to add a Custom activity in which i will declare a function that will return the letter of a column based on its order( e.g. input: 3; output: ‘C’ ).

	function numToLetter(num){
  var s = '', t;

  while (num > 0) {
    t = (num - 1) % 26;
    s = String.fromCharCode(65 + t) + s;
    num = (num - t)/26 | 0;
  }
  return s || undefined;
}

This function will be used as a parameter in the next activity that stores the Excel values in the context: ctx.excel.sheet.getCell() :

 

This function will generate the following line:

rootData.Items[sc.localData.Startloop1].Values[sc.localData.Startloop2] = ctx.excel.sheet.getCell(sc.localData.Startloop1+2, numToLetter(sc.localData.Startloop2+1));

Notice that the position(sc.localData.Startloop1 and sc.localData.Startloop2) was not mentioned anywhere as parameters, and was set automatically after I build the project.

Next 2 steps should be ‘Loop to start‘ activities to close both Loop R and Loop C, but first i should add a delay of 100 ms in order to avoid the  ‘Error, Out of stack space’ :

 

7. Use the read values

We are done reading and storing the Excel values. Next step is to use those values according to each scenario. In my example i will display them in the console using ctx.log() function, and i will show you how to call the variables:

I added a Custom function, and added the following code by accessing the Scripts:

// Display values
		for(var i = 0; i<rootData.Items.length;i++){
		ctx.log('Row '+i+' contains following values:');
		for(var j = 0;j<rootData.Items[i].Values.length;j++){
			//if is date, change format 
				if(typeof rootData.Items[i].Values[j] ==='date'){
					ctx.log(ctx.getDate(new Date(rootData.Items[i].Values[j]),'/'));
			//else print the value
				}else{
					ctx.log(rootData.Items[i].Values[j]);
				}
		}
	}

 

Notice that when displaying the values, i added a condition to check if the type of the value is Date, in order to display with a proper format.

 

We will close the workflows with End Excel Activity from Excel Lib category inside the Activities tab. Use this whenever you are done using the Excel Library scripts inside your workflow.

Close your workflow with an End activity from Scenario category.

 

 

 

Conclusion

Using Loops in SAP Intelligent RPA is very simple and intuitive and  nesting multiple loops is no different. If you follow this tutorial step by step you will end with a bot that can read any table inside an Excel file, without knowing its dimensions.

The best part is that after you build it once, it will be a ready-to-use bot that won’t require any changes in order to access the values of any table in an Excel file.

20 Comments
You must be Logged on to comment or reply to a post.
      • Monica Paiu

        Hi Monica,

        My bad, i should have explained the question better.  I am just thinking of typical single sheet  holding Header and details items . for e.g. could be SO Header and SO Item details in below format

        I can think of looping through each row and checking for Header of Item identifier and processing accordingly.

        Is there any better way?

        Regards

        Vijay

        /
        • Hi Vijay,

          Assuming you want to read the data as two separate tables, you have to change the Exit loops condition in order for the reading to stop when it hits the next header:

          if(ctx.excel.sheet.getCell(sc.localData.Startloop_I+2,'A') == 'H' )

          To start reading the  next table, you should change the first parameter in getCell() function :

          ctx.excel.sheet.getCell(sc.localData.Startloop_H + ctx.excel.sheet.getLastRow('A1')   ,'A')

          You can find the link to the documentation for each function in the blog post.

           

          Best regards,

          Monica

           

  • Hi Monica,

    I get an error in Start Loop: “Start block doesn’t have a loop (error FWK00090).

    Can you help me with this?

     

    Thank you in advance!

    BR,

    /
    • Hi Toni,

      You get the error because you missed one of the three objects that form a loop. Please re-check step 5 to see what it is missing from your loop structure.

      It might be that you missed the last activity : Loop to the start block.

       

      Best regards,

      Monica

      • Hi Monica,

        the excel is opening and exiting immediately in debugging stage and showing”Excel not found and create new one”. could you please help me on it

         

         

        • Hi Madhavi,

          The message is just a bug. It appears even when the file is accessible, otherwise you should have got an error message(in read) about it. Use some Log functions to display the read value sin the console to check if it is working.

           

           

           

          • Hi Monica,

            thank you the  problem is solved…now im trying to create 5 sales orders  records, the first record has been generated ,. the second record one of the click  is not found…could you please help me out ..

            im inserting the error page,.

             

            /
  • Hello Monica,

     

    Is there a way (or Tutorial) which shows how you take the data from excel and input values to specific fields on SAP – So if you had an excel file with material number, RPA could take the material # and put in a field in ERP (say MM03 for example)?

  • This is awesome and will definitely going to be very useful.

    I have a question, in the exit condition, how can we specify to run the loop a certain number of times?

     

     

    • Please read step 5.

      For every loop we have a variable that counts the iteration.

      If you don’t change the stepnames of your loop, the default name of the variable is sc.localData.Startloop. For every iteration completed in the loop, the sc.localData.Startloop value  is incremented by 1.

      Just use this in your condition if you want your loop to be executed a certain number of times

      ex:

      if(sc.localData.Startloop>9)
  • Hi Monica Paiu

     

    Hope you are doing good. Thank you for sharing a detailed blog about “How to read the excel data”.

    I know, this is not the first time this question is getting popped up. But, after going through the step 5 clearly, I’m getting the error as “Start block doesn’t have a loop”.

    Also, could u please put more light on how to use “Loop to start“.

     

    Thanks 🙂

    /
    🙂
    • Hi Monica Paiu,

       

      The issue got resolved and i found a way to use the “Loop to start” activity. Following is the screenshot of workflow,

       

      The “loop to start” activity is necessary to run a loop from start to exit activity.

       

      /
  • Hello Monica!
    When building a project and transferring data from excel to csv, the following happens:
    the cells contain not only the contents of the excel file, but also the name of the method that was used.
    Context variables are used.
    Tell me how you can avoid this?
    Thank you so much!