The transformation scripting engine's base concept comes out from the Groovy scripting language.
Transfornation scripting is an advanced features for processing data during import. Thanks to transformations cripts you can:
- cleanup values
- change values
- create new columns with values computed from other column(s)
- mark values with flags and notes
- build intelligence for handling values according to column names
You are enabled to enter transformation scripts in the column detail dialog while setting or editting import settings.
On this page:
Transformation scripts syntax resembles the Groovy and Java syntax. It is designed to be readable and effective but not strict. Users dfamiliar with scripting and programming should know, that some of the common features (like loops) are absent because of security and performance reasons. Script can be applied to and import column. The basic function is transforming the input value of a column row to another value.
Transformation scripts basics
- You can use variables in scripts for storing values and working with them
- You can use conditions and branching
- You can use wide pallete of functions
Our first script using variable and function:
val = value() return trim(val)
This script trims whitespaces from the beginning and end of the column value of each data import cell. You can see, that the last line contains command return. The function of the script can be described as "pick a value from the current cell, apply function trim and return the result".
Variables
You can store values into variables and use them into expressions or as a script result. The typing of variables is dynamic thus in most of the cases there is no need to think of a variable type.
Example of declaring universal variables which are dynamically typed. You can assign values to variables for later use:
a = value() b = 12 c = 100.56 bool = false e = "Hello" x = b + c return e + " world"
You can retype a variable or function return by adding as nameOfType:
double b = value() as double
Type name |
|
---|---|
|
stores boolean value True / False, used usually as a result of functions in branching expressions |
|
stores a string of characters |
|
stores an integer number |
|
stores an decimal number |
|
represents an Date object in gregorian format |
|
represents the Date part of the date for using in date and time functions |
|
represents the Time part of the date for using in date and time functions |
|
represents the DateTime object for using in date and time functions |
How to access the column values?
Accessing the value we want to process is a key issue. Scripts provide a function value() which returns the current value. There are more advanced possibilities to access values:
- Function value(x) returns value of a x-th column of the import
- Function name() returns name of the current column
- Function name(x) returns name of the x-th column of the import
- Function format() returns format name of the current column
The values function returns a String value. String cannot be handled as a number unless it is properly converted. The conversion usually consists of normalization (ex.: round all numbers to 2 decimal places) or cleanup (ex.: replace all "," with ".").