Resources

Knowledge Base

How to use the Insert DataTable activity

Building a custom data table is useful when you want to add to it a number of rows that you don't know in advance.

If you are interested in the "Build Data Table" activity, please scroll down to the end of this post.

Building a custom data table is useful when you want to add to it a number of rows that you don't know in advance.
What do you do with the data table once it has been filled with data? You might want to save it in a database table, an Excel file or a CSV file.

This example shows you how to:

  • create a new DataTable object (that doesn't have any rows or columns)
  • add 2 columns (you may need to add more)
  • add 3 rows (this is the part where you normally add as many rows as needed, possibly in a loop, as the result of a previous activity)
  • save it in a CSV file (persist the data)

Let's create a sequence and then create a variable which is valid inside the sequence:

Create_Data_Table_IMG1.png

The first activity we add to the sequence is Assign: assign to the newly created variable a new object of DataTable type.

Create_Data_Table_IMG2.png

Now our data table has no rows and no columns.


Lets add two columns: Age, which will hold numbers, and Sex, which will hold strings.
Since we plan to record the age as a whole number only, we select the data type Int32.Create_Data_Table_IMG3.png

For the Sex column, we might store either 'm' (or 'male') or 'f' (or 'female').
Please note the MaxLength property in the Options category, which makes sense for string columns. Let's set it to 20.

 Create_Data_Table_IMG4.png

 

The meaning of other important attributes of a Data Column:

Input category:

Column - you can attach to the data table specified above an already existing column, possibly obtained from another table; if you specify a value for this property, all the properties in the **Options** category (AllowDBNull, AutoIncrement, DefaultValue, MaxLength, Unique) are ignored because they are copied from the existing column. You still need to specify the name for the new column and the data table to be attached to.
ColumnName - the name that the new column will bear. Usually it's a literal string, but you may use a string variable that holds the name.
DataTable - the name of the variable that represents the data table to which this column will be attached.

Misc category:

TypeArgument - select from the list the type of data that this column will hold.  The most common are String, Int32, DateTime. You can click "Browse for Types..." and search for other data types (for example, in the Browse for Types window, you can search for System.Double, which can hold decimal numbers).

Create_Data_Table_IMG5.png

Options category:

AutoIncrement - Specifies whether the value of the column increments automatically when a new row is added.
Unique - If checked, specifies that the values in each row of the new column must be unique.
DefaultValue - Specifies the default value for the new column when creating new rows. The value must be of the type stated by the TypeArgument property. If a specific value is not provided, the default one will remain active.
AllowDBNull - Specifies whether null values are allowed in the new column for rows that belong to the table.
MaxLength - Specifies the maximum length allowed for string types for each value of the new text column; ignored for non-text columns.

 

Now we'll examine the properties of the DataRow object (available through the Add Data Row activity)

Create_Data_Table_IMG6.png

You must specify the name of the data table to which the newly built row is added.
The ArrayRow property allows you to specify values for the columns of the new row, in the format of a list (identified by { and }) whose elements are comma-separated. The string literals are enclosed in double quotes, the numbers need not be. If you don't specify values for all the columns, the missing columns are left empty in the new row or the default value (if it was specified when the column was created) will be used.

As a first example, we add three rows to the data table, with the following values:
{30, "m"}
{27, "f"}
{35, "m"}

Create_Data_Table_IMG7.png

 

The second example, pictured below, uses a loop (Do While activity)
In this loop, the user is asked to provide a value for the Age, then a value for the Sex and stores them in a new DataRow, which is attached to the given Data Table.
The loop ends when the user enters the value 0 for the age.
The values entered for the Age and the Sex are stored into 2 variables, which are used to specify the array values that represent the source of the data row (property ArrayRow).

The whole Data Table is then written into a CSV file.

Create_Data_Table_IMG8.png

 

The "Build Data Table" activity allows you to create, during the design time, a table with an initial number of rows and columns; useful when you know in advance all the columns that you need; in this case there is no need to use the "Add Data Column" activity. However, you are still allowed to dynamically add columns, as shown above. Most probably you will only need to add rows (in addition to what you've already added during the design time).

Create_Data_Table_IMG9.jpg

To add more rows at design time, simply fill the cells on the last row, press Tab in the last column and UiPath Studio will automatically add a new row for you.