Skip to main content

Transformations in DataSync

The Transformations module stores value mappings that DataSync applies to the fields of an extraction. Each mapping pairs an old value from the source with a new value for the destination, so DataSync replaces one with the other while it loads the data.

Mappings are grouped in a transformation group, which you then select in your extractions. This keeps the destination data in the same format, even when the sources use different ones. A transformation fits simple one to one replacements. For more complex logic, such as a result that depends on other fields, use a calculated field instead.

Example use cases:
  • Add a prefix to customer IDs: 10001 becomes C10001.
  • Remove dashes from account numbers: 40000-00-00 becomes 400000000.
  • Standardize titles: MR. and mr both become Mr..
  • Replace country names with codes: Canada becomes CA.

Create a transformation group​

A transformation group contains the pairs of old and new values that DataSync applies to a field. To convert blank or missing values, enter one of these keywords in the Old Value column:

KeywordMatchesWhat the field contains
BI_EMPTYEmpty valuesMatches fields that exist but contain no text.
BI_NULLNull valuesMatches fields that have no value stored at all.
  1. In DataSync, select the Transformations module.
  2. Click New.
  3. Enter a name for the transformation group, then click Next.
  4. In the Old Value column, enter the source value to convert.
  5. In the New Value column, enter the value that replaces it in the destination.
  6. Repeat steps 4 and 5 for each value to convert.
  7. Click Save.
Transformation dialog in DataSync with the Old Value and New Value columns for entering value pairs and the option to import an Excel file
  1. Enter the value to match in the source database.
  2. Enter the value that replaces the old value in the destination database.
  3. Import the value pairs from an Excel file.
Delete a transformation

Select the transformation, then click the trash icon in the upper right corner.

Import transformations from an Excel file​

Import an Excel file when you already have a long list of value pairs, so you do not have to enter each row by hand. The file needs one column for the old values and one for the new values. During the import, you select an import mode that decides what happens to the values already in the group:

  • Replace overwrites all existing values in the transformation group with the values from the Excel file.
  • Merge adds the values from the Excel file to the group and keeps the current entries.
  1. Click New.
  2. Enter a name for the transformation group, then click Next.
  3. Click Choose an Excel file and browse to your file, or drag the file into the highlighted area.
  4. Select the sheet to import.
  5. Select whether the first row contains headers.
  6. Select the columns that hold the Old Value and New Value data.
  7. Select Replace or Merge.
  8. Click Continue, then click Save.

Export a transformation​

Export a transformation group to back it up, to share it with another environment, or to reuse it in a different DataSync installation. The exported .xlsx file uses the same Old Value and New Value layout as the Excel import, so you can import it into another installation.

..
  1. On the Transformations page, select a transformation in the list.
  2. Click the Export icon in the upper right corner.
  3. Enter a name for the export file.
  4. Click Export. DataSync saves the transformation as an .xlsx file on your computer.