Skip to main content

Transformations

The Transformations module lets you create and manage value mappings that are applied at the field level in extractions, regardless of extraction type. Transformations allow you to convert source values into the desired destination values, ensuring consistent and clean data during migration.

A transformation defines how an old value from the source database is converted to a new value in the destination database. This is typically used for simple one-to-one mappings. For more complex logic, you can use a calculated field instead.

Example use cases​

example
  • ID formatting: Add a prefix to numeric IDs (10001 → C10001).
  • Number format: Remove dashes from numbers (40000-00-00 → 400000000).
  • Data standardization: Convert variations of titles to a consistent format (MR. and mr → Mr.).
  • Abbreviation mapping: Replace values with standard abbreviations (Canada → CA).

Add a transformation group​

A transformation group contains one or more value pairs to convert during an extraction.

EntryDescription
Old ValueThe value in the source database to be matched.
New ValueThe value to replace the old value in the destination database.

Add manually​

  1. From the welcome screen or left navigation pane, select Transformations.
  2. Click New.
  3. Enter a name for the transformation group, and then select Next.
  4. In the Old Value column, enter the source value to be converted.
  5. In the New Value column, enter the destination value.
  6. Use BI_EMPTY to replace empty values or BI_NULL to replace null values.
  7. Press Enter to add another line and repeat the process as needed.
  8. Click Save.

Import an Excel file​

  1. From the welcome screen or left navigation pane, select Transformations.
  2. Click New.
  3. Enter a name for the transformation group, and then select Next.
  4. Select Choose an Excel file and browse to locate your file, or drag the file into the specified area.
  5. Select the sheet to import.
  6. Choose whether the first row contains headers.
  7. Select the columns that hold the Old Value and New Value data.
  8. Select the Excel import mode:
    • Replace – Overwrites all existing values in the transformation group with those from the Excel file.
    • Merge – Adds values from the Excel file to the existing group without removing current entries.
  9. Click Continue, then Save.

Export a transformation​

Export a transformation group if you want to back it up, share it with another environment, or reuse it in a different DataSync installation.

  1. In the Transformations page, select a transformation from the list.
  2. Click the Export icon in the top-right corner.
  3. Enter a name for the export file.
  4. Click Export to save the file as an .xlsx file on your computer.

Edit a transformation​

  1. In the Transformations page, select a transformation from the list.
  2. Click the Edit Group icon in the top-right corner.
  3. Make the required changes.
  4. Click Save.

Delete a transformation​

  1. In the Transformations page, select a transformation from the list.
  2. Click the Delete Group icon in the top-right corner.
  3. In the confirmation dialog, select Confirm.