DataTables and Excel Automation With Studio (UiRPA part 7)

Data Tables and Excel Automation with Studio

About the course

This course covers automating one of the most widely used business tools—Excel. You will learn the different ways to work with Excel and associated files (.xlsx, .xls, .csv) using the specific methods and tools that UiPath Studio offers. The course will also introduce a type of variable meant for working with Excel files and databases, the DataTable (System.Data.DataTable). 

What you will learn in this course

At the end of this course you will be able to:

• Create, customize and populate DataTable variables.
• Use the most common methods for DataTable manipulation.
• Differentiate between the categories of activities used when working with Excel files: Workbook activities and Excel App Integration activities.
• Use specific modern design activities to work with Excel files (to read data, write data, save files, and so on).

What are DataTables?

DataTable is the type of variable that can store data as a simple spreadsheet with rows and columns. You can identify each piece of data based on its unique column and row coordinates. Think of it as the memory representation of an Excel worksheet. 

In DataTables, individual cells can be identified by using the Column name or zero-based index and the row index. 

How are DataTables created?

The most common activities and methods to create DataTables are: 

The Build Data Table Activity: By using this activity, you choose the number of columns and the data type of each of them. Moreover, you can configure each column with specific options like allowing null values, unique values, auto-increment (for numbers), default value and length (for strings).

The Read Range Activities: This activity gets the content of a worksheet (or a selection from that worksheet) and stores it in a DataTable variable, which can be created from the Properties panel using Ctrl + K.

The Read CSV Activity: This activity captures the content of a CSV file and stores it in a DataTable variable. Although not commonly used anymore, there are still legacy or internal-built applications that work with this kind of document. 

The Data Scraping Action: This functionality of UiPath Studio enables you to extract structured data from your browser, application or document to a DataTable.

The Generate Data Table From Text Activity: Can be used to create a DataTable from structured text, by letting the user indicate the row and column separators.

Working with DataTables

UiPath offers a broad range of activities that can be used to work with DataTable variables.

Build Data Table: Is used to create a DataTable using a dedicated window. This activity allows the customization of the number of columns and type of data for each column. 

For Each Row in Data Table: Is used to perform a certain activity for each row of a DataTable (similar to a For Each loop).

Filter Data Table: Allows filtering a DataTable through a Filter Wizard, using various conditions. This activity can be configured to create a new DataTable for the output of the activity or to keep the existing one and filter (delete) the entries that do not match the filtering conditions.

Add Data Column: Adds a column to an existing DataTable variable. The input data can be of DataColumn type or the column can be added empty, by specifying the data type and configuring the options (allowing null values, requesting unique values, auto-incrementing, default value and maximum length). 

Add Data Row: Adds a new row to an existing DataTable variable. The input data can be of DataRow type or can be entered as an Array Row, by matching each object with the data type of each column. 

Clear Data Table: Clears all the data in an existing DataTable variable.

Generate Data Table From Text: Can be used to create a DataTable from structured text, by letting the user indicate the row and column separators.

Join Data Tables: Combines rows from two tables by using values common to each other using the Join Wizard, according to a Join rule that answers the question “What to do with the data that doesn’t match?”. It is one of the most useful activities in business scenarios, where working with more than one Data Table is very common.

Lookup Data Table: It is similar to vLookup in Excel. You can search for a provided value in a specified DataTable, and the RowIndex returns its value.  Or it can be configured to return the value from a cell with the given coordinates (RowIndex and Target Column). 

Merge Data Table: Is used to append a specified DataTable to the current DataTable. The operation is more simple than the Join Data Type activity, as it has 4 predefined actions to perform over the missing schema.

Output Data Table: Writes a DataTable to a string using the CSV format.

Remove Data Column: Removes a certain column from a specified DataTable. The input may consist of the column index, column name or a Data Column variable.

Remove Data Row: Removes a row from a specified DataTable. The input may consist of the row index or a Data Row variable.

Remove Duplicate Rows: Removes the duplicate rows from a specified DataTable variable, keeping only the first occurrence.

Sort Data Table: Can sort a DataTable ascending or descending based on the values in a specific column.

Get Row Item: Retrieves a value from a row in a DataTable according to a specified column.

Update Row Item: Assigns a specified value to the indicated column of a DataTable row.

Having a DataTable dt these are the easiest way to:

• Get the number of rows: dt.Rows.Count (output – int32 type)
• Get the number of columns: dt.Columns.Count (output – int32 type)
• Get the value from an ItemArray (inside For each row): Assign activity where ItemValue = Row.ItemArray(ColumnIndex).ToString (starts from 0)
• Get the specific row index (inside For each row): dt.Rows.IndexOf(CurrentRow) (Output- int32 type)
• Get a specific column index if the column name is known: dt.Columns.IndexOf(“Columnname”) OR dt.Columns.IndexOf(ColumnIndex) (Output – int32 type)
• Get only certain columns: dt = dt.DefaultView.ToTable(FALSE, “ColumnName-1”)
• Search for a record having specific text in it: dt = dt.AsEnumerable().Where(Function(a) a.Field(of String)(“yourColumnName”).ToString.Contains(“your text value”)).CopyToDatatable()
• Get the Max value of a column: int_max = dt.AsEnumerable().Max(Function(row) cint(row(“ColumnName”))) (Output – int32 type)
• Get the Min value of a column: int_min = dt.AsEnumerable().Min(Function(row) cint(row(“ColumnName”))) (Output – int32 type)
• Get Average value of a column: int_avg = dt.AsEnumerable().Average(Function(row) cint(row(“ColumnName”))) (Output – int32 type)
• Get TOP N rows: dt = dt.AsEnumerable().Top(5).CopyToDataTable() (Output – DataTable type)
• Skip first N rows: dt = dt.AsEnumerable().Skip(5).CopyToDataTable() (Output – DataTable type)
• Copy: dt_New = dt.Copy() (Output – DataTable type)
• Clone the table structure: dt_new = dt.Clone() (Output – DataTable type)
• Add a new row using the Add Data Row Activity: set the ArrayRow from the input as an array {Name, Age}

Workbooks and Common Activities

UiPath offers two types of design experience (Modern and Classic) each with two separate ways of accessing and manipulating workbooks:

• Workbook or File Access Level (works the same for both Modern and Classic).
• Excel or Excel App Integration (offers different options depending on whether you are using the Modern or Classic design experience).

Each of them comes with advantages and limitations.

Workbook – File Access Level

All workbook activities will be executed in the background.

(+) Doesn’t require Microsoft Excel to be installed, and can be faster and more reliable for some operations just by not opening the file in the Excel application.

(!) Works only for .xls and .xlsx files.
(!) Doesn’t work with .xlsm files.

(!) The file should not be open in Excel at runtime.

Excel – Excel App Integration

UiPath will open Excel just like a human would.

(+) Works with .xls, .xlsx and .xlsm, and it has some specific activities for working with .csv. All activities can be set to either be visible to the user or run in the background. The file can be opened in Excel at runtime.

(!) Microsoft Excel must be installed even when the ‘Visible’ box is unchecked. If the file isn’t open, it will be opened, saved, and closed for each activity.

Let’s start with the common activities:

Append Range: Adds the information from a DataTable to the end of a specified Excel spreadsheet. If the sheet does not exist, it creates it. 

Get Table Range: Locates and extracts the range of an Excel table from a specified spreadsheet using the table name as input. 

Read Cell: Reads the content of a given cell and stores it as a String.

Read Cell Formula: Reads the formula from a given cell and stores it as a String.

Read Column: Reads a column starting with a cell inputted by the user and stores it as an IEnumerable <object> variable.

Read Range: Reads a specified range and stores it in a DataTable. If ‘Use filter’ is checked in the Read Range activity under ‘Excel Application Scope’, it will read only the filtered data. This option does not exist for the Read Range activity under ‘Workbook’.

Read Row: Reads a row starting with a cell input from the user and stores it as an IEnumerable <object> variable.

Write Cell: Writes a value into a specified cell. If the cell contains data, the activity will overwrite it. If the sheet specified doesn’t exist, it will be created.

Write Range: Writes the data from a DataTable variable in a spreadsheet starting with the cell indicated in the StartingCell field.

The Excel Application Scope

In the Classic Design Experience, the integration with Excel is enabled by using an Excel Application Scope activity. It is a container and all the other Excel activities used to work with the specified Excel file have to be placed inside the container. When the execution ends, the specified workbook and the Excel application are closed.

The Excel Application Scope can be configured to write the output of the activities in the container in a different file.

Important note: If the same workflow deals with information from two or more Excel files, an Excel Application Scope has to be used for each file.

Workbook activities, on the other hand, do not require a scope. The Excel file needs to be indicated in the properties for each individual activity.

The Excel App Integration specific activities

CSV Activities: These activities can read from and write to CSV files, using DataTable variables. Although found under Excel App Integration, they work even if they are not placed inside an Excel Application Scope container.

• Append to CSV: add the info from a DataTable to a CSV file, creating it if it doesn’t exist. The activity does not overwrite existing data.
• Read CSV: reads all entries from a CSV file and stores them in a DataTable.
• Write CSV: overwrites a CSV with the information from a DataTable.

Range Activities: These activities can read data, insert and delete rows and columns, and even copy/paste entire ranges. They are similar to the corresponding activities under DataTable, but they work directly in the Excel file.

• Delete Column: removes a column from an Excel file based on the name.
• Insert Column: inserts a blank column in an Excel file, at a certain position.
• Insert/Delete Columns: either adds blank columns or removes existing columns, based on the specified change type.
• Read Column: Reads the values from a column beginning with the cell specified in the StartingCell property field, and stores them in an IEnumerable<Object> variable. 
• Insert/Delete Rows: either adds blank rows or removes existing rows, based on the specified change type.
• Select Range: selects a specific range in an Excel file. In general, it is paired with another activity that performs a certain manipulation over the selected data.
• Get Selected Range: outputs a given range as String.
• Delete Range: removes a specified range from an Excel file.
• Auto Fill Range: applies a given formula over a given range in an Excel file.
• Copy Paste Range: copies and pastes an entire range (values, formulas and formatting) from a source sheet to a destination sheet.
• Lookup Range: searches for a value in all the cells in a given range.
• Remove Duplicate Range: deletes all duplicate rows from a given range.
• Read Range: Reads the value of an Excel range and stores it in a DataTable variable. If the range isn’t specified, the whole spreadsheet is read. If the range is specified as a cell, the whole spreadsheet starting from that cell is read. 
• Append Range: Adds the information stored in a DataTable variable to the end of a specified Excel spreadsheet. If the sheet does not exist, a new one is created with the name indicated in the SheetName field.
• Write Range: Writes the data from a DataTable variable in a spreadsheet starting with the cell indicated in the StartingCell field. If the starting cell isn’t specified, the data is written starting from the A1 cell. If the sheet does not exist, a new one is created with the value specified in the SheetName property.

Table Activities: These activities create, filter and sort tables directly in Excel files.

• Filter Table: applies a filter on all the values from a column in a table inside an Excel file. Once the file is saved, only the rows that meet the filter will be displayed. Please note that this activity does not remove the rows that do not meet the criteria, but only hides them. A good use of this method involves using a Read Range activity after this one, with ‘Use filters’ box checked. The output will be a DataTable containing only the entries that met the given criteria.
• Sort Table: sorts a table in an Excel file based on the values in a given column.
• Create Table: it creates a table (with name) in a range specified in the Properties panel.

File Activities: These activities work directly with the Excel files, either by saving or closing them.

• Close Workbook.
• Save Workbook.

Cell Colour Activities: These activities are able to capture and modify the background colour of cells in Excel files.

• Get Cell Color: reads the background colour or a given cell in an Excel file and stores it as color variable output.
• Set Range Color: changes the background colour of all the cells in a given range. The input is a colour variable.

Sheet Activities: These activities can perform various actions over the sheets in an Excel file.

• Get Workbook Sheet: reads the name of a sheet by its index.
• Get Workbook Sheets: extracts the sheet names and stores them ordered by index.
• Copy Sheet: copies a sheet in an Excel file and pastes either in the same Excel file or in a different one specified.

Pivot Table Activities: These activities facilitate working with pivot tables in Excel files.

• Refresh Pivot Table: refreshes a pivot table in an Excel file. This is useful when pivot table source data changes, as the refresh is not automatic.
• Create Pivot Table: creates a pivot table using a specified sheet and given parameters.

Macro Activities: These activities can execute macros that where already defined in the Excel file, or can invoke macros from other files. Please note that these activities work with .xslm files.

• Execute Macro.
• Invoke VBA: macro from another file.

Modern Design Excel Activities

Use Excel File:

Lets you select an Excel file to use in the automation and enables Studio to integrate with Excel. The data in the file is available for all the activities added inside the Use Excel File.

This means that when you configure child activities, you can select data from the file directly from Studio.

– If the Excel file doesn’t exist at the time you design the automation, you can define an existing Excel file with the same structure as a template for use at design time.

– You can also add UIAutomation activities directly in the Use Excel File activity to quickly automate the interface of Excel. For this to work, you need to have the Excel file open on our machine.

Auto Fill: Fills cells with data based on data in other cells using the AutoFill feature in Excel. Similar to double-clicking the Fill Handle in Excel to copy the formula from one or more adjacent cells down to the cells in the rows with data. 

Create Pivot Table: Creates a pivot table from a specified range or table to help you calculate, summarize, and analyze data. Once added, you can add pivot table fields as rows, columns, filters, or summary fields.

 Delete Column: Deletes one or more columns from a sheet, table, or range in an Excel file.

Delete Sheet: Deletes a specified sheet from an Excel file.

Export to CSV: Exports the data in an Excel range, table, or sheet to a specified CSV file.
If the CSV file does not exist, it is created when the automation is executed.
If the file already exists, it is replaced by automation.

When the data is exported, column headers are exported as follows:

– for tables, the headers are exported if visible in Excel.
– for pivot tables, headers and the total row are exported if visible in Excel.
– for sheets and ranges, only the data visible in Excel is exported. No headers are added by default.

Filter: Creates a filter in a range, table, or sheet based on the values in a single column. Can also be used to clear existing filters.

To filter for multiple columns across a single range/sheet/table, you must use multiple Filter activities targeting the same source range.

For Each Excel Row: Iterates through each row in a given Excel file, sheet or specific range.

Format as Table: Formats a range of cells as a table with a specified name. After the table is created, you can reference it later in your automation using its name.

Get Chart: Copies an Excel chart to the clipboard for use in other activities. You can paste a chart later in the automation in a Word document using the Paste Chart/Picture into Document activity or in a PowerPoint slide using the Paste Item into Slide activity.

Insert Rows: Insert one or more rows in a table, range, or sheet at the specified location. Specify where to insert the rows, how many rows to insert, and at what position to insert the rows in the specified range.

Read Cell Value:

Copies the value from a spreadsheet cell.

Read Range: Reads the value of an Excel range as a DataTable.

Remove Duplicates: Deletes duplicate rows in a specified range.

Save Excel File As: Saves a workbook added to the project or the Project Notebook as a different file. You can specify the file name and the Excel file type to save as.

Sort Rage: Sorts the data in a specified sheet, table, or range by one or more columns.

VLookup: Finds data in a range or sheet using the VLOOKUP function.

Write Cell: Enters a text or a formula in a cell.

Write DataTable to Excel: Writes a data table that was saved for later to an Excel file.

Facts

How to define and initialise an empty List in the Variable panel: new List(of String)

…and with values: new List(of String) from {“red”, “green”, “blue”}

How to define and initialise an empty Array in the Variable panel: String elements {“red”, “green”, “blue”} or Integer elements {1,2,3,4,5}

How to define and initialise an empty Dictionary: new Dictionary(of Int32, String)

…

Two activities that you can use if you want to add data to an existing .xlsx document without overwriting existing data: Workbook Append Range, Excel Append Range.

…

Question: Can activities which require a Use Excel Application container run on a machine that does not have the Excel application installed? 

Answer: No, these activities require Excel to be installed

…

Question: What happens if you use a Write Range activity and try to write data in a .xlsx file that does not exist?

Answer: It will create that file for you and write the data in it.

…

Question: What container do you need to add to be able to use Modern Excel activities?

Answer: Use Excel File

Congratulation