Use transformations
- Marcel Spapens
Data transformation is a crucial step in the data integration process, as it ensures that data is accurate, consistent, and compatible with the target system. By transforming data, you can ensure that your import and export processes run smoothly, without any errors or inconsistencies. Even if you are exporting to a file, transformations are very useful to make the exported data user-friendly or unified.
You will find the transformations dialog in the wizard steps of each DataBridge profile.
Add a new transformation by selecting one from the list and clicking Add.
Existing transformations are listed, and they can be copied, edited and deleted. Be careful with deleting transformations since this cannot be undone, and the column will be removed from the output immediately.
This article describes all the transformations that are available in DataBridge and the purpose they serve.
Transformation | Purpose | More information |
|---|---|---|
Add conditional field | Will use the value of the selected field unless that value is empty in which case it will use the value of another field to add to the output. |
|
Add field | Will add a new field to the output with a fixed value. |
|
Append text value | Adds a value before or after the value from the selected field. |
|
Clean field | Cleans the value from the selected field according to the setting. Clean number removes all non-digit characters. Clean text removes all special characters like dashes and dots. Convert to a numeric value removes all non-digit characters and leading zeros from the input value and treats it as a number. |
|
Copy field | Creates an exact copy of the selected field. |
|
Current date time | Creates a field with the value of the date and time of the exact moment that DataBridge processes the record. |
|
Find pattern | Extracts the value from the selected field that matches a search pattern. Regular expressions (regex) are used for the match. | |
Format text value | Formats the value from the selected field based on the setting: uppercase, lowercase, capitalize first, capitalize words or trim. |
|
Map list | Converts each possible value of the selected field into an item from a specific list in the destination platform or a text value in a file or database. | Read more: |
Merge fields | Merges two or more selected fields into one new output field with a delimiter between the values. |
|
Remove field | Hide the selected field from the output. |
|
Rename field | Rename the selected field. |
|
Reorder fields | Change the order in which fields will appear in the output. |
|
Replace | Replaces the value or a part of the value of the selected field with a new value. | Read more: |
Skip | Skip the record if the selected field matches or contains the specified value. |
|
Skip by date | Skip the record if the selected field’s date time value predates the last time the DataBridge profile ran successfully. | Available from system to file/database only. Cannot be used with instant jobs. |
Split field | Splits the value of the selected field into two or more parts and returns the selected part(s) in the output. | Read more: |
Switch fields | Returns the value of a field or a fixed value based on the value of a field. | Read more: |
Write back to source | Updates the source platform with the database ID of the created or updated record in the destination platform. | Available for supported system to system configurations only. |
Preview the results of a transformation
Use the preview button to show a preview of the transformation results. The preview will show the values from the selected input field and the value that will show in the output.
Use the refresh button to update the preview after a change to any of the settings.
Fields as the output of a transformation
All fields -or rather columns- that are added through a transformation are marked with a star ( * ) in the right upper corner of the column. Hover over the star to see the details of the transformation.
Chained transformations
It is possible to use the output of a transformation as the input (field) for another transformation. That is what we refer to as chained transformations. Read more