Man illustrationFlower illustration

Docy

A Sample RPA Process with Excel Operations

Estimated reading: 7 minutes 149 views

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

NameOpen: Flight_Routes Excel
*Excel nameFlight_Routes
*Excel file nameCurrent directory where the file is located
In the Excel name field, special character constraints is important, only letters and numbers are recommended.

3. Using the Read excel to dataset activity, transfer the data in the Excel file to a dataset.

4. Check the Has header box because our data has a header row. By doing so, it will be possible to specify the column header values in the Column fields in other activities at which you get or set cell values.

INFO – If there is no header row in the table, you should not select the Has header option.

INFO – If Sheet name field is left blank, the first page will be transferred to the dataset.

Excel > Read excel to dataset

NameRead excel to dataset
*Excel name${Flight_Routes}
*New dataset nameFlight_RoutesDS
Sheet nameRoutes
Has headerChecked

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

NameGet size: Flight Routes
*Dataset name${Flight_RoutesDS}
Size typeROW
*Result variable namegetSize

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 flowChecked

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

NameAdd column: Days Between
*Dataset name${Flight_RouteDS}
*Column nameDays Between

Dataset > Add column

NameAdd column: Concatanated Dates
*Dataset name${Flight_RouteDS}
*Column nameConcatanated 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

NameLoop for each flight route
Cardinality (Multi-instance)${getSize}
Multi-instance typeSequential
ExclusiveTrue

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

NameGet action: Return Date
*Dataset name${Flight_RouteDS}
*ColumnR.Date
Result variable name${loopCounter}
RowreturnDateStr

Dataset > Get action

NameGet action: Depart Date
*Dataset name${Flight_RouteDS}
*ColumnD.Date
Result variable name${loopCounter}
RowdepartureDateStr

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

NameSet action: Days Between
Dataset name${Flight_RouteDS}
*TypeSTRING
*ColumnDays 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

NameSet action: Concatanated Dates
Dataset name${Flight_RouteDS}
*TypeSTRING
*ColumnConcatanated 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

NameWrite dataset to excel
*Dataset name${Flight_RoutesDS}
*ShiftNONE
*Excel name${Flight_Routes}
*Value=${departureDateStr}-${returnDateStr}
Include headerTrue
Sheet nameRoutes

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

NameSave and close: Flight_Routes Excel
*Excel name${Flight_Routes}
*ActionSAVE_AND_CLOSE
Excel file nameCurrent directory where the file is located
CONTENTS