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.

 

 Related Articles Learn the Creation Techniques f UiPath robotics creation with Udemy’s online courses that take it up a notch

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

 

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.

 

・Build Data Table  Properties

・Output Data Table  Properties

 

・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

 

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

 

 

・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

Back to Table of Contents

 

 Related Articles Learn the Creation Techniques f UiPath robotics creation with Udemy’s online courses that take it up a notch

 same category UiPath