Home > Data Import > Transforming Data (TLANG)

Transforming Data (TLANG)

As each input column is processed, the using block can contain commands to clean and transform the column value. For example, we can trim whitespace from the column value:

  NAME -> c.name using { trim(), toUpperCase() }
}

These statements make up a small language, called TLANG. Each TLANG statements receives the value produced by the the previous statement, and can transform it to a new value. The value produced by the final statement is the final result of the TLANG execution. TLANG statements are separated by a ‘,’ character.

TLANG function	Description	Example
abbreviate(maxlen)	truncate a string if it exceeds a give maxlen. The resulting string will be at most maxlen characters long.	abbreviate(32)
abbreviate(maxlen,suffix)	truncate a string if it exceeds a give maxlen. Adds the given suffix. The resulting string will be at most maxlen characters long.	abbreviate(32, ‘…')
asDate(dateFormat)	converts the string to a Date using the given dateFormat	asDate(‘yyyy-MM-dd’)
combine(param1, …)	combines the parameters into a single string. The parameters can be column names or string values.	combine(FIRSTNAME, ' ‘, LASTNAME)
fail()	causes the entire import to terminate.	fail()
split(delimiter)	splits the string into an array of elements separated by delimiter. Then you can use `first` or `allAfterFirst’ to get one of the split values. \| split(’ ‘).first() \|
substringAfter(separator)	the substring after the first occurrence of a separator	substringAfter('-')
substringBefore(separator)	the substring before the first occurrence of a separator	substringBefore('-')
substring(startIndex)	the substring starting at startIndex	substringString(4)
substring(startIndex, endIndex)	the substring starting at startIndex and ending at endIndex	substringString(4)
toLowerCase()	converts the string to lower-case	toLowerCase()
toUpperCase()	converts the string to upper-case	toUpperCase()
trim()	remove whitespace from beginning and end of string.

Explicit Values

You can specify an explicit value to use. The column value is ignored and the explicit value is used instead. This is generally used with the if statement.

NAME -> c.name using { 'some constant' }
}

You can also specify the value of any let variable in the current Delia session. This is useful for computed default values. The following would use the value of let variable “defaultCategory”.

CAT -> c.category using { if missing then defaultCategory, endif }

Conditional Statements

TLANG supports an IF statement for doing conditional tranforms. The if statement must end with an endif statement.

The conditional expression is similar to those with validation rules. The keyword value means the current value that the TLANG command has received as input.

GRADE -> c.grade using { if value > 100 then
                           100,
                         endif
                       }
}

The syntax “if condition then statement” is considered a single TLANG statement. The comma delimiter comes after statement. elseif has the same syntax.

The if statement can contain elseif and else statements.

NAME -> c.taxCode using { if value.len() < 2 then
                           combine('ss',value), 
                         else if value.len() < 3 then
                           combine('s',value), 
                         else 
                           value,
                         endif
                       }
}

If the if statement is producing a final value, you can optionally use return *statement instead of then. In this case, endif is not required.

GRADE -> c.grade using { if value > 100 return 100,
                         if value < 0 return 0  
                       }
}

Default Values

A common import requirement is to provide a default value if there is none in the CSV column. The missing condition can be used; it is true if the input value is null or empty string.

TAXRATE -> c.tax using { if missing return 0.13
                       }
}

Enumarations

Another common import activity is to convert an enumeration value in the CSV data to a different enumeration in the Delia type. Use an if statement.

TAXCODE -> c.tax using { if value == 'A47' then 'GST',
                         elseif value == 'A49' then 'PST',
                         elseif value == 'A99' then 'VAT',
                         else 'NOTAX'
                       }
}

TLANG function

If the same set of TLANG statements is being used in many fields and input functions, you can define a tlang function.

tlang function productCode() {
  trim(),
  toUpperCase(),
  abbreviate(32)
}

Then you can use the TLANG function in any using statement.

PCODE -> c.productCode using { productCode() }