Handling Custom Columns in ETL Profile Updates

This article explains how to update ETL profiles when your uploaded data file has a different number of columns, and how to use custom columns to transform data using JavaScript.

When Column Changes Occur:

If your uploaded file contains more or fewer columns than expected, ACE will prompt you to review the changes.

• If prompted, review the changes in the file structure.

• Click Yes to update the ETL profile to match the new file.

• Proceed to the Data Map section to review and adjust mappings.

Mapping New Columns

1. Navigate to the ETL Profile → Data Map section.

2. Identify any new columns that need to be mapped.

3. For columns that require calculated values or transformation, use a custom column.

What Are Custom Columns?

Custom columns let you apply logic using JavaScript to extend or transform your data. They can be used to:

• Transform or combine field values

• Skip rows based on conditions

• Format numbers or strings

• Flag or enrich records with metadata

Please Note:

Scripts are evaluated from left to right. You can only reference columns to the left of the custom column.

If you’re not comfortable with JavaScript, contact ACE Support for assistance.

Adding a Custom Column in ETL Profile (Using ETL Pro Logic)

Custom columns are created inside an ETL Profile, but the logic itself is written in the ETL Pro Logic editor. When you add a custom column, ACE automatically opens ETL Pro Logic for you to enter the JavaScript.

Steps

1. Scroll to the Custom Column section of your ETL Profile and click Add.

2. The ETL Pro Logic editor will open.

3. Enter an Identifier (e.g., SKIPCHECK).

4. In the Script section, add your JavaScript logic.

5. Click Save.

var active = 'false'
skip = true
if (line[12] !== '')
{
    active = 'true'   
    skip = false
}

active

Custom Column Example

• Identifier: SKIPCHECK → This is the name of the logic being defined.

• Column #: 13 → This specifies that the logic is applied to column 13 in the input data.

• Script: → The script defines the custom logic.

ETL Pro Logic Help and Help Examples

When you open the ETL Pro Logic to add custom columns, there is a help and help examples for reference: