Day 4 - Excel Automation and Data Manipulation

👉 Check out our full 'Africa Series - Automation Student Developers (EN)' page to register for the full program: https://bit.ly/Africa_Automation_Student_Developers

In this fourth session, we shall learn how to automate Excel-related tasks and manipulate data using UiPath Studio.

📕 Detailed agenda:
About Excel Automation and Excel Activities
About Data Manipulation and Data Conversion
About Strings and String Manipulation
💻 Extra training through UiPath Academy:
Excel Automation with the Modern Experience in Studio
Data Manipulation with Strings in Studio

👉 Register here for our upcoming Session 5/ June 25: Making Your RPA Journey Continuous and Beneficial: https://community.uipath.com/events/details/uipath-lagos-presents-session-5-making-your-automation-journey-continuous-and-beneficial/

1. Introduction to the
Excel Automation
andData Manipulation
24.06.2024Africa Series Day 4—Kickstart for Automation Student Developers

2. 2
Senior RPA Developer @SterlingBank
UiPath MVP 2024
linkedin.com/in/emmanuel-kolawole/
Senior Consultant @dipoleDIAMOND
UiPath MVP 2024
linkedin.com/in/afeararomi/
Global Manager,
Marketing Community@UiPath
linkedin.com/in/crisvidu/
Emmanuel Kolawole Afe Araromi Cristina Vidu
Meet Today’s Hosts

3. 3
Meet Today’s Speakers
Automation Project Manager @InnGen
2023 UiPath Student Dev Champion
linkedin.com/in/moazu-razak/
RPA Developer
at Boundaryless Group
linkedin.com/in/michael-patrick/
Moazu Razak Michael Patrick
RPA Developer
at dipoleDIAMOND
linkedin.com/in/joseph-osubu/
Joseph Osubu

4. 4
1.Welcome
2.Session Agenda
3.About Excel Automation and Excel Activities
4.About Datatables
5.Data Manipulation and Data Conversion
6.About Strings and String Manipulation
7.QnA
8.Close out
Today’s Agenda

5. 5
About Today’s Session
•The meetup will not last for more than 1.5 hours
•You are encouraged to network and share your LinkedIn in the chat box
•We apologize for keeping you on mute till Q&A time
•Please use the chat to place questions too between now and Q&A

6. 6
Excel Automation

7. 7
What's Excel automation?
Microsoft Excel automation is a technique used to automate repetitive or time-
consuming tasks in Microsoft Excel using software tools or coding techniques.
Excel automation can be used to automate tasks such as data entry,
calculations, and report generation. The automation of Excel can lead to
improved accuracy, efficiency, and cost savings. It can also be used to create
powerful data visualizations.
UiPath.Excel.Activities package provides two ways to interact with Excel
workbooks:
●Workbook or File Access Level
●Excel or Excel App Integration

8. 8

9. 9
Excel Automation -What can you automate?
•Create report:
•Read and write data
•Sort data
•Data Filters
•Pivot tables
•Excel formatting
•Work with ranges –append, auto fill, add/delete rows/columns, sheet
•Check reports for errors:
•Delete duplicate data
•Data reconciliation
•Fill in forms with data from Excel spreadsheets
•Extract data from emails and move to desktop/web application
•Data extraction and migration
•Data Table to Excel, Excel to Data Table

10. 10
How do I use it?
•Package: UiPath.Excel.Activities

11. 11
Data Tables
One of the most important
activities in Studio –DataTable
•Maintain Big Data in Data Table
variables
•Read from excel
•Merge data tables
•Output to excel
•Data processing in loop
•Data lookup
•Filter
•Data Sorting
•Remove duplicates

12. 12
Why are DataTables important in Excel
automation?
•Efficient handling of large amounts of data: When working with large datasets,
DataTables allow for more efficient handling of data, including sorting, filtering,
and grouping.
•Improved data accuracy: DataTables can be used to validate data and ensure
accuracy before exporting to Excel, reducing the risk of errors or mistakes.
•Better data organization: DataTables can help organize data in a structured
format, making it easier to navigate and understand.
•Integration with other systems: DataTables can be integrated with other
systems or applications, allowing for seamless data transfer and analysis
across different platforms.

13. 13
Excel Demo Run-through 1
Open Excel File Read Input File Add a New Column
Calculate Estimated
Value
End Process
Update Excel with New
sheet containing
updated record

14. 14
Excel Demo 2
•Go To https://www.rpasamples.com/opportunities
•Use Scrape Data to retrieve the Account list into a DataTable
•Use “Write Range” within Excel Application Scope
•Add a new column to the table with “Add Column Data”
•Loop through the DT and check if qty is over 40 -> add a note
•Use “Write Range” again (or write cell if it’s not too long) to update the Excel

15. 15
Data Manipulation

16. 16
Data Manipulation with Strings in Studio
▪Data manipulation is the process through which the data is altered using
various operations in order to facilitate its usage
▪Allows the user to change data from one form to another by
concatenating, converting, replacing, extracting, splitting or segregating
to make it ready to be used by RPA in workflows
Data Manipulation allows us to customize the data in order to be able to
generate the desired outcome based on the process needs

17. 17
Concat
•Concatenates the string representations of two specified objects
•String.Concat(Var1, Var2)
Contains
•Checks whether a specified substring occurs within a string.
•Returns true or false
•VarName.Contains(“Test”)
Format
•Converts an entire expression into a string (and Inserts them into another text)
•Reduces complexity and increases readability
•String.Format(“{0} is {1}”, VarName1, Varname2)
IndexOf
•Returns the zero-based index of the first occurrence of a character in a string
•Varname.Indexof(“A”)
Strings and String Manipulation
●Some of the operations that can be performed on strings are:

18. 18
Methods for String Manipulations
Join
•Concatenates the elements in a collection and displays them as string
•String.Join(“|”, CollectionVariable)
Replace
•Replaces all the occurrences of a substring in a string
•VariableName.Replace(“original”, “replaced”)
Split
•Splits a string into substrings using a given separator
•VariableName.Split(“|”c)(index)
Substring
•Extracts a substring from a string using the starting index and the length
•VariableName.Substring(StartIndex, Length)
Strings and String Manipulation

19. 19
Variables
▪Data of all kinds can be stored in variables, and there are several variable types in
Studio that can be classified into three types
Variable
TypesScalar
Characters
Booleans
Numbers
Collections
Arrays
Lists
Strings (a collection of characters)
Dictionary
Tables
Two dimensional structures that hold
data indexed by rows and columns

20. 20
Data conversion is the process of converting one type of data to another type.
The methods for data conversion include:
Simply assigning the
data value to the
desired data type.
In UiPath, explicit
assignment is
generally used for data
conversion
•Eg:
Integer.parse(“1243”)
to convert string to
number
Assign
Convert any datatype
to string using the
.ToString method.
•Eg: the user can convert
DateTime datatype to string
datatype or number to string
and vice versa
.ToString Method
Stores any kind of
data, including text,
numbers, dates, and
arrays.
Automatically
converted to other
types to perform
certain actions.
The first element in the
expression is used as
a guideline for what
operation Studio
performs
GenericValue
Variable
Data Conversion

21. 21
What is a Regular Expression?
Regular Expression (RegEx) 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. Uses of RegEx include:
●Input Validation
●String Parsing
●Data Scraping
●String Manipulation
Some business scenarios where RegEx is used include; 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." .etc

22. 22
Practice Exercise 1
Extract Email With Regex
1.Start a new project. Add a ‘Sequence’, rename it, and add an annotation.
2.Add a Read Text Fileactivity to the sequence.
◆Add “Template.txt” in the Filenameproperty field
◆Create a new variable inside the Outputproperty field and name it InputData.
3. Add a Matchesactivity the workflow.
◆Add InputDatain the Inputproperty field.
◆Create a new variable inside the Resultproperty field (use CTRL + Kto auto-assign the correct
type) and name it FoundEmailMatches.
◆Click Configure Regular Expressionbutton to configure the regular expression. Select Emailas
the value for the RegExproperty and Exactly 1as the value for the Quantifiersproperty.
◆Set the RegexOptionproperty field value to IgnoreCase.
◆Click Save.

23. 23
Practice Exercise 1
4. Add a Log Messageactivity, set its Log Levelto Infoand add the following message:
"New employee IDs will be generated for the following email addresses:".
5. Add a For Eachactivity.
◆Add FoundEmailMatchesvariable in the List of itemsproperty field.
◆Add emailAddressin the itemsproperty field.
◆Add a Log Message activity inside the Body sequence.
●Set the Log Levelto Infoand add emailAddress.Tostringin the Message property
field.
6. Run the project in Debug mode and check the OutputPanel.

24. 24
What is a list variable?
A list variable in UiPath is a type of variable that can hold a collection or sequence of data items. It
allows you to store and manage multiple values of the same data type (such as strings, numbers, or
custom objects) within a single variable Unlike arrays, it does not have a fixed number of elements.
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.
How to initialize list variables
To initialize a variable is to specify an initial value to assign to it. All variables are given an initial value
when they are declared. However, this process differ between value or reference type variables.
Data Manipulation with Lists and Dictionaries
in Studio

25. 25
a.Value type -For numeric, Boolean, DateTime and other value types, the compiler will give them a
valid value if we do not explicitly do so
b.Reference type -Reference type variables, like Lists and Dictionaries, initialize to the object we
provide. Hence, the compiler will not assign a value if we don't.
How to initialize list variables
●The first way was to create a list type of variable. Then, we used a Build Collection activity to add
items to the list and specify the list variable to hold the items.
●The second way was to create a list type of variable, then add the items inside the Default value
field. Then, initialize the list by typing new List of Type from the specified items. new List(of String)
Data Manipulation with Lists and Dictionaries
in Studio

26. 26
What is a dictionary variable?
Dictionary variables (Dictionary<TKey, TValue>) are collection type of variables of (key, value) pairs, in which
the keys are unique. Think of the Address Book in your mobile phone, where each entry has corresponding
data (name, phone number(s), email address, etc).
The data types for both keys and values have to be chosen when the variable is declared. Data types within
dictionaries can be any of the supported variables (String, Int32, etc) including Dictionary<TKey, TValue>.
Some business scenarios in which you will most likely encounter dictionaries include Storing configuration
details or other information that needs to be accessed throughout a process, Storing the job titles or other
relevant information of employees etc.
The operations that are most often associated with dictionaries are:
●Adding and deleting (key, value) pairs. VarName.Add(Key, Value) VarName.Remove(Key)
●Retrieving the value associated with a key. VarName.Item(Key)
●Re-assigning new values to existing keys. VarName.Add(Key, Value)
Data Manipulation with Lists and Dictionaries
in Studio

27. 27
What’s Next?
Global Student Developer Chapter
https://community.uipath.com/global-student-developer/
Log in to UiPath Academy academy.uipath.com
●Go to the Learning Planspage
●Enrol for the Automation Developer Associate Trainingcourse
https://bit.ly/4c8mHem
●Take the “Excel Automation with the Modern Experience in Studio” lesson
●Take the “Data Manipulation with Strings in Studio” lesson
Session 5: Making your Automation Journey Continuous and Beneficial
●Series Registration Page https://bit.ly/3Vz0G1d

28. 28
Goodbye! But Stay Close…
●[email protected]
●www.community.uipath.com
Happy Automation!