The transformation scripting engine's base concept is based on the Groovy scripting language.
Transformation scripting is an advanced feature for processing data during import. Thanks to transformation scripts 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 allowed to enter transformation scripts in the column detail dialog while setting or editting import settings.
Transformation scripts syntax resembles the Groovy and Java syntax. It is designed to be readable and effective but not strict. Users familiar 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".
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:
Object value() | returns the value (String, Number, LocalDate, LocalTime - according to column type) of current column. |
| returns the value (String, Number, LocalDate, LocalTime - according to column type) of column at |
| returns the value (String, Number, LocalDate, LocalTime - according to column type) of column specified by |
| returns the value (String, Number, LocalDate, LocalTime - according to column type) of column specified by |
LocalDate dateValue() | returns the value as LocalDate of current column. |
LocalDate dateValue(int columnIndex) | returns the value as LocalDate of column at columnIndex |
LocalDate dateValue(String attributeCode) | returns the LocalDate value of column specified by attributeCode. Applicable in existing data sets only. |
LocalTime timeValue() | returns the value as LocalTime of current column. |
LocalTime timeValue(int columnIndex) | returns the value as LocalDate of current column at columnIndex. |
LocalTime timeValue(String attributeCode) | returns the LocalTime value of column specified by attributeCode. Applicable in existing data sets only. |
Integer integerValue() | returns the value as Integer of current column. |
Integer integerValue(int columnIndex) | returns the value as Integer of current column at columnIndex. |
Integer integerValue(String code) | returns the Integer value of column specified by attributeCode. Applicable in existing data sets only. |
Double doubleValue() | returns the value as Double of current column. |
Double doubleValue(int columnIndex) | returns the value as Double of current column at columnIndex. |
Double doubleValue(String code) | returns the Double value of column specified by attributeCode. Applicable in existing data sets only. |
| returns the name of the current column |
| returns the name of the column a |
| returns format of the current column |
| returns default value of the current column, which is the value in "Replace empty cells with value" |
String getFileName() | returns file name of imported file |
String getPath() | returns path to the imported file. Useful for zip archives, ftp folders or http connections. |
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 |
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 ".").