Description:

The Transform a List of Column Values step applies a configurable list of transformations to one or more columns within an ETL dataset. The transformation is configured through the Configure Transform List property, where ETL designers define the columns to transform together with the operations that should be applied.

Many enterprise ETL processes require multiple columns to undergo similar transformation logic during data preparation. Rather than creating several individual transformation steps, this component allows related transformations to be managed centrally within a single ETL step, simplifying ETL definitions and improving maintainability.

Depending on the configured transformation list, values may be standardized, reformatted, converted, normalized, or otherwise prepared for downstream processing.

The Transform a List of Column Values step is commonly placed after importing and cleansing data but before validation, filtering, aggregation, reporting, or exporting.

The step supports:

• Multiple column transformations
• Centralized transformation configuration
• Batch processing of column values
• Data normalization
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Standardizing imported datasets
• Applying common business transformations
• Preparing data for validation
• Normalizing imported values
• Enterprise data migration
• Report preparation
• Integration with downstream business systems

Usage:

The Transform a List of Column Values step is typically inserted after importing source data and completing initial data cleansing but before validation, grouping, aggregation, filtering, or exporting.

During execution, each configured transformation rule is applied to its corresponding source column. All transformed values become part of the ETL dataset and are immediately available to downstream components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Standardizing customer information
• Converting imported business values
• Preparing product information for reporting
• Normalizing employee records
• Applying consistent transformations across multiple columns
• Preparing ERP migration datasets
• Creating clean datasets for business intelligence and analytics

The transformed values can then be validated, grouped, filtered, aggregated, exported, or loaded into downstream systems.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsTransformListDef” example.          

• Create a new ETL definition called “clsTransformListDef” and open the definition in designer mode.
• Drag “clsInputCSV, clsTransformList, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” step and other steps, as shown above.
• Define a variable or a global variable to hold the virtual path. 
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsTransformList" step to configure its "Required" properties. Provide a name for the step. Select the output column from the drop-down list. Specify the input delimiter. Click the button to configure the combined columns. Click the Save button. 

• Click the "clsTransformList" step to configure its "Required" properties. Click the button to configure the transform list. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the source text column from the drop-down list. Enter the find value and the replace value for the transform. Click the Save button. You may add multiple columns for transformation using the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute it. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformList” step should transform a list of values, as configured, into the output file. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Group related transformation rules within a single configuration to simplify ETL maintenance.
• Perform trimming and basic data cleansing before executing transformation lists.
• Validate transformed values before exporting them to downstream systems.
• Use consistent business transformation rules across multiple ETL definitions.
• Test transformation lists using representative production datasets before deployment.
• Document transformation rules to simplify long-term maintenance and auditing.
• Combine this transform with Transform Text Trim, Transform Text Replace, Validate Data, Filter with Conditions, Group By Transform, Remove Duplicates, JSON Output File, or SQL Output components to create comprehensive enterprise ETL workflows.

Notes:

• Multiple transformation rules can be configured within a single ETL step.
• Source columns must exist within the ETL schema.
• Transformation behavior depends entirely on the configured transformation list.
• Successfully transformed values immediately become available to downstream ETL components.
• Invalid column mappings or transformation configurations may result in ETL execution errors.
• This transform is commonly used for enterprise data standardization, ERP migration, CRM integration, reporting, regulatory compliance, master data preparation, and large-scale ETL processing.
• Review downstream mappings whenever transformation rules are modified to ensure consistency throughout the ETL pipeline.

Transform a List of Column Values vs. Transform Text Replace:

Although both transformations modify data values, they serve different purposes:

As a general guideline:

• Use Transform a List of Column Values when multiple columns require coordinated transformation logic managed through a single configuration.
• Use Transform Text Replace when only specific text values need to be substituted with predefined alternatives.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Transformation list configuration
• Source column mappings
• Transformation rules
• ETL schema validation
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.