UiPath

Read, output, search, write, and delete data tables in UiPath

In UiPath Studio development, we often work with data tables.

However, manipulating data tables is not intuitive and in some cases it is difficult to know how to use an activity.

This article explains how to read, output, search, write, and delete data tables.

This site was created by translating a blog created in Japanese into English using the DeepL translation.

Please forgive me if some of the English text is a little strange

 

Loading the data table

There are three main ways to load a data table.

  1. Create and load on Workflow
  2. Import CSV file
  3. Load an Excel file
sea otter-kun
sea otter-kun
I will explain them in order, but you can skip [(1) Create and load on Workflow] as it is not used very often and is just for reference.

 

Load data tables on the workflow

Use the “Build Data Table” activity to load the data table created in the workflow.

The Build Data Table can be found in Programming > DataTable.

 

Build Data Table  Setteing item

Setting location Setteing item Setteing contents
Body
Create a Datatable… Clicking the DataTable button in the activity body opens the Build Data Table window, which enables you to customize the table to be created.
Properties Common DisplayName The display name of the activity.
Output DataTable A DataTable variable that stores the information added in the Build Data Table window.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

 

Sample Process
Set table data on the workflow and output it as a data table, and output the data table as text to the log.

・Click “DataTable…” in the “Build Data Table” activity. in the “Build Data Table” activity.

sea otter-kun
sea otter-kun
The column name and the type of the column name can be edited using the pencil mark at the bottom right of the header.

 

・Build Data Table  Properties

・Output Data Table  Properties

penguin-san
penguin-san
“Output Data Table” is an activity that converts a data table into text.

 

・Variables

・Execution result

 

Read CSV

To read a CSV file into the data table, use the “Read CSV” activity.

“Read CSV” is located in [App Integration > CSV].

 

Read CSV  Setting item

Setting location Setting item Setting contents
Body
FilePath Full path of the CSV file.
DataTable  Represents the output DataTable that contains the information obtained from the CSV file.
Has headers Specifies if the first row in the CSV file should be considered a header row.
Properties Common DisplayName The display name of the activity.
File FilePath Full path of the CSV file.
Output DataTable  Represents the output DataTable that contains the information obtained from the CSV file.
Options Delimiter Specifies the delimiter in the CSV file: Tab, Comma(‘,’), Semicolon(‘;’), Caret(‘^’), or Pipe(‘|’).
Encoding The encoding type to be used. You can find the complete list of codes for each character encoding here.
Has headers Specifies if the first row in the CSV file should be considered a header row.
IgnoreQuotes Specifies if quotes should be ignored when reading the file.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.。

 

Sample Process
Outputs a CSV file to a data table by reading it, and outputs the data table to the log as text.

・Read CSV  Properties

 

・Output Data Table  Properties

・Variables

・CSV file to be read

・Execution result

 

Import an Excel file

To read an Excel file into a data table, use the “Read Range” activity.

“Read Range” is located under App Integration > Excel.

 

Read Range  Stteing item

Setting location Setting item Setting contents
Body
SheetName The name of the sheet in which the range that you want to read is.
Range The range that you want to read.
Properties Common DisplayName The display name of the activity.
Input SheetName The name of the sheet in which the range that you want to read is.
Range The range that you want to read.
Options AddHeaders  When selected, the column headers from the specified spreadsheet range are also extracted.
PreserveFormat
(slower)
Selecting this check box keeps the formatting of the range that you want to read.
UseFilter When selected, the activity does not read what has been filtered out of the specified range.
Output DataTable Stores the data extracted from the specified Excel range in a DataTable variable.
Misc Private  If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Outputs the specified sheet of an Excel file to a data table by reading it, and outputs the data table to the log.

 

・Read Range  Properties

・Output Data Table  Properties

・Variables

・Excel file to be loaded

・Execution result

 

Datatable output

Text output

Use the “Output Data Table” activity to output the data table together with the text.

The “Output Data Table” is located in [Programming > DataTable].

 

Output Data Table  Setting item

Setteing location Setting item Setting contents
Propeties Common DisplayName The display name of the activity.
Input DataTable The DataTable object to be written into a string.
Output Text The output of the DataTable as a string.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Convert the data table to text data and output the log.

・Read CSV Properties

・Output Data Table  Properties

・CSV file to be read

・Execution result

sea otter-kun
sea otter-kun
“Output Data Table” is mainly used for debugging and log output.

 

Output by specifying the rows and columns of the data table.

For output specifying the rows and columns of the data table, put (row number)(column number or column name) after the data table variable.

The row and column numbers start at 0, so the first row will have index 0, and the second row will have index 1.

 

Sample Process
Output data to the log, specified by the row number and column number or column name of the data table.

 

・Code for copy and paste

dtTest(0)(0)
dtTest(0)(1)
dtTest(1)("Name")
dtTest(1)("Score")

 

 

・Read CSV Properties

・Variables

・CSV file to be read

・Execution result

 

Output line by line

To output the data table row by row, use “For Each Row in Data Table” and specify the (column number or column name) after the row data.

The “For Each Row in Data Table” activity can be found in [Workflow > Control].

 

For Each Row in Data Table  Setting item

Setting location Setting item Setting Contents
Body
ForEach Enter a name to be used when referring to the current iteration. The default is row.
DataTable  The DataTable variable for which an action is to be executed once for each row.
Properties Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Input DataTable  The DataTable variable for which an action is to be executed once for each row.
Common DisplayName The display name of the activity.
Output Index A zero-based index that specifies what element of the current collection is being iterated, stored in an Int32 variable.

 

Sample Process
Extract the data table line by line and output to the log specifying the column number or column name.

・Code for copy and paste

"Column Index 0: "+row(0).ToString
"Column Index 1: "+row(1).ToString
"Column Name: "+row("Name").ToString
"Column Score: "+row("Score").ToString

 

・Read CSV  Properties

・For Each Row in Data Table  Properties

・Variables

・CSV file to be read

・Execution result

 

Searching the Data Table

Search the data table and output the specified column data and index number.

Use the “Lookup Data Table” to search for data in a data table and output the data and indexes of the matching rows.

The “Lookup Data Table” can be found in [Programming > DataTable].

 

Lookup Data Table  Setting item

Setting location Setting item Setting contents
Properties Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Input DataTable The DataTable variable in which you want to perform the lookup.
LookupValue The value you want to search for in the specified DataTable variable.
Lookup Colum Column  The variable containing the column you want to search for.
ColumnIndex The index of the column you want to search for.
ColumnName The name of the column you want to search for.
Common DisplayName  The display name of the activity.
Output CellValue Optional. The value found in the cell that has the Row coordinates specified in the RowIndex property and the Column coordinates specified in the Target Column property category.
RowIndex  The Row index of the found cell, stored in an Int32 variable.
Target Column Column Specifying a DataColumn variable here returns the value of the cell found at the coordinates between this column and the value from the RowIndex property.
ColumnIndex Specifying a Column index here returns the value of the cell found at the coordinates between this column and the value from the RowIndex property.
ColumnName Specifying a Column name here returns the value of the cell found at the coordinates between this column and the value from the RowIndex property.

* Only one item can be specified for each of Target Column and Lookup Column.

 

Sample Process
Searches the data table and outputs the data of the specified column name and index number to the log.

・Code for copy and paste

strSearch+" Score:"+objOutput.ToString
strSearch+" index: "+intIndex.ToString

 

 

・Read CSV  Properties

・Lookup Data Table  Properties

 

penguin-san
penguin-san
  • The ColumnName of the Target Column specifies the column name to be output.
  • The input DataTable specifies the data table to be searched.
  • The input Lookup Column specifies the string to be searched.
  • The output CellValues will be of type Object.
  • The RowIndex of the output will be of type int.
  • The ColumnName of the Target Column specifies the name of the column to be searched.

 

・Variables

・CSV file to be read

・Execution result

 

Updating the data table

Add a row to the data table

To add rows to the data table, use the “Add Data Row” activity.

The “Add Data Row” can be found in [Programming > DataTable].

 

Add Data Row  Setting item

設定場所 設定項目 設定内容
Properties Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Common DisplayName The display name of the activity.
Input DataTable The DataTable object to which the row is to be added.
ArrayRow An array of objects to be added to the DataTable.
DataRow The DataRow object to be added to the DataTable.

 

Sample Process
Add row data and arrays to the data table, and output the data table as text data.

・rowTest AddDatRow  Properties

・rowTest AddArrayRow  Properties

・Lookup Data Table  Properties

・CSV file to be read

・Execution result

 

Delete a specific row from a data table

To remove a specific row from the data table, use “Remove Data Row”.

The “Remove Data Row” can be found in [Programming > DataTable].

 

Remove Data Row  Setting item

設定場所 設定項目 設定内容
Properties Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Input DataTable The DataTable object from which the row is removed.
Row  The DataRow object which is to be removed.
RowIndex The index of the row to be removed.
Common DisplayName The display name of the activity.

 

Sample Process 1
Delete a specific row in the data table by specifying the index number.

・Remove Data Row

・Output Data Table

・CSV file to be read

 

・Variables

・Execution result

 

Sample Process 2
Deletes a specific row in the data table by specifying the row data.

・Remove Data Row  Properties

・Output Data Table

・CSV file to be read

・Execution result

 

Modify the specified data in the data table

Writing the specified data to the data table is done by assigning the modified data to the DataTable (row number) (column number or column name).

 

Sample Process

・Output Data Table  Properties

・Variables

・CSV file to be read

 

・Execution result

Summary

  • To read the data table, use “Read CSV” and “Read Range”.
  • For data table output, use “Output Data Table”, (row number)(column number or column name) after the data table variable, (column number or column name) after the row data using “For Each Row”.
  • Use “Lookup Data Table” to search the data table.
  • To add a row to the data table, use “Add Data Row“.
  • To remove a specific row from the data table, use “Remove Data Row“.
  • To modify the specified data to the data table, assign the modified data to the DataTable (row number) (column number or column name).