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.
- A (demo) subscription of Nebula DataBridge, to sign-up, follow this https://my-databridge.infobridgeuniverse.com/Account/SignUp
- 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
Seperator: 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:
Gabriel | José | de | la | Concordia | García | Márquez |
|---|---|---|---|---|---|---|
0 | 1 | 2 | 3 | 4 | 5 | 6 |
-7 | -6 | -5 | -4 | -3 | -2 | -1 |
First segment index | Last segment index | ||
|---|---|---|---|
First word | 0 | 0 | |
First three words | 0 | 2 | |
Last three words | -3 | -1 | |
First word to next to last word | 0 | -2 | |
Second word to last word | 1 | -1 | |
Last word | -1 | -1 |
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.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.
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.
Value in import or export column | Output Field Name | Seperator | Segments from | To | Ignore field if not splittable | Remark |
|---|---|---|---|---|---|---|
Pierre van Mever | First name | [:space:] | first (index 0) | first (index 0) | see details below | This will only put the first name in your output field |
Pierre van Mever | Middle name | [:space:] | second (index 1) | second (index 1) | see details below | This will only put the middle name in your output field |
Pierre van Mever | Last name | [:space:] | last (index -1) | last (index -1) | see details below | This will only put the last name in your output field |
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.
Value in import or export column | Output Field Name | Seperator | Segments from | To | Ignore field if not splittable | Remark |
|---|---|---|---|---|---|---|
Pierre van Mever | First name | [:space:] | first (index 0) | first (index 0) | see details below | This will only put the first name in your output field |
Pierre van Mever | Last name | [:space:] | second (index 1) | last (index -1) | see details below | This will put the middle name and last name in your output field |
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
Value in import or export column | Output Field Name | Seperator | Segments from | To | Ignore field if not splittable | Remark |
|---|---|---|---|---|---|---|
Jan de Groot laan 4 | Street name | [:space:] | first (index 0) | next to last (index -2) | see details below | The entire street name except the number will be in your output field |
Jan de Groot laan 4 | House number | [:space:] | last (index -1) | last (index -1) | see details below | This will only put the number in your output field |
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 seperate 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 splitted 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 Settingsof the profile. You can select the format that is suitable for you 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.