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 a fixed(known) header, but an unknown number of rows.
What will we learn in this tutorial:
- How to use the Excel Library;
- How to read unknown number of rows from Excel File;
- How to use Loops;
Steps to follow:
- Create a new workflow;
2. Import Excel Library Scripts;
3. Add Activities and functions from 'Excel Lib' category;
4. Use a loop for dynamic reading;
5. Read the file row by row;
6. Display 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. Add Activities from 'Excel Lib' category
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 CreateDatatype.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:
Now that we accessed our file, we can start reading its content:
The saved data should be saved in some variables, and for it we have to create the proper Context structure:
4. Use a loop for dynamic reading
In the next steps we will be reading the data row by row. In order to fully automate this process, we will be using:
- Loop structure in order to access all the rows;
- 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 will be executed.
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:
'Exit loop' 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.Startloop(is automatically created) will reach the same value as the number of rows to be read from the file.
5. Read the file row by row
Now we can add the functions that read data from the file and stores the values in the context. For this i will be using 'Get one value from a cell' activity for each column.
As parameters, i used:
- sc.localData.Startloop(iterator)+2 to indicate the row number;
- the letter of the colum to indicate the column to be read;
- $data$ to indicate where to store the value;
6. Display the read values
Before closing the loop, we want to print the values in the console, in order to see the data that have been read. To do that, use the 'Log' activity from 'System' category. As parameter, enter the message you want to display:
Next step should be 'Loop to start' activity, but first i should add a delay of 10 ms in order to avoid the 'Error, Out of stack space' :
I ended my workflow with 'End Excel' activity to mark the end of using the library, and 'End scenario' in order to mark the end of my process.
Conclusion
This blog post should help you to understand the use of the 'Excel Library' and how to integrate Loops for dynamic activities. At the end, you should be able to understand this functionalities and use them according to your scenario.
- SAP Managed Tags:
19 Comments
