How to use transformation Split field

Overview

In this how-to article, we are describing how to use DataBridge Transformation to split an input column into multiple output columns.

In DataBridge you can use the Transformation Split field for this. 

Sometimes the information contained in a single field needs to be split into several pieces to match the expected output. Most of the time, it is a field that contains a full name that we want to separate into first name and last name. It can also be a complete address which lines must fit in distinct output fields.

The split transformation lets you split a field into segments by any chosen separator and pick which segments you want to keep in the output. This means that if you want two split a field into two separate fields, then you need to create two split transformations with the same separator but different selected segments.

Prerequisites

The following prerequisites are required before you follow the steps below.

1. A (demo) subscription of DataBridge, to sign up, follow this URL: https://my-databridge.infobridgeuniverse.com/Account/SignUp
2. A SuperOffice CRM login name with administrator rights

Configure the DataBridge Profile 

Create a new profile or edit an existing profile by clicking Manage profile. On the Transform page, select the Transformation you want, in this case, Split and click Add

In the Split transformation the settings to choose are:

Field: Select the field

Output field name: The name of the output field

Separator: The separator consists of any character or sequence of characters. It can also contain special characters, but since all of them cannot be typed into a textbox, you can use the following syntax to include them:

• [:space:] - matches space (\s) characters
• [:tab:] - matches tab (\t) characters
• [:newline:] - matches newline (\n) characters
• [:return:] - matches carriage-return (\r) characters

NB: of course you can directly type a space into a textbox, the [:space:] syntax merely helps make it stand out.

The number of occurrences of the separator in a field determines the number of segments you get. So, if the separator is a space and the field contains three words (two spaces) then you get three segments.

Segments from/ to: Once the field is split into segments, you can choose which sequence of segments you want to include in the output. To do so, you have to select the index of the first segment to include, and the index of the last segment to include.

To select these indexes, you can either choose in the drop-down lists one of the values First, Second, Next to last or Last or you can enter in these same drop-down lists any custom number.

The index is zero-based, meaning that the first segment has index 0, the second segment index 1, etc.

You can also select segments starting from the end. In that case, the last segment has index -1, the segment before that has index -2, etc.

Example:

In practice, if you need to split a full name into first name and last name, you have to think about the fact that many people have compound surnames, which means that the field will be split into more than two segments. You can decide that the first word will always be the first name, and all the rest shall be placed in the last name output field.

To do so, you have to create two split transformations. In both transformations, you set the separator as [:space:]. In the “first name” transformation, select segments from First to First and in the “last name” transformation, select segments from Second to Last. Voilà! All your contacts with a compound surname can now have their family name correctly registered in your system.

Examples for splitting person full name

Enter the values below to achieve the needed split functionality.

Split field transformation sample: Split person name in three separate SuperOffice fields, applicable when the middle field function is used in SuperOffice. You need to create three split transformations with the same separator but different selected segments.

Split field transformation sample: Split person name in two separate SuperOffice fields. You need to create two split transformations with the same separator but different selected segments.

Example for splitting street address 

Split field transformation sample: Split Street address in separate street name and house number. You need to create two split transformations with the same separator but different selected segments

Example for splitting date field 

Split field transformation sample: Split date field in separate date and time field. In this case, a consultant did not want the time from the updatedDate field exported next to the date. Note: Although we now have a separate option to select the date/time format of your choice when exporting (more info), we did want to show you this example.

• Choose the new transformation Split field
• In the split field setting page select the date field that must be cut so it doesn’t contain the time anymore.
• In the Output field name enter a name for a new column that contains the split date
• In separator add the split delimiter needed, in this case [:space]
• The segment from and to can be left empty for a date-time split. (When you have several splits as a result (e.g. “Pierre van Mever” will have 3 segments) each segment can be assigned to a separate field in these selectors)
• Click save and finish the export profile.

The result will be like:

Note that the date format is changed, this is because we have chosen a different format in the Destination Settings of the profile. You can select the format that is suitable for your system.

In your external system, you can use the new data fields without time.

Ignore field if not splittable: Your data might not always be properly formatted, and it can happen that a field does not contain the separator as expected. In that case, what should happen to that piece of data?

When you want to split a name into first and last name, if you happen to have only the last name of some of your contacts then you probably don’t want the last name to appear twice in the output, both as a first name and a last name.

To avoid this, you can simply check the “Ignore field if not splittable” in one of the transformations, and uncheck it in the other one.

Extending the idea

This how-to article describes how to use transformations to split an input field into multiple output fields. There are also many more transformations you can use, see these articles:

More info on import transformations
More info on export transformations

Summary

You have learned to use the transformation Split in order to split a single field into several pieces to match the expected output.