SAP Intelligent RPA: Read Data from Excel: Fixed Header
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;
- Desktop Studio 220.127.116.11;
- Microsoft Office;
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.
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.
Thankyou for Information. It was helpful.
how can we do start and end loop with SAP GUI , Do we need to start new session every time?
Between ‘Start Loop’ and ‘Loop to the Start’ activities you should only integrate the steps that must be executed multiple times( each time the bot enters a new iteration).
Opening a SAP GUI session should be done only once, and this step should be integrated before starting the loop.
Hope this answers your question.
Very useful information！
I tried to follow the description on the blog, but I ran into some problems。How do I create the proper Context structure？
Could you share a more detailed example?
The context can be created in the Workflows perspective, by opening the Context panel. In here you can add elements to create your desired structure by right-clicking in the blank space and select either Folder or Item.
A Folder can contain multiple Folders and Items, and an Item cannot contain anything else( is a final node).
For each type of element, you can define it as an array by checking the box on the right, on each element's level. See example below:
Setting the proper Context structure depends on the type of your data set and the way you want to manage it.
Hope this helped.
For more information, visit the Help page about the Context
thank you for this guide. It helped me a lot with my first steps with the SAP IRPA tool!
I am new to this tool and I would really appreciate if someone answer below queries:
1. What is the significance for start loop+2, and what is the criteria for initializing the row in the excel?
2. Is there a separate blog for looping logic as well such as this one?
3.What is the significance for getlastrow('A1')-2 and why we are subtracting -2?
1.I added value 2 to sc.localData.Startloop at step 5 because:
About initilizing the row in Excel file, can you be more specific to what part of the blog you are referring?
2. You can check the developer guide on Loop activities. I'll update if i find a blog about it.
3.Please read the documentation about the getLastRow() function.
It returns the first empty cell, not the last row completed(so we extract 1); we extract another one to elevate the starting point gap between excel row numbering and the value of sc.localData.Startloop.
Hope this helps.
Very nice blog!
I got 2 queries.
Could you please provide help here.
Thank you in advance,
i have implemented the code as provided but getting the following errors while implementing,can you please have a look and suggest
Error while debugging the code
Make sure that each Loop that you created contains all three components:
Attached the flow please let me know if anything is missing,getting the same error as posted in previous post.
Conditions are mentioned as shown above in the article,screenshot below for your reference.
Kindly have a look and suggest.
Be careful when renaming your objects from the loop, as they are connected based on the name. If you change the name, make sure all of them are synchronized.
thank you for this very helpful post about working with Excel.
I’m getting the ‘overstack error’ even when setting the delay at 1000 ms. I’m using the version 18.104.22.168 of the Desktop Studio. Do you know how I can solve this issue?
Thanks in advance!
HI Aleksandar, Use the debugger to indentify at which step you get the error. Add a delay before that step.
I fixed the issue. I had a sleep instead of a delay.
Thank you very much!
Thanks for your blog! This really helped a lot however may I ask one question? This is confusing to explain but hopefully I make it clear enough
Let's say I need to check in an excel file that each row has all the required information needed for that row and if all rows are valid then I can proceed with the workflow
However I've found one case that If I have 5 rows in an excel where the first and second row are valid but the third is not as it has one value missing in a cell for example in cell A4 (A4 is empty).
Then If I use the exit loop condition as shown here (.getLastRow('A4')-2) when reading the excel it will only read to the 3 row and continue on with the rest of the process i.e. not checking/reading the 4th and 5th rows?
Is there anyway to get around this problem? Apologies if i'm not clear enough but any help would be appreciated thanks!
Your post is great.
I have 2 questions:
It is convenient to work that way, eg, while we read the file, records are added to the web, this would not delay or make the process take longer.
What advantages does it have if I first store all the excel info in an array and then start the loop in the application.
I hope to be clear.
I have several vendor invoices in an excel file with two sheets “Header” and “Line Items” (one “Header” row have many rows in the “Line Items sheet”)
Based on them, I have to post in SAP FI several documents in a single bot execution, one document for one “Header” row
I need a loop for Header and another for Line Items
how can I make the connection between row 1 from sheet “Header” and the related rows from sheet “Line Items”?