Transformations for DataBridge export

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, Clean number, Merge fields, Replace, Format text value.
Click here to view the article on Transformations for DataBridge Import

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

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.

TransformExport5.5.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.
In the example below a User Defined Field is used (in this case this represents the SuperOffice serial number). As a fallback field the User Defined Field InfoBridge number is used.
The fieldname in the output field will be Customer Serial Number. 
When the first field 'SuperOffice Serial number' does not contain any information the 2nd field 'InfoBridge number' will be used. 

Add field

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

Transform13.pngThe result can be seen on the Summary page

Append text value

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

TransformExport16.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 Summary page you can see the result of the transformation, the non-digit characters are removed.

Transform2.png

Copy field

This transformation can be used for when you want one field from the source to map to multiple fields in the destination. In this example the AssociateId will be copied to the column AssociateId copy.

TransformExport15.png

Current date time

This transformation can be used to fill a field in an export file with the current date and time. Choose the Transform Current date time and choose a Field name

TransformExport13.png

On the Summary page of the Export wizard you will see the result of this transform

TransformExport14.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  

TransformExport11.png


We have chosen the format 'Upper Case' for the Name field. And this is the result on the Summary page.

TransformExport12.png

Merge fields

In this example we will merge the first- and lastname in the Export file. The fields will be merged into a new field 'Name'. As a delimiter we use a space.

TransformExport6.png


The result can be see on the Summary page.

TransformExport8.png

Remove fields

 transform prevents the selected fields from appearing in the output. In the example below the two selected fields will not be exported.

Rename fields 

This transform enables you to rename the title of a field. In this example the field 'name' will be replaced by 'Company Name' in the output file

Replace

In this example we will replace SuperOffice values. When they match, the values in the Interest column 'persint/Newsletter' will be replaced with the value OK. 

TransformExport9.png


On the Summary page you can see the result of the replace transformation.

TransformExport10.png

Skip

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