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:
- 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:
- 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.
- 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.
- SAP Managed Tags:
20 Comments
