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.

Object value(int columnIndex)

returns the value (String, Number, LocalDate, LocalTime - according to column type) of column at columnIndex

Object value(String indicatorCode)

returns the value (String, Number, LocalDate, LocalTime - according to column type) of column specified by indicatorCode. Applicable in existing data sets only.

Object value(String attributeCode)

returns the value (String, Number, LocalDate, LocalTime - according to column type) of column specified by attributeCode. Applicable in existing data sets only.

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.

String name()

returns the name of the current column

String name(int columntIndex)

returns the name of the column a colimnIndex

String format()

returns format of the current column

String defaultValue()

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


boolean

stores boolean value True / False, used usually as a result of functions in branching expressions

String

stores a string of characters

int

stores an integer number

double

stores an decimal number

Date

represents an Date object in gregorian format

LocalDate

represents the Date part of the date for using in date and time functions

LocalTime

represents the Time part of the date for using in date and time functions

DateTime

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 ".").

More scripting language basics

Transformation scripts topics

Advanced script tools

  • No labels