How to design a sample process that includes web applications with Robusta RPA tool step by step?

You can connect to Excel from ROBUSTA RPA, add new columns and rows and use Excel operations. This tutorial will walk you through designing a sample RPA process with Excel operations, step by step.

PROCESS SCENARIO

In this tutorial:

Open an Excel file and check whether there is any flight routes data in it.

If there is, add two columns, one for calculating the number of days between departure and return dates, and one for combining these dates.

Then, continue with a repeating sub-process since you want to perform operations for each row in the Excel file.

In this sub-process, you will set the values for the newly added columns.

STEP BY STEP PROCESS

CONNECT TO EXCEL AND CREATE A DATASET

1. Start the process by using the Open activity under the Excel section to open the Excel file that contains the flight routes data.

2. Just drag and drop the activities you want to use into the design area then enter the relevant parameters.

Excel > Open

Name	Open: Flight_Routes Excel
*Excel name	Flight_Routes
*Excel file name	Current directory where the file is located

Excel > Read excel to dataset

Name	Read excel to dataset
*Excel name	${Flight_Routes}
*New dataset name	Flight_RoutesDS
Sheet name	Routes
Has header	Checked

5. In the next step, get the row count of the dataset with the Get size activity.

6. After selecting the dataset from the list in the Dataset name field, choose the Row option in the Size Type field because you want the number of rows to be counted.

7. Then, in the Result variable name field, define the variable that you want to assign the row count value as ‘getSize’.

Dataset > Get size

Name	Get size: Flight Routes
*Dataset name	${Flight_RoutesDS}
Size type	ROW
*Result variable name	getSize

8. You have to ensure that the process needs to terminate if any row is not found in the excel file. To achieve this, add an Exclusive gateway activity that allows us to create alternative flows in our process.

9. First flow condition ( ${getSize==0} ) checks whether the getSize variable value is equal to ‘true’ or not, which means there is no row in the file.

The first flow condition

Flow condition

( ${getSize==0} )

10. If this condition is met, the process is completed with an end event.

11. In the second flow condition, choose the default flow option and do not set any condition expression.

The second flow condition

Default flow

Checked

ADD COLUMNS

12. Continue your process by adding two columns to the dataset with the Add column activity.

13. The name of first column is “Days Between”. The name of second column is “Concatenated Days”.

14. “Days Between” column is the difference of the two date columns, “D.date” and “R.date”. “Concatenated Days” column is concatenate these two columns.

Dataset > Add column

Name	Add column: Days Between
*Dataset name	${Flight_RouteDS}
*Column name	Days Between

Dataset > Add column

Name	Add column: Concatanated Dates
*Dataset name	${Flight_RouteDS}
*Column name	Concatanated Dates

PROCESS ROWS IN SUB-PROCESS.

15.The next activity is Sub-Process that is used here because of recurring sub-process. We want to process each and every row in the dataset one by one.

16. We need to define how many times this sub-process will be repeated in the Cardinality field. This will be provided by using “getSize” variable.

17. Choose the value of the Multi-instance type field as Sequential because you want each operation to be done sequentially in our loop. The “loopCounter” variable is automatically defined at the beginning of the loop.

INFO – loopCounter, which first takes a value of 0 increases by 1 at each iteration. When the value of the “loopCounter” variable reaches the Cardinality value, the loop is automatically terminated.

Structural > Sub process

Name	Loop for each flight route
Cardinality (Multi-instance)	${getSize}
Multi-instance type	Sequential
Exclusive	True

18. In the sub-process, obtain the flight return and departure date from the dataset with the Get action activity. Set the column header that we want to get data from in the Column field.

19. Since you want to repeat the same operation for all the rows, set ‘loopCounter’ variable in the Row field.

20. The index value of rows and columns in the dataset starts with 0, the same as the loopCounter variable.

Dataset > Get action

Name	Get action: Return Date
*Dataset name	${Flight_RouteDS}
*Column	R.Date
Result variable name	${loopCounter}
Row	returnDateStr

Dataset > Get action

Name	Get action: Depart Date
*Dataset name	${Flight_RouteDS}
*Column	D.Date
Result variable name	${loopCounter}
Row	departureDateStr

21.Then, use the Set action activity to set an excel formula to calculate the difference of the dates.

22. Then we set to the newly created ‘Days Between’ column. In this activity, after selecting the dataset, we set the column header in the Column field and ‘loopCounter’ variable in the Row field.

23. In the Value field, write the formula( =H${loopCounter+2}-G${loopCounter+2} ) to calculate the difference between the dates by using the equal sign at the beginning.

24. The dates you want to process start from the second row of the H and G columns in the table, the row value in the formula should also start from the 2nd row. So, in the formula syntax, add 2 to the loopCounter variable.

Dataset > Set action

Name	Set action: Days Between
Dataset name	${Flight_RouteDS}
*Type	STRING
*Column	Days Between
*Value	=H${loopcounter+2}-G${loopcounter+2}
Row	${loopCounter}

25. In this step, combine the dates and set the value in the newly added column named ‘Concatenated Dates’. Write the variables that hold the date values in the Value field with a hyphen between them.

26. Complete the loop activity with the End event, which allows us to end the process flow.

Dataset > Set action

Name	Set action: Concatanated Dates
Dataset name	${Flight_RouteDS}
*Type	STRING
*Column	Concatanated Dates
*Value	${departureDateStr}-${returnDateStr}
Row	${loopCounter}

FINISH YOUR EXCEL OPERATION

27. Use Write dataset to excel activity after you finish the operations in the subprocess, which allows you to transfer all the data in a dataset to an Excel file.

28. In this activity, after selecting the dataset and the Excel file, choose the Include header option because you want to copy the header information for newly added columns.

Excel > Write dataset to excel

Name	Write dataset to excel
*Dataset name	${Flight_RoutesDS}
*Shift	NONE
*Excel name	${Flight_Routes}
*Value	=${departureDateStr}-${returnDateStr}
Include header	True
Sheet name	Routes

29.Save the Excel file in the output folder with the Save and close activity. For this, enter the directory, file name and extension of where you want to save our file in the Excel file name field.

30. In the Action field, select the Save and close option from the list and complete your process.

Excel > Save and close

Name	Save and close: Flight_Routes Excel
*Excel name	${Flight_Routes}
*Action	SAVE_AND_CLOSE
Excel file name	Current directory where the file is located