Transformations for DataBridge Import

Last update:
Created :
Written by Support InfoBridge

Overview:

DataBridge supports transformations for import and export files. Data transformations can be added to import and export jobs. At the moment the following transformations are supplied: Rename field, Add field, Append text value, Copy field, Clean number, Merge fields, Replace, Format text value, Current date and time.
Click here to view the article on Transformations for DataBridge Export

How to:

Create a new profile or edit an existing profile by clicking Manage profile. Notice that you now have an extra step in the configuration wizard called Transform.

On the Transform page select the Transformation you want and click Add

11-09-2018_16-10-43.png

 

The options to choose from are:

   

Add conditional field

Create a new field with the value of the selected field. When the value of that field is empty, the value of the fallback field will be used.

Add field

Add a new field with a fixed value.

Append text value

Allows prepending and appending text to field values

Clean number

Clean a text field that contains a number. Removes all non-digit characters, including white space and comma's and periods.

Copy field

Can be used for when you want one field from the source to map to multiple fields in the destination (for example: address which can be both visit and postal).

Current date time

This transformation can be used to fill a field with the current date and time

Format text value

Formats the value in the field.

Merge fields

Merges the input fields into a newly defined output fields.

Remove fields

Prevents the selected fields from appearing in the output.

Rename field

Renames the title of the field and leaves the value unchanged.

Replace

If the field matches the specified value it will be replaced by a new value. For example: if the input value is 'OK' it can be replaced by 'TRUE' and used to turn interests on.

Skip

If the field matches the specified value the record will be skipped


The added transformations can be edited or removed at any time, see below.

Transform5.png


NOTE:
If you are adding transformations to an existing profile with mappings in place and choose to use the Rename field transformation you will see the message as shown in the picture below. 
This means that a mapping is no longer valid, due to the renaming.
This can easily be fixed by clicking the Remove Invalid Mapped Fields button and map the field to the correct field.

Transform6.png

 

THE OUTPUT RESULTS

Add conditional field

Create a new field with the value of the selected field. When the value of that field is empty, the value of the fallback field will be used.

For instance, you have a import file with data from two different external systems. In it there are two number columns, now you can say basically: when column 1 has records import those, when empty choose column 2

Add field

For our import we have chosen to add a field Code with a fixed value 123

Transform13.png


The result can be seen on the Configure fields page

Transform14.png

Append text value

The “Append text value” transformation allows for prepending and appending text to field values.

Transform21.png

Clean number

In the example below we have chosen the transformation 'Clean number' to clean the column with phone numbers. In the Configure Source page you can see the phone numbers and on the Configure Fields page you can see the result of the transformation, the non-digit characters are removed.

Transform2.png

Transform12.png

 

 

 

Copy field

The Address field from the Import file will also be used for the postal address. This can be achieved by choosing the Copy Field transfrmation.

Transform16.png

The result can be viewed on the Summary page. The Address column from the import file can be used twice.

Transform17.png

Current date time

This transformation can be used to fill a field with the current date and time.
In this example we will fill a user defined field with the latest update by DataBridge. 

First choose the Transform 'Current date time' and fill in a field name. 

Transform19.png

The next step is to map the new field to a SuperOffice field. In this example we use a User Defined Field called 'DataBridge Import Date'. 

Transform18.png

After running the Import job, the current date and time is filled in in the Udef of SuperOffice.

Transform20.png

Format text value

With Format text value you can format the values in the chosen field. There are a couple of format options.
As an example let's use 'hello YouTube'

Upper case All in upper case HELLO YOUTUBE
Lower case All in lower case hello youtube
Capitalize first First letter in capital Hello YouTube
Capitalize first decapitalize rest First letter in capital, rest in lower case Hello youtube
Capitalize words First letter of each word in capital Hello YouTube
Capitalize words decapitalize rest First letter of each word in capital, rest in lower case Hello Youtube
Trim Trim will remove all leading and trailing white-space from the value in the fields  

Transform14.png


We have chosen the format 'Upper Case' for the Company field. And this is the result in the Configure fields.

Transform15.png

Merge fields

In this example the fields Address and Number will be merged into a new output field Street address. As a delimiter a space is used.

transform10.png

 

On the Configure fields page you can see the result of the merge.

Transform11.png

Remove fields

Prevents the selected fields from appearing in the output. This transform makes most sense for Export, more info

Rename fields

In the picture below you see the preview of a file with the column header 'Bedrijf'. With the transformation 'Rename field' we want to rename this column header from Dutch to English, to the column header 'Company'

Transform7.png

Transform3.png


The result can be seen in the Configure Fields page.

Transform8.png

Replace

In this example the values in the column Partner will be replaced when they match the value OK. This value will be replaced with True. This True value can be used to turn on interests in SuperOffice.

Transform12.png


On the Configure fields page you can see the result of the replace transformation.

Transform13.png

Skip

With this transformation you can choose to skip a record when a specified value is matched.