...
...
...
...
...
...
...
...
Overview
In this how-to article, we are describing how to use DataBridge Transformation to split an input column into multiple output columns.
...
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 this URL: 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
.png?version=1&modificationDate=1567603319908&cacheVersion=1&api=v2&width=500)
In the Split transformation the settings to choose are:
...
Output field name: The name of the output field
SeperatorSeparator: 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:
...
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 |
|---|
Separator | 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 |
|---|
Separator | 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 |
|---|
Separator | 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 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 splitted 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 Settingsof Settings of the profile. You can select the format that is suitable for you your system.
In your external system, you can use the new data fields without time.
...
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.