Description:

The Transform Text Replace with Operator step replaces text within one or more source columns using configurable replacement operators. The transformation is configured through the Configure Transform List property, where ETL designers define one or more replacement operations.

Unlike a simple find-and-replace transformation, this step supports configurable replacement operators, allowing replacement behavior to be driven by the selected operator and its associated configuration. This provides additional flexibility when processing imported or generated text values that require more structured replacement logic.

The transformation is commonly used to normalize imported data, modify standardized values, remove unwanted text fragments, replace prefixes or suffixes, update legacy values, and prepare information for downstream processing.

The Transform Text Replace with Operator step is typically placed after text cleansing operations such as trimming and before validation, grouping, reporting, or exporting.

The step supports:

• Operator-based text replacement
• Multiple transformation rules
• Text normalization
• Data cleansing
• Structured replacement logic
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Updating business terminology
• Standardizing imported values
• Cleaning legacy application data
• Modifying product identifiers
• Preparing integration fields
• Improving reporting consistency
• Enterprise data quality initiatives

Usage:

The Transform Text Replace with Operator step is typically inserted after importing and cleaning text data but before validation, filtering, grouping, or exporting.

During execution, each configured transformation evaluates the selected replacement operator and applies the configured replacement logic to the specified source column. The resulting values become available for downstream ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Standardizing imported business terminology
• Replacing legacy product identifiers
• Updating department naming conventions
• Removing obsolete text fragments
• Converting imported values into enterprise standards
• Preparing normalized values for downstream integrations
• Cleaning text before reporting or database loading

The transformed values can then be validated, filtered, grouped, 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 “clsTransformReplaceDef” example.          

• Create a new definition named “clsTransformReplaceDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformReplace, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global variable required 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 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 "clsTransformReplace" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsTransformReplace" 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. Select the operator (contains, starts with, ends with) from the drop-down list. Enter the “find word” and “replace word” in the transformation columns. Select the checkbox to replace the field value. Click the Save button. You may add multiple transformation columns by 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. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformReplace” step should replace the text with an operator as configured in the output file. The updated values are now standardized for downstream validation, reporting, and database loading. The variable or global holds the virtual path of the output file, as shown below. 

Tips:

• Trim imported text before applying replacement operators to ensure consistent matching.
• Use operator-based replacement when multiple replacement behaviors are available through the transformation configuration.
• Preserve original values by duplicating the source column if auditing or comparison is required.
• Test replacement configurations using representative production datasets before deployment.
• Document replacement rules so they remain consistent across ETL definitions.
• Review downstream validation rules after modifying business terminology or identifiers.
• Combine this transform with Transform Text Trim, Validate Data, Remove Duplicates, Filter with Conditions, Rename Column, JSON Output File, or SQL Output components to create comprehensive ETL data cleansing workflows.

Notes:

• Replacement behavior is determined by the configured replacement operator.
• Multiple replacement rules can be configured within a single transformation.
• Source text columns must exist within the ETL schema.
• Values that do not satisfy the configured replacement criteria remain unchanged.
• Invalid transformation rules or column mappings may result in ETL execution errors.
• This transform is commonly used for customer data standardization, ERP migration, CRM integration, reporting, product catalog normalization, and enterprise data quality initiatives.
• Review downstream mappings whenever replacement logic is modified.
• For advanced pattern matching or expression-based replacement, consider using Transform Using Regular Expression.

Transform Text Replace with Operator vs. Transform Text Replace:

Although both transformations perform text replacement, they are intended for different scenarios:

As a general guideline:

• Use Transform Text Replace with Operator when replacement behavior should be controlled by configurable operators or transformation rules.
• Use Transform Text Replace when replacing known text values with predefined alternatives using simple find-and-replace logic.

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:

• Replacement transformation rules
• Source column mappings
• Replacement operator configuration
• 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.