Hello and welcome to UiPath Essentials Training, Data Manipulation.
In this episode we will learn some basics about working with data in RPA projects.
Hopefully, you are already familiar with control flow and variables from the previous chapter.
Data handled with UiPath comes in many flavors - or types... as we’d rather call them - and
most often it will be either text, numbers, dates and times, lists or tables.
Data of all kinds is stored in variables and used in Visual Basic expressions to configure
activities.
All activity properties have predefined types and expect expression values accordingly.
Types are visible on dedicated tooltips when hovered with the mouse.
A Type Into activity for example has properties of different types, like ContinueOnError...requiring
a Boolean value, Delays...expecting Integers...and Text...accepting a String.
Fortunately, we don’t have to know such information for all properties.
If we are using the context menu to add a new variable to a property...or pressing Ctrl+K...the
variable is automatically created with the appropriate type.
In this case, a String variable has been created.
Most of the variables used with UiPath will fit into one of the following categories:
Scalar variables...holding a single value of a fixed type, for example Characters, Booleans,
Numbers and DateTimes….
Collections... like Arrays, Lists and Queues
A particular case in this category is the String data type.
As text is basically a sequence of characters, we may understand String as an extended type
of a simple collection.
Another special case is the Dictionary structure.
Dictionaries work like two synchronized collections of associated names and values that allow
a value to be referenced by its name, rather than its index.
Dictionaries are used to pull data from Orchestrator queues.
The last category: tables
Tabels are two-dimensional structures that hold data
indexed by rows and columns.
A table is similar to an array of dictionaries, where values are accessible by row index and
column names.
The Generic Value is a special type, entirely unique to UiPath, designed to simplify the
use of basic activities.
It can represent basic types of data, including text, numbers and date/times.
Using Generic Value is convenient, as the developers don’t need to know or care about
the type of data they are dealing with.
The downside is that you lose access to the default methods that are applicable to specific
variable types.
For example, the Split method cannot be used with a generic value variable, but it will
work for the string class.
Using specific types also enables more precise expression evaluations.
Arrays and lists in UiPath are very similar entities.
The main difference is that an array has a fixed size, while the list can be lengthened
or shortened by adding or removing elements.
To define an array variable we need to pick the array data type and then specify elements’ type.
Optionally we can give it a default value as a comma separated sequence of items enclosed
in braces.
To define a list variable we’ll browse for the Generic.(dot) List type and then pick
the required content type.
To initialize the new variable to an empty list, we will set the default value to a new
List, stating again the element’s type in the declaration.
We can optionally provide some content to the new list by using an initialization array...like this.
Both lists and arrays may be iterated using a ForEach activity.
We need to select the correct item type here...
Let’s use a LogMessage activity
and run the workflow to see the elements in the array
check the Output pane and here they are.
Now we will change the loop to go through the list.
No difference from array here, but a list has appropriate methods to Add and to Remove
elements.
To add a new element in the list, we need to use the InvokeMethod activity, that requires
the list object, the method name and the new element specified in the parameters property.
Careful that element type should match the list definition.
Now if we run this again, we’ll notice that there’s a third value in the Output pane,
the one that we just added.
Dictionaries are generally useful when working with Orchestrator queues but they can also
be used to hold multiple values in a single variable, for example, to reduce the number
of arguments when passing data between workflows.
To define a dictionary, we will browse for the appropriate type...the key type and value
type need to be selected.
Usually the key is a String and the value is either a String or the more general Object.
To initialize it with values, we need to provide the Dictionary constructor with an array of
key/value pairs like this one...
For adding data to a dictionary, a simple assign will do: the key name specified to
the left and the new value to the right.
Most of the data handled in UiPath is text, also referred to as String.
Robots can receive text inputs from various sources - emails, files or via screen scraping
- and can generate text as output for applications, emails, files and log reports.
There is also a third scenario here which doesn’t fall into the input/output categories
- text which is processed internally by the robot...like in the case of selectors, names
or file paths.
To set an initial value for a String variable, we can type right here in the variables pane.
A String value is a sequence of characters, enclosed in double quotes.
If the text itself contains quotes, we need to write them twice, so they are not interpreted
as String delimiters - this is a trick known as escape sequence.
The newly created variable is now ready to be used in expressions, let’s try it with
an If.
We can build simple expressions with Visual Basic operators or we can use some of the
predefined methods of the String data type.
Let’s type “dot” after the variable name...and a list of available methods pops up.
We have some pretty useful functions here like Contains, Remove, Replace, Split, ToLower
and Trim.
For this example, let’s pick Contains.
This method returns a True or False value, depending on whether a word or phrase is found
within a text.
This is particularly useful in situations where the status of an operation is reported
by an application-generated message, with particular keywords...like success.
To simulate how a robot would react, let’s use two message boxes as action placeholders:
if the message string contains the word “success”, the robot will follow the first branch, displaying
the message “Here would be more processing”.
Otherwise, it will go with the second branch, displaying “Here we’d report an exception”.
Let’s run this.
As you can see, the robot has reported an exception as the word “success” is not
contained in the message string.
Now let’s create a new variable called status, where we’ll store the text from an application
statusbar... say “Operation completed successfully.
Record number has been created.”...
we’ll use this variable for the condition and run the process again.
This time, as you can see, the first branch gets executed.
Let’s extend our example and assume further processing is needed, based on the record
number created.
We need an Assign activity to build an expression that will help us retrieve that number.
The Split method is very useful to extract parts of a text when they are delimited by
known markers; in our case, the number comes between spaces.
The result of a Split operation is an array of String values, containing all the pieces
between the delimiters.
Using space as delimiter, our example will return an array with eight elements - the
5th one, with index 4, is the number that we are looking for.
So let’s define a variable for the result - parts - and then display the extracted number
in a message box.
Perfect.
Split offers an alternative for achieving this extraction.
In our example, the number is delimited by the words “Record” and “has”.
By pressing Ctrl plus Shift plus Space... while the cursor position is between the braces,
we can investigate more Split options.
We can use this one, taking in an array of String delimiters.
Let’s supply as argument to the Split method, an array with the words “Record ” and
“ has”.
Notice that we have included the spaces in the markers as well.
A second argument to the Split method specifies if we want empty elements in the results,
but in our case right now this option doesn’t really matter.
In this new scheme, we expect the number to be identified as the second element, which
has an index of one.
Let’s check it though...and there you have it.
While Split method allows us to extract relevant parts from a larger text, there are common
scenarios when various data needs to be assembled as text, for example for logging or when we
want to send out an email.
There are two ways of doing that: with string concatenation or using String.Format.
• To concatenate strings we can simply use the “+” sign, but if we need to mix various
types of data, like for example integers and timestamps, they must be individually converted
to a String.
All data types have a default ToString method that offers a readable textual representation.
Let’s display a message which contains a static text, followed by a String variable
and let’s also append a timestamp: we’ll use Now, a DateTime construct available in
Visual Basic, which we will convert ToString.
Let’s run this.
And there it is - as you can see, the date is displayed according to the regional settings
on our operating system.
If we want it to be in a different format, we must specify it as argument to the conversion.
There are many options and combinations available, most of them using one or more of these tokens,
as listed on the MSDN site.
If we want to show the month name and the year, we’ll simply add four M’s and four
Y’s.
Let’s see...Hello world, March 2017.
Great.
Concatenation is handy and flexible, but if texts are long or if there is much data
involved, the increasing complexity of the resulting expression may be avoided with the
use of String Format.
This method automatically converts data to text representation and it also supports custom
formatting.
Let’s see how our message would look.
First we set the frame text where data would be inserted.
This is the static part, then comes a data placeholder, followed by another.
The numbers inside braces are the positions of data arguments coming right next, but indexed
from zero: world, DateTime.Now.
We can also customize the format for the date, using the same tokens as before.
There are many String methods RPA makes use of.
They are easy to handle and understand, so for quick reference, we have compiled a shortlist
for the most commonly used:
Checking whether a text contains a specific word or phrase.
Checking whether a text starts or ends with a particular phrase.
Assembling data in textual form.
Replacing a word or phrase within a text.
Splitting a text in tokens by known delimiters.
Extracting pieces from text of known indexes and lengths.
Changing text to lower or to uppercase.
Removing leading and trailing whitespace.
Details about all the methods can be looked up
on the Microsoft Visual Basic pages for String class.
See the link in the video description.
Datatables are very important structures, extremely helpful when working with Excel
or CSV files. There’s a Datatables chapter later in this course, dedicated entirely to them.
For now though, let’s start building a short example, to get our hands around the basic data tables
concepts.
We will use this CSV file, containing tabular data, for our automation workflow.
The ReadCSV activity loads the data from the file into a DataTable variable.
Let’s call it sampleData.
We’ll make sure that the option IncludeColumnNames is checked, so that the first row is interpreted
as the column names.
To iterate through the DataTable, we’ll use a ForEachRow activity.
To print the names of people in the table we can use a WriteLine activity...and to get
a cell value from each row, we can pull from the row by the column name.
The expression looks like getting data from a row Dictionary.
Running this will output each name in the table.
Alternatively, if we want to see the value on the name column for a fixed row index,
we’d have to write an expression like this, digging into the table like in an array of
row dictionaries.
DataTable is a complex structure that has many handy methods - Select is a particularly
useful one.
This method goes through the whole DataTable looking for the rows that follow a certain
rule and returns an array with the matches.
Let’s see how it works.
We’ll use an assign activity... specifying the table
(dot) method used and as arguments, an expression that applies to the table data - let’s say
age less than forty and income greater than 40k.
Next we define a new variable to store the search results.
By default, its type is set to GenericValue - the warning indicates that we need to change
this, so we’ll choose an array of DataRows.
To iterate through the array holding the search results, we need to use a ForEach activity.
The rest - referring to the data within a column - is the same as before.
Ok, so let’s display the name, age and income using String concatenation.
Looks like Paul is the only one who passed the test.
This concludes our Essential Training on Data Manipulation.
Today you have learned about the most frequent data types and methods that you will likely
encounter in your projects.
Thanks for watching and happy automation!