The data imported may be transformed e.g. to adapt them to a format demanded by
the database. Click on the button to open a window as shown below.
Here you can enter 4 types of transformation 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 than the position set to 2. You can change the direction of the
sequence with the button
Seq starting at the first position and
starting at the last position.
Click on the button Test the transformation to see the result of your
transformation.
The
translate transformation
translates 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 roman into numeric notation. To do this click on the
button to add a translation
transformation (see below). To list all different values present in the data,
click on the
button. A list as shown below will be
created. You may as well use the
and
buttons to add or remove values from the
list or the
button to clear 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.
For a short introduction see the video
.
To load a predefined list for the transformation use the
button. A window as shown below will
open. Choose the encoding of the data in you translation source, if the first
line contains the column definition and click on the button to open a
file. Click OK to use the values from the file for the translation.
To load a database table for the transformation use the
button. A window as shown below will
open. Choose the table and the columns for the translation. Click OK to use the values from the
table for the translation. Only unique values from the table will be
used to ensure an unequivocal translation.
The values of the database table will be listed underneath as shown
below. For the translation will only be regarded if the manual list does
not contain the corresponding table. That means that content in the
manual table will overwrite corresponding content in the database table.
To remove the database table use the
button.
The transformation using regular expressions will transform the values according to the entered Regular expression and Replace by vales. For more details please see documentations about regular expressions.
The replacement transformation replaces any text in the data by a text specified by the user. In the example shown below, the text "." is replaced by "-".
The calculation transformation performs a calculation on numeric value, dependent on an optional condition. In the example below, 2 calculations were applied to convert 2-digit values into 4 digit years.
The filter transformation compares the values from the file with a value entered
by the user. As a result you can either
Import
content of
column in file or
Import a fixed value. To select
another column that should be compared, click on the
button and choose a column from the
file in the window that will open. If the column that should be compared is not
the column of the transformation, the number of the column will be shown instead
of the
symbol. To add further filter
conditions use the
button. For the combination of the conditions
you can choose among AND and OR.
The color transformation converts values representing a color between different formats.
