Description:

The Transform Text Replace step searches for specified text within one or more source columns and replaces matching occurrences with configurable replacement values. The transformation is configured through the Configure Find and Replace Text property, where ETL designers define one or more find-and-replace rules.

Many imported datasets contain inconsistent abbreviations, outdated terminology, unwanted characters, legacy codes, or formatting differences. This transformation provides an efficient way to normalize these values without requiring complex scripting or custom code.

The Transform Text Replace step is commonly used to standardize imported data before validation, filtering, reporting, or loading into downstream systems.

Unlike Transform Text Trim, which removes only leading and trailing whitespace, or Transform Using Regular Expression, which supports advanced pattern matching, this step performs straightforward text substitution based on configured search and replacement values.

The step supports:

• Text search and replacement
• Multiple replacement rules
• Value standardization
• Data cleansing
• Text normalization
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Correcting spelling inconsistencies
• Expanding abbreviations
• Standardizing department names
• Removing unwanted characters
• Updating legacy codes
• Preparing integration data
• Enterprise data quality initiatives

Usage:

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

During execution, each configured source column is examined for matching text values. Whenever a configured match is found, the specified replacement text is substituted, producing standardized output for downstream ETL processing.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Replacing "USA" with "United States"
• Updating obsolete product codes
• Standardizing department abbreviations
• Removing unwanted punctuation
• Correcting imported spelling variations
• Replacing legacy application codes
• Preparing normalized values for reporting and database loading

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

• Create a new definition named “clsTransformReplaceTextDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformReplaceText, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global 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 "clsTransformReplaceText" 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 "clsTransformReplaceText" step to configure its "Required" properties. Click the button to open the transform list configuration window. Click the Add Row (+) button to insert an empty row. Select the text column 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 “clsTransformReplaceText” step should replace the text as configured in the output file. The updated values are now standardized and available for downstream validation, reporting, filtering, and integration. The variable or global holds the virtual path of the output file, as shown below. 

Tips:

• Trim imported text before performing replacements to ensure reliable matching.
• Standardize replacement rules across ETL definitions to maintain consistent business terminology.
• Preserve original values by duplicating the source column when audit or comparison requirements exist.
• Test replacement rules using representative production datasets to verify expected results.
• Be cautious when replacing short or commonly occurring text that could unintentionally affect unrelated values.
• Apply replacement operations before validation or database lookups to improve matching accuracy.
• Combine this transform with Transform Text Trim, Validate Data, Remove Duplicates, Filter with Conditions, Rename Column, JSON Output File, or SQL Output components to build comprehensive ETL data cleansing workflows.

Notes:

• Multiple find-and-replace rules can be configured within a single transformation.
• Source text columns must exist within the ETL schema.
• Replacement operations affect only configured columns.
• If the search text is not found, the original value remains unchanged.
• Invalid column mappings or configuration errors may result in ETL execution errors.
• This transform is commonly used for customer data standardization, ERP migration, CRM integration, reporting, regulatory compliance, product catalog normalization, and enterprise data quality initiatives.
• Review downstream validation and reporting logic whenever replacement rules are modified.
• For advanced pattern-based replacements or conditional matching, consider using Transform Using Regular Expression.

Transform Text Replace vs. Transform Using Regular Expression:

Although both transformations modify text values, they are designed for different use cases:

As a general guideline:

• Use Transform Text Replace when replacing known text values with standardized alternatives.
• Use Transform Using Regular Expression when matching or replacing variable text patterns that cannot be handled by exact text matching.

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:

• Find-and-replace rules
• Source column mappings
• Search and replacement values
• 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.