SAP Intelligent RPA: How to search, read, update fields and write new rows in Excel files

[former_member632001]

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. For each row we will compare the Startdate with the current date. if it is smaller, we will update the EndDate of that row with the Current date +1 and create a new row for that entry at the end of the Excel file.

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 declare local variables;


• How to use Loops;


• How to use an If statements;


• How to compare date variables;


• How to to update a date value;


• How to copy and write rows at the end of an Excel file;

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. Declare and initialize local variables;


6. Implement a loop;


7. Read the Excel data row by row;


8. Set an If statement and update the Excel file;


9. Save the file and close it;

Prerequisites

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 SearchByDate.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:



Column StartDate and EndDate have a Date format, therefor the variables in which we will save those values will have typeof = date , in order to preserve the format.

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

We will use the row array variable to store the rows from the Excel file that will be updated.

5. Declare and initialize local variables

In the next steps we will declare and initialize three local variables that we will use later on. The local variables won't make part of the context structure, but can be used in the same way. The local variables look like this : sc.localData.NameOfMyVariable.

We want to declare following local variables:

• sc.localData.length - in which we will store the number of rows inside our file;


• sc.localData.currentDate - in which we will store the Current Date ;


• sc.localData.nextDay - in which we will store the Current Date+1;

To do this we will be using Set context activity from the System category each time. i will explain each one:

 

Initializing sc.localData.length:

We use the ctx.excel.sheet.getLastRow() the row number with the first empty cell on column 'A'. We extract 2 to eliminate the header, and the first empty row.

Initializing sc.localData.currentDate:

 

We use the new Date() to make it typeof date. When no parameters are given, the default value will be the current date.

We do the same when initializing sc.localData.nextDay in order to make it type of date, and will change its value in a separate step:

 

To change its value, we will be using the <date>.setDate() function. As parameters we use the getDate() function to get the current value of sc.localData.nextDay (which is the current date) to which we add one to obtain the next day:

 

6. Implement a loop

We have out variables declared, now we can implement the loop structure in order to iterate each row that exists in the Excel 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:

The condition based on which we will exit the loop, will be done by comparing the iteration number(sc.localData.Startloop) with the sc.localData.length value we determined at step 5.

sc.localData.Startloop is declared automatically when using Loops. Depending on the number of loops used in the workflow, the name of the iterator can differ.

 

7. Read the Excel data row by row

In the next step we will be reading each value of StartDate for every row. We will store that value in the a new local variable, named sc.localData.Startdate.

Because we will be updating only those that have a StartDate lesser than the current date, we will store the entire row only if the condition is met - this will be implemented in the next steps.

Add a Get one value activity from Excel lib category.

The row parameter will get the value of the iterator +2. Wee add 2 because the iterator starts from value 0. Also, the first row in the file is a header, and we will skip reading it.

Column parameter takes value 'D' because the Startdate is on the column D.

In the end we want to store the value of that cell in the sc.localDataStartDate.

 

8. Set an If statement and update the Excel file

To declare the If statement, in order to check if the Startdate is lesser than the current date. If this condition will be met, we will:

• save the whole row in the row array declared in the context;


• update the EndDate with the current date;


• write the saved row at the end of the excel;


• update the StartDate of the new row with the value of sc.localData.NextDay

All of the activities mentioned above must me clustered into a Sequence. Add a Sequence activity from the Flow category:

If we double-click on the Sequence we just added in the workflow, we can add the update activities.

Add an If(start) activity from the Flow - If category and define the condition in the Properties:

 

In order for the next activities, that we will add, to be executed only if the condition is met, we have to add them over the If activity, and in the end it should look like this:

To save the row, we add a Get values activity from Excel lib category.

The parameters must reprezent the location of the row we want to read:

Because we want to read only a w row, the parameter Start row and Last row will have the same value: the iteration value (sc.localData.Startloop) +2. We add 2 because the iteration starts from 0 – so we add one; the first row inside the file is the Header – so we add 1 again.

Start column will get value ‘A’, and Last column will get the last column in the file that is not empty;

Last column will get value 'E' because that is the last column completed in the table.

In the Variable we have to mention the context(defined in step 4) in which we will save the values.

 

For the next step, we will ad a Set one value activity from Excel lib category.

For the value i used the ctx.getDate()  function. As parameters for this function i used new Date() to get the current date, and ‘/’ as separator to write the current date in the format YYYY/MM/DD

 

Next, we add

Again, we are using ctx.excel.sheet.getLastRow() function to determine the first row empty in our file.

Last step in the sequence is a Set one value activity in order to update the new StartDate with sc.localData.NextDay.

 

After build, in the Scripts perspective we will have the following block of code:

	// If (true)

	if (sc.localData.StartDate < sc.localData.currentDate)

	{

		// Saves the row where Startdate < Current Date

		rootData.SearchByDate.row[sc.localData.Startloop] = ctx.excel.sheet.getFullRangeValues('A',sc.localData.Startloop+2,'E',sc.localData.Startloop+2, undefined);

		// Close Enddate with Current Date

		ctx.excel.sheet.setCell(sc.localData.Startloop+2, 'E', ctx.getDate(new Date(),'/'));

		// Add new Row at the end

		ctx.excel.sheet.setFullRangeValues('A',ctx.excel.sheet.getLastRow('A1'),rootData.SearchByDate.row[sc.localData.Startloop],false);

		// set new Startdate for the new row

		ctx.excel.sheet.setCell(ctx.excel.sheet.getLastRow('A1')-1, 'D', ctx.getDate(new Date(sc.localData.nextDay),'/'));

	}

 

All the steps inside the Loop are defined, so we can close it.

Add a Loop to the start block activity:

 

9. Save the file and close it

Because the values inside the Excel were updated, we first have to save the file before closing it.

Add Save Excel file from Excel lib category  activity to save the file.

Add Close Excel from Excel lib category  file to close the file.

Add End Excel from Excel lib category to end using the library.

Add End activity from Scenario category to mark the end of the workflow.

Conclusion

If you follow this tutorial step by step you will be able to read, update and create new data in an Excel file.

After I build and run my project, the Excel file contains following values(Excution date is '02/05/2020'):