The data may be transformed during the import, e.g. to adapt it to a format demanded by the database. Click on the button to open the window below.
Here you can enter 4 types of transformations that should be applied to your data. cut out parts, translate contents from the file, RegEx apply regular expressions or replace text in the data from the file. All transformations will be applied in the sequence they had been entered. Finally, if a prefix and/or a postfix are defined, these will be added after the transformation. To remove a transformation, select it and click on the button.
With the cut transformation you can restrict the data taken from the file to a part of the text in the file. This is done by splitters and the position after splitting. In the example below, the month of a date should be extracted from the information. To achieve this, the splitter '.' is added and then the position is set to 2. You can change the direction of the sequence with the Seq button starting at the first position and starting at the last position. Click on the button Test the transformation to see the result of your transformations.
The translate transformation changes values from the file into values entered by the user. In the example above, the values of the month cut out from the date string should be translated from digits into names. To do this click on the button to add a translation transformation. To list all different values present in the data, click on the button. A list of entries will be created. You may as well use the and buttons to add values to or remove values from the list. Then enter the translations as shown below. Use the save button to save entries and the Test the transformation button to see the result. Press the button to clear the list.
The RegEx transformation using regular expressions will transform the values according to the entered Regular expression and Replace by values. For more details please see documentations about regular expressions.
The replacement transformation changes any text in the data defined under Replace by a text entered by the user under With, e.g. to adapt it to a format demanded by the database.
The calculation transformation performs a calculation on numeric value, dependent on an optional condition. In the example below a calculation is applied to convert 2-digit values into 4-digit years.