Add transforms - Automate_Studio - 20.3

Automate Studio with Connect User Guide
Product type
Software
Portfolio
Integrate
Product family
Automate
Product
Automate > Automate Studio
Version
20.3
Language
English
Product name
Automate Studio
Title
Automate Studio with Connect User Guide
Topic type
Overview
Installation
Administration
How Do I
First publish date
2018
ft:lastEdition
2024-05-06
ft:lastPublication
2024-05-06T08:28:10.436135

By using Excel formulas during the mapping stage, you can transform data before uploading or downloading it. This feature is available for Excel data files.

  1. In the Mapper, click the field that contains the values you want to transform. On the ribbon, click Add Transform.

  2. In the Transform Rule box, enter the Excel formula.

    In the example below, the formula for the field Net Weight will be applied to net weight values that start from cell H2, and the transformed value will be stored in column J from cell J2 onwards.

    Note:

    If the formula is offset by one or more rows, you can specify that in the Row where formula will be evaluated box. For example, if you offset the formula above by one row, type 3 in the box.

    If you are using a date in the formula, wrap the formula in the =TEXT( , "mm/dd/yyyy") function. For example, if the formula is =today()+1, enter =TEXT(today()+1, "mm/dd/yyyy").

    Transforms on header fields apply only to rows that contain header data, and transforms on line-item fields apply only to rows that contain line-item data. If you have header and line-item data mapped to the same column, you will need to add the transform twice.

    Note:

    Transform formulas may be entered in all languages supported by Microsoft Excel.

  3. To validate the Excel formula, click Validate.

  4. Do one of the following:
    • To overwrite the original data, change column letter in the Column box to the original column—in the example, F. Click OK.
    • To retain the original data, click OK.

The spreadsheet includes the formula in the header row.

header with field name description and formula