Data Table Automation in UiPath

Data tables are simply data structures that live in memory, and they can hold structured data, and if you recall from any RPA over the course, RPA thrives on structured data; mostly, they're like an Excel spreadsheet that you just can't see.

  • Data tables are almost the backbone of robotic process automation.
  • Almost every software robot you build will more than likely contain a data table.
  • They have rows that are labeled by id like one two three.
  • They also have columns that can be labeled by ids like 2:59, or they can have names in the header like first name last name email and so on.
  • We can add new rows to the data table as objects. And here, you can see that there are curly braces around several values representing a specific human being; we can access cell data by examining a specific column of a row.
  • And like I mentioned above, its name or an ID can reference that column.
  • It's often easier to access data from the cells when the columns do have names.
  • It's easiest to create a data table by reading an Excel spreadsheet or by scraping a web table.
  • It's also possible to create a data table using the build data table activity.
We Can Perform below Activities in Data Table
  • Create and Modify data table structure
  • Adding Rows to the Data Table
  • We will loop through the rows of a data table and examine some cell values.
  • Deleting a data table rows
Quick Tour on Data Table Activities
  • Create a new process called Data_Table_Example
    new-process-data-table-example
  • Once the Process opened in the UiPath Studio, Go to the Activities pane and search for word Table
  • You can see three relevant sections App Integration, Programming, and System
  • Where App Integration has an Excel table.
  • The System which has a File, Workbook, and Get Table Range
  • And, the Programming section that has this Data Table with all our data table activities
    activities-of-data-table-rpa-uipath
  • We are going to start by using the build data table, which is going to allow us to create a data table according to a specified schema.
  • And that's a graphical UI that allows us to add columns of specified data types then we'll use the Add Data Row activity to put data into our data table.
  • Then we will use For Each Row Loop activity through the rows of Data Table
  • And we will use Get Row Item to examine a particular cell from a given row
  • We will use the Remove data Row/Column to delete a Row/Column.
  • Notice we also have things like Clear a Data Table and Generate Data Table allows you to point the activity at a blob of semi unstructured data and specify what the column separators are and what the rows operators are. It will automatically detect and generate a data table for you.
  • If I type in the word range, you'll see that we have app integration excel, and if we use this Read Range Activity, we can read in an Excel spreadsheet, which will result in the creation of a data table.
  • And the same thing works down here in the more generic system file workbook read range or get table range. Both of these right ranges are going to take a data table as input as well.

Keyboard Shortcuts in UiPath Studio

Build Data Table Activity in UiPath

Open the Data_Table_Example Process in the UiPath Studio and then drag and drop the Sequence inside the designer pane.

Next, add Build Data Table activity inside the Sequence as shown below

add-build-data-activity-rpa-uipath

Create a new variable for Output Property under the Properties pane

data-table-variable-mydatatable-rpa-uipath

Next, click on the Data Table tab, which is present inside the Build Data Table Activity, and then the Build Data Table wizard opens, which can be of a resizable.

Where you will find two default columns, you can change the name of the column by right-clicking on it.
default-columns-rpa-uipath

Close these default columns and then create a new one by clicking on the + symbol in the Build Data Table Wizard. Once you click on it, you will find the below wizard.
new-column-wizard-rpa-uipath

Next, I am going to create my first column as below
creating-first-column-myfirst-column-rpa-uipath

And the second column as shown below
creating-last-column-rpa-uipath

And the Third column as below, in the third column, I am selecting the Data type as System.int16
sselect-datatype-as-int16-rpa-uipath
creating-age-column-rpa-uipath

Next, the column we need for Email address enter the column name as below and then select the Unique checkbox as below.
creating-email-address-column-rpa-uipath

So the Data Table is as shown below, you can Add Column or Delete a Column and also you can Rename the Column.
data-table-rpa-uipath

Editing DataTable Columns in UiPath:

We can go back and edit the column names and types, and not only that, you can click and drag to reorder them within the UI. Again this can only be done in more recent versions of UI path.

And like I said, if you want to edit a column, simply click on this pencil, and you can change the column name, data type, and all the other values that we had access to when we were creating the cell.
pencil-symbol-to-edit-columns-rpa-uipath

And also, it is better to edit the Data Table before entering the data.

Adding Data Table Rows:

We do have the ability to add data rows when we build a data table. Go to the Activities Pane and then search for the word Table, you will find Add Data Row activity.

Insert Add Data Row Activity inside the Sequence and then click on the Add Data Row Activity and enter the Variable name which you have created(Mydatatable) in the Build Data Table activity, in the DataTable property under Properties Pane.

add-data-row-actvitity-rpa-uipath

Before adding the row data, we want to know how many columns are there and their data types by clicking on the Data Table button inside the Build Data Table activity.

As we have Three String data type columns and one Number column in the Data table. Add Data Row activity allows us to pass either the DataRow Object or ArrayRow Object. So I am selecting ArrayRow Object and entering the array as below.
adding-row-data-rpa-uipath

Next, add one more Add Data Row activity and then click on the ArrayRow property and then add one more Row data into the Data Table.
adding-one-more-row-data-rpa-uipath

Now, if we run the Sequence, the data table will be created, and two data rows will be added to the Data table.

Loop Through the rows of a Data Table:

Now that we've created a data table and added rows to it. Now it's time to loop through those rows and examine cell values. It shouldn't come as much of a surprise to look through our data.

Next, add For Each activity inside the Sequence
for-each-row-activity-rpa-uipath

Add Mydatatable variable name in the Enter a VB expression and then enter the Log Message activity inside the Body of the For Each Row activity and enter the details as shown below.

"Current User: " + user.Item("First > which is present under Control Activity and then add Log Message activity and copy-paste the details from the Previous Log Message activity as below.

"Current User: " + user.Item("First Name").Tostring+" "+user.Item("Last Name").ToString+" "+user.Item("Age").ToString+" "+user.Item("Email address").ToString

And enter the query Mydatatable.Select("Age < 50") to print the details of the row where age is less than 50, as shown below.
for-each-activity-to-query-data-table-rpa-uipath

Deleting Data Table Row:

To delete the Rows from the Data Table first, you need to write the condition in the For Each loop. After adding Row Data to the Data table, add For Each activity inside the Sequence and write the details as shown below.
for-each-activity-to-delete-row-rpa-uipath

Next, add If condition activity inside the body of the For Each Activity and then add sequence activity inside the Then condition block as shown below.
if-condition-to-delete-row-from-data-table-rpa-uipath

Next, add Log Message activity inside the Sequence, and the write the condition as shown below in the Message property under the Properties Pane.

"Deleting Item: "+ user.Item("First Name").Tostring+" "+user.Item("Last Name").ToString+" "+user.Item("Age").ToString+" "+user.Item("Email address").ToString

And then write the condition as shown below in the Condition block
for-each-activity-with-log-message-and-condition-rpa-uipath

Next, add Remove Data Row activity inside the Sequence in the Then Block and then enter the name of the data table as a user in the Row property for Properties pane.

then-remove-data-row-rpa-uipath

Next, Save and run the Sequence, after the execution, the row data whose First Name starts from Robert will be deleted.
deleting-item-robert-rpa-uipath