Data is a very integral part of any process and it isn’t any different for UiPath automation. In most data driven processes, we are either reading data or modifying it. Data Table 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.
In Data Tables, the regular convention of identifying the columns and the rows are applied. Columns are identified through capital letters and rows through numbers.
A Data Table is an in-memory representation of a single database table which has a collection of rows and columns.
How are Data Tables Created?
The most common activities and method to create Data Tables are:
- The Build Data Table Activity
- The Read Range Activity
- The Read CSV Activity
- The Data Scraping Action
- The Generate Data Table from Text Activity
- 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 allow null values, unique values, auto-increment (for numbers), default value and length (for Strings).
a. Drag and Drop Build Data Table Activity from the activity panel.
b. Click on Data Table and Configure column as per requirements.
c. Column properties configuration
2. Read CSV Activity: – This activity captures the content of a csv file and stores it in a Data Table variable. Although not commonly used anymore, there are still legacy or internal-built applications with this kind of documents. This activity resides under the app integration section under CSV option.
You can directly drag and drop the activity into the workflow and start using it.
3. Read Range Activity: – This is the most common activity to create data table which is being used when you are working on the excel files. This activity gets the content of worksheet (or a selection from that worksheet) and stores it in a data table variable, which can be created from the properties panel using Ctrl + K.
There are two types of Read Range activities available in Activity panel. One under the Excel Activity and the other is under the Workbook Activity. There is no hard difference between both the activities. Both populate the data table as an output. For Excel activity Read Range you need to use Excel Application scope whereas for workbook activity you can directly use the Read Range.
4. Data Scraping Action: – This functionality of UiPath studio enables you to extract structured data from your browser, application, or document to a Data Table.
5. Generate Data Table from Text Activity: – This can be used to create a Data Table from structured text, by letting the use indicate the row and column separators. You can use this activity by drag and drop from activity panel.
UiPath offers a broad range of activities that can be used to work with Datable Variable: –
- Add Data Column: – Adds a column to an existing Data Table variable. The input data can be of Data Column 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 Data Table variable. The input data can be of Data Row type or can be entered as an Array Row, by matching each object with the data type of each column.
- Build Data Table: – Is used to create a Data Table using a dedicated window. This activity allows the customization of number of columns and type of data for each column.
- Clear Data Table: – Clears all the data in an existing Data Table variable.
- Filter Data Table: – Allows filtering a Data Table through a Filter Wizard using various conditions. This activity can be configured to create a new Data Table for the output of the activity or to keep the existing one and filter (delete) the entries that do not match the filtering conditions.
- For Each Row in Data Table: – Is used to perform a certain activity for each row of Data Table (like For Each Loop).
- Generate Data Table from Text: – Can be used to create a Data Table from structured text by letting the user indicate the row and column separators.
- Join Data Tables: – Combines rows from row 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 like VLOOKUP in Excel. You can search for a provided value in the specified Data Table and the Row Index returns its value. It can also be configured to return the value from a cell with the given coordinates (Row Index and Target Column).
- Merge Data Table: – Is used to append a specified Data Table to the current Data Table. The operation is simpler than Join Data Type activity, as it has 4 predefined actions to perform over the missing schema.
- Output Data Table: – Writes a Data Table to a string using the CSV format.
- Remove Data Column: – Removes a certain column from a specified Data Table. The input may consist of the column index, column name or Data Column variable.
- Remove Data Row: – Removes a row from a specified Data Table. The input may consist of the row index or a Data Row variable.
- Remove Duplicate Row: – Removes the duplicate rows from a specified Data Table variable, keeping only the first occurrence.
- Sort Data Table: – Can sort a Data Table in an ascending or descending order of the values in a specific column.
- Get Row Item: – Retrieves a value from a row in a Data Table according to a specified column.
- Update Row Item: – Assigns a specified value to the indicated column of a Data Table row.