3. UiPath - Data Manipulation - Giri

Clear Collection

Clears a specified collection of all items. One possible use is to empty a collection before starting a new phase of a process that will populate it again.

Concat

Concat Concatenates the string representations of two specified objects Expression: String.Concat (VarName1, VarName2)

Contains

Contains Checks whether a specified substring occurs within a string. Returns true or false Expression: VarName.Contains ("text")

Verify if the dates in the list have passed in the current year

Create a DateTime variable ("TmpDate") and use the 'DateTime.ParseExact' method within an 'Assign' activity to convert to DateTime: TmpDate = DateTime.ParseExact(InputDate, "dd.MM.yyyy", nothing) Use an 'If' activity to verify if the item's month is the same with the current month: TmpDate.Month = DateTime.Now.Month:If the condition is met, print a message using the 'Write Line' activity and the 'String.Format' method.If the condition is not met, add a sequence with the following activities and methods: a. Create a new variable of TimeSpan type ("TimeDifference") calculate the difference between the current month and the item's month using the 'Subtract' method in an 'Assign' activity: TimeDifference = Datetime.Now.Subtract(new DateTime(Datetime.Now.Year, TmpDate.Month, TmpDate.Day))b. Use an 'If' activity to verify if the date is in the future or in the past, by stating the condition as TimeDifference.Days > 0 and print a different message for when the month has passed or is in the future using the 'String.Format' method in a 'Write Line' activity: String.Format( "{0} days until the date {1}", Math.Abs(TimeDifference.Days).ToString, TmpDate.Day.ToString+ "." + TmpDate.Month.ToString+"."+DateTime.Now.Year.ToString

What is data manipulation?

Data manipulation is the process of modifying, structuring, formatting, or sorting through data in order to facilitate its usage and increase its management capabilities

Join

Join Concatenates the elements in a collection and displays them as String Expression: String.Join("|", CollVarName1)

Initialization

Just like in the example of Lists, Dictionaries have to be initialized. In the following example, the initialization is done inside an 'Assign' activity. However, as you may remember from the Lists chapter, it can be done from the Variables Panel.

String Methods

String manipulation is done by using String Methods borrowed from VB.Net. Below are some of the most common methods used in RPA:

Strings

Strings are the data type corresponding to text. It is difficult to imagine an automation scenario that doesn't involve the use of strings. Anytime a text needs to be captured, processed, sent between applications, or displayed, string comes in handy (unless the data is structured - like a table).

Substring

Substring Extracts a substring from a string using the starting index and the length Expression: VarName1.Substring(startIndex, length)

Adding

VarName.Add(Key, Value) - adds an item to an existing Dictionary. Because Add does not return a value, use the Invoke Code activity.

Split

Split Splits a string into substrings using a given separator Expression: VarName.Split("|"c)(index)

Add to Collection

Adds an item to a specified collection. It is equivalent to List.Add(). It can be used, for example, to add a new name to a list of company names.

What are some business scenarios in which I will use data manipulation?

Data manipulation methods are frequently employed by website owners to extract and view specific information from their web server logs. That allows for watching their most popular pages, as well as their traffic sources. Consider the process of interrogating public financial or legal databases. Data manipulation provides the means for the credit analysts to extract only the relevant data and use it in other documents or correlate it with information from other sources.

Dictionaries

Dictionaries (or Dictionary<TKey, TValue>, as you will encounter them) are collections of (key, value) pairs, in which the keys are unique. Think of the Address Book in your mobile phone, where each name has corresponding data (phone number(s), email). The data types for both keys and values have to be chosen when the variable is instantiated. Data types in Dictionaries can be any of the supported variables (including Dictionaries, for example). The operations that are most often associated with Dictionaries are: Adding and deleting (key, value) pairs Retrieving the value associated with a key Re-assigning new values to existing keys

Format

Format Converts the value of objects to strings (and inserts them into another text) Expression: String.Format("{0} is {1}", VarName1, VarName2)

What are some business scenarios in which you will most likely encounter strings?

Getting the status of an operation Extracting the relevant piece from a larger text portion Displaying information to the human user

IndexOf

IndexOf IndexOf Returns the zero-based index of the first occurrence of a character in a String Expression: VarName1.IndexOf("a")

Exists in Collection

Indicates whether a given item is present in a given collection by outputting a Boolean as the result. We can use this activity to check whether a list of clients contains a specific name.

Methods for Working with Dictionaries

Initialization Adding Removing Retrieving

IsMatch

IsMatch Indicates whether the specified regular expression finds a match in the specified input string.

Lists

Lists (or List<T>, as you will encounter them) are data structures consisting of objects of the same data type (for example string or integer). Each object has a fixed position in the list; thus, it can be accessed by index. While arrays are fixed-size structures for storing multiple objects, lists allow us to add, insert and remove items. Lists can store large numbers of elements - names, numbers, time coordinates and many others. Lists provide specific methods of manipulation, such as: Adding and removing items Searching for an element Looping through the items (and performing certain actions on each) Sorting the objects Extracting items and converting them to other data types.

UiPath Methods applicable to Collections

Manipulating lists can be done using .NET methods or using the collection methods that UiPath Studio offers:

Methods in UiPath that use the RegEx builder:

Matches IsMatch Replace

Matches

Matches Searches an input string for all occurrences and returns all the successful matches.

RegEx Builder

Regular Expression (REGEX, or regexp) is a specific search pattern that can be used to easily match, locate and manage text. However, creating RegEx expressions may be challenging. UiPath Studio contains a RegEx builder that simplifies the creation of regular expressions. Typical uses of RegEx include: Input validation String parsing Data scraping String manipulation

Remove from Collection

Removes an item from a specified collection and can output a Boolean variable that confirms the success of the removal operation. This activity can be used, for example, to remove an invoice number from a list of invoices to be processed.

Replace

Replace Replaces all the occurrences of a substring in a string Expression: VarName.Replace ("original", "replaced")

Replace-1

Replaces strings that match a regular expression pattern with a specified replacement string.

What are some business scenarios in which I will use RegEx?

Retrieving pieces of text that follow a certain pattern, for example: extracting phone numbers that start with a certain digit; collecting all the street names from a bulk text, even if they don't follow a specific pattern - some of them contain "Street", others "Rd.", and so on. It would take much longer to build the same expression using the regular String methods - for example, RegEx has a predefined expression to locate all the URLs in a string.

Validate the user's string input

Start the project as a flowchart (given the expected high number of decision points) and inform the user about the rules via a 'Message Box'. Present an 'Input Dialog' to the user. Check if the user typed anything in the input field. If no text is provided, then return to the 'Input Dialog' stage. Validate the input text using RegEx ('Is Match' activity) - 'Advanced' type, ^([A-Z][\w]{7,20})*$ Use an Int32 variable to count how many times the user had failed input attempts (not taking empty input into account). Use a decision node to check if the user reached 3 failed attempts. If the user reached a maximum of 3 failed attempts then write to output the following message using a 'Write Line' activity: "Maximum number of retries exceeded" and end execution. If the RegEx validation passed, then print the following message to output: "Your text follows the rules!" and end execution. You can use a variable to store the text presented to the user.

Calculate and print the number of victories of each Tour de France winner

Start the project as a sequence and create a new Dictionary with types (String, Int32) Loop through the values in the first Dictionary with a 'For Each' ("ForEach WinnerNames in FirstDictionaryName.Values") and check if the value is already present as key in the new Dictionary using the '.ContainsKey' method inside an 'If' activity ("SecondDictionaryName.ContainsKey(WinnerName)"): if the condition is met, use an 'Assign' activity to increase the value of the corresponding key: WinnerCounts(WinnerName) = WinnerCounts(WinnerName) + 1 if the condition is not met, add the key to the second Dictionary with value "1" using an 'Invoke Method' activity with the TargetObject the second Dictionary name and 'Add' as MethodName Loop through the second Dictionary using 'For Each' ("ForEach winner in SecondDictionaryName") and use an 'If' to write a differentiated text for those with 1 win vs. those with more wins using 'Write Line'.

Sort the List and Print 3 Values

Start the project as a sequence and define a List of Strings variable ("countries") with the following default value: new List(of String) from {"Germany", "Spain", "Japan", "Brazil", "India", "China"}. Add an 'Invoke Method' activity and write 'Sort' under MethodName and "countries" under TargetObject. Leave the TargetType unchanged (null). Add an 'Invoke Method' activity. Write 'Reverse' under MethodName and have the other 2 fields just like in the previous 'Invoke Method' Print the extracted values using a 'Write Line' activity and the 'String.Join' and the 'GetRange' methods: String.Join (", ", countries.GetRange(0,3)). Add one more 'Invoke Method' activity. Type 'Reverse' in the MethodName field. In the TargetObject field, type the name of the original variable. Leave the TargetType unchanged (null). Create a new List of String variable. Add an 'Assign' activity to the Designer panel. In the To field, enter the variable created at step 11. In the Value field, enter the name of the original variable, followed by .GetRange(0,3). This variable stores the first three values in the sorted list. Print the extracted values using a 'Write Line' activity. Use the 'String.Join' method: String.Join (", ", secondvariablename.ToArray).

Calculate the combined weight of the packages sent to one city

Start the project as a sequence. Loop through the values of the initial Dictionary using 'For Each' and (in the Body) loop through the keys of the second dictionary (cities) with 'For Each' and check if the city is already in the Dictionary (using 'If' and 'Contains' method): if the condition is met, add the weight stored in the corresponding value to the existing value using an 'Assign' activity; if the condition is not met, add the city in the dictionary with the corresponding value using the 'Invoke Method' activity and 'Add' as MethodName 3. Use an 'Input Dialog' activity to display all the cities by using SecondVariableName.Keys.ToArray under 'Options' in the activity properties a. If the user chooses a city, use 'Write Line' and the 'String.Format' method to print the combined weight. b. If the user doesn't choose a city, use 'Write Line' to display a generic message.

Extract the email address from a string (without using RegEx)

Start the project as sequence and create 2 variables (a String and an Int32). Use 2 'Assign' activities - the first to locate the "@" character, and the second to store its index (by using the 'IndexOf' method: IndexTextToFind = VariableName.IndexOf("@")). Extract all the text before and after "@" and store each in variables using 'Assign' activities, like EmailAddress_Part1 and EmailAddress_Part2. Keep only the text portion from the last space character from EmailAddress_Part1 to the end and save it into the EmailAddress_Part1variable, using the 'LastIndexOf' method in an 'Assign' activity: EmailAddress_Part1.Substring(EmailAddress_Part1.LastIndexOf(" ")). Extract all the text from EmailAddress_Part2 to the first space character (use an 'If' to check if the space exists; if it doesn't, consider all the text as part of the email). Print to output the email address using a 'Write Line' activity: i.e. "The email address is [email protected]."

Get input information and use it to fill in the placeholders in a string

Start the project as sequence and create 3 string variables to store the first name, last name and the day of the week, respectively. Use 3 'Input Dialog' activities to get the input from the user Use the 'Replace' method in 'Assign' activities for each of the 3 strings with input data: VariableName = VariableName.Replace("<parameter>",StringVariable.Trim). In order to preserve the formatting, use the 'Trim' method after each string variable to cut any unwanted spaces. Print the output using a 'Write Line' activity.

Extract the email address from a string (using RegEx)

Start the project as sequence. Use the 'Matches' activity with the RegEx tailored for email addresses and use an IEnumerable variable to store them. Use a 'For Each' activity to loop through the values in the created variable and use the 'Write Line' activity and the 'Value' method to display the values.

What are some business scenarios in which you will most likely encounter dictionaries?

Storing configuration details or other information that needs to be accessed throughout a process Storing the job titles or other relevant information of employees Storing the bank accounts of suppliers

What are some business scenarios in which you will most likely encounter lists?

Storing the computer names of the members of a project team for a certain configuration that needs to be made Collecting and storing the numbers of invoices that meet certain criteria Keeping track of the ticket numbers created in a certain period on a certain issue;

Retrieving

VarName.Item(Key) - returns the Dictionary item by its key VarName.Count - returns an Int32 value of the number of Dictionary items VarName.ContainsKey(Key) - checks if the item with the given key exists in the Dictionary and returns a Boolean result VarName.TryGetValue(Key, Value) - checks if an item with a given key exists in the Dictionary and returns a Boolean result and the value if found

Removing

VarName.Remove(Key) - removes an item from the Dictionary. It can be used in an 'Assign' activity.

List Demo

We started the project as a Sequence and created 2 List variables ("SpainCities" and "UKCities"). Since Collection variables have to be instantiated and populated with values, we used 2 different methods:For "SpainCities", we instantiated and populated the List from the Variables Panel, using the expression new List (of String) from {"MADRID","valencia", "BARCELONA"};For "UKCities", we instantiated the List from the Variables Panel using the expression new List (of String) and populated it using 'Add to Collection' activities. We merged the 2 List variables in a newly created List variable ("AllCities"), using the Enumerable.Concat method inside an Assign activity. We've used the expression Enumerable.Concat(SpainCities.AsEnumerable, UKCities.AsEnumerable).ToList. .AsEnumerable is used to convert the 2 Lists to Enumerable data type, and then .ToList converts the outcome to List. We used an Invoke Method to sort the elements in the outcome List by selecting Sort in the MethodName field. We used a For Each activity to go through the elements in the "AllCities" variable and:Convert them to ProperCase using the StrConv method: StrConv(item, VbStrConv.ProperCase);Add the converted items to a newly created List variable ("AllCitiesProperCase") using Add to Collection. We used a Write Line activity to display the converted values using the String.Join method: String.Join(",",AllCitiesProperCase).

Demo Dictionary

We started the project as a Sequence and created a new Dictionary (String, String) to store the names and the birthdates (named "birthDates"); We used 3 Assign activities to add elements to the Dictionary variable: birthDates("key") = "value". The keys were names and the values were dates (dd/mm/yyyy); We used a For Each activity to loop through the keys of the Dictionaries and:Convert each value to date using the DateTime.Parse(birthDates(item)) method and assign it to a newly created DateTime variable ("dateCurrent");Determine the current year anniversary by using the expression new DateTime(DateTime.Today.Year, dateCurrent.Month, dateCurrent.Day) and store it in a newly created DateTime variable ("curentYearAnniversary");Determine the current year anniversary weekday by using the expression currentYearAnniversary.DayOfWeek.ToString and store it a newly created String variable ("birthdayWeekday");Use an If activity to verify whether the current year anniversary has passed through the condition "currentYearAnniversary < DateTime.Today":If True, use a Log Message activity to print that the anniversary of that person passed and the weekday in which it was;If False, use a Log Message activity to print that the anniversary of that person will be in the future and the weekday in which it will be.

Demo Dictionary2

We started the project as a Sequence and instantiated the newly created Dictionary variable by using an Assign activity and the following expression: cities = new Dictionary(of String, List(Of String)) We used 2 Assign activities to populate the Dictionary, with the name of a country as key, and several cities from those countries as values; We used an Add to Collection activity to add a new city name ("Portland") in the value corresponding to the "US" key, by writing the name of the city in the Item field in the Properties panel; We used a Remove from Collection activity to remove a city name ("London") from the corresponding value List. To do so, "London" was inserted in the Item field in the Properties panel; We used 2 Log Message activities to display the values corresponding to the 2 keys in the Dictionary.

Demo Regex

We started the project as a Sequence and used an Assign for a newly created String variable. We used the Matches activity to identify the street names and numbers by:Setting the Type as AdvancedAdding the "road", "drive", "avenue" and so on in the Value field;Using the Exactly quantifier with the value 1;Making sure the IgnoreCase box was checked. We used a For Each activity to loop through the output variable of the Matches activity and print it using a Log Message activity.

String Demo

We started the project as a Sequence and used an Assign for the initial value of the "message" String variable: "You searched for author Mark Twain. His books can be found in the following stores: Bookland, Classics bookstore." We created a new String variable ("author") and used a succession of String methods to assign the author from the query: message.Split("."c).First.ToString.Substring(message.LastIndexOf("author")):v Split("."c).First.ToString extracts the first sentence of the String and converts it to a String;v Substring(message.LastIndexOf("author")) extracts the Substring that is after "author"; We created a new List variable ("bookstores") and used a succession of String methods to assign the bookstores from the query: message.Split("."c)(1).ToString.Split(":"c).Last.ToString.Split(","c).ToListv message.Split("."c)(1).ToString extracts the second sentence of the String and converts it to a String;v Split(":"c).Last.ToString splits the remaining String and keeps only the last part of it;v Split(","c).ToList takes each string separated by comma and adds it as an element in the List variable; We created a new String variable to display the values of the 2 variables: String.Format("Availability for {0}: {1}", author ,String.Join(";"+vbCr,bookstores)). In this expression, String.Join is used to extract each element in the "bookstores" List variable and display them. vbCr is used to go on a new row for each element (similar to Environment.NewLine)