Add Column

Adds a column to the specified data table.

Command availability: IBM RPA SaaS and IBM RPA on premises

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

addColumn --dataTable(DataTable) --columnname(String) --type(String) [--expression(String)]

Input parameter

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label Script mode name Required Accepted variable types Description
Data Table dataTable Required Data Table Data table that is evaluated.
Column Name columnname Required Text Enter the name that is given to the new column.
Type type Required Text Select the data type of the new column added to the data table.

See the type parameter options.
Expression expression Optional Use an expression to filter or column data. For more information, see the expression parameter options.

type parameter options

The following table displays the options available for the type input parameter. The table shows the options available when working in Script mode and the equivalent label in the Designer mode.

Designer mode label Script mode name
Boolean Boolean
Number Numeric
Text String
Date Date
Date Time DateTime
Time Span TimeSpan
Money Money
Phone Number PhoneNumber

expression parameter options

The expression parameter allows you to filter rows in a data table based on specific conditions.

When you create a Where expression, reference column values by using the column name. The column name can consist of simple sequences of characters or special characters such as ~, (, ), #, \, /, =, >, <, +, -, *, %, &, |, ^, ' ", [, ], and (blank space). Enclose column names containing special characters within either square brackets or the grave accent (`).

Example:

Column name containing special characters: [Column#] or `Column#`.

For information about filtering column names with reserved words, see Expression Syntax 🡥.

Note:The where parameter does not require double quotation marks around the expression syntax when entering it.

Value types guideline

  • String values
    Enclose string values within single quotation marks, for example: Name = 'John'. To include single quotation marks within a string value, escape them using another single quotation character, for example: Name = ''John''.

  • Date values
    Enclose date values within single quotation marks, for example: Date = '05/05/99'.

  • Numeric values
    Define numeric and decimals values directly, without enclosing them within any characters, for example: Goal = 4 or Price = 70.00.

  • Operator values
    You can use Boolean operators, comparison expressions and mathematical operations:

Boolean operators:

  • AND
  • OR
  • NOT

Comparison expressions:

  • <
  • >
  • <=
  • >=
  • <>
  • =
  • IN
  • LIKE

Mathematical operations:

  • * (multiplication)
  • / (division)
  • + (addition)
  • - (subtraction)
  • % (modulus)

For more information about supported Boolean operators precedence and arithmetic operators, see Operators 🡥.

Also, you can use wildcard characters with the LIKE comparison expression. For more information, see about rules and patterns of wildcards, see wildcard characters 🡥.

Aggregate functions

Use aggregate functions by referencing the table column name. The following functions are supported:

  • Sum()
  • Avg()
  • Count()
  • Min()
  • Max()
  • StdDev()
  • Var()

Example: Count(Salary) >= 3

Also, you can use the following supported functions:

  • LEN(Column name): Gets the length of a string. For example: LEN(Name) = 4.
  • SUBSTRING(Column name, start index, substring length): Gets a substring of a specified length, starting at a specified point in the string. For example: SUBSTRING(Name, 1, 3) = 'Car'.
  • TRIM(Column name): Removes all leading and trailing blank characters, such as, \r, \n, \t, (blank space). For example: TRIM(Name) = 'User name'.
  • IIF(Column name, true statement, false statement): Gets one of two values depending on the result of a logical expression. For example: IIF(Name = 'John', True, False) = True.

Example

Use the Add Column command to add a column that uses the type Date.

defVar --name excelFile --type Excel
defVar --name excelTable --type DataTable
defVar --name columnNameList --type List --innertype String
// Enter the file path to the excel file
excelOpen --file "tableExcelCompanyCity.xlsx" excelFile=value
excelGetTable --file ${excelFile} --getfirstsheet  --entiretable  --hasheaders  excelTable=value
getColumns --dataTable ${excelTable} columnNameList=names
logMessage --message "Table columns before addColumn: ${columnNameList}" --type "Info"
// Add a new column that uses the Date type 
addColumn --dataTable ${excelTable} --columnname "Column Name" --type Date
getColumns --dataTable ${excelTable} columnNameList=names
// Displays all the columns in the console
logMessage --message "Table columns after addColumn: ${columnNameList}" --type "Info"