Description:

The Transform Using Regular Expression step applies one or more regular expression (RegEx) transformations to fields within an ETL dataset.

This transformation enables designers to configure pattern-based operations that manipulate text values during ETL execution. Regular expressions can be used to extract specific portions of text, replace matching values, standardize formats, remove unwanted characters, mask sensitive information, or normalize imported data before it is processed by downstream ETL components.

The transformation rules are configured through the Configure Fields property, where one or more column mappings and associated regular expression operations can be defined.

The Transform Using Regular Expression step is commonly used after data import and before validation, aggregation, or loading into destination systems.

The step supports:

• Regular expression transformations
• Pattern matching
• Text replacement
• Text extraction
• Data cleansing
• Data normalization
• Multiple field transformations
• Integration with FlowWright ETL pipelines

This step can be used for:

• Cleaning imported data
• Standardizing customer information
• Removing unwanted characters
• Formatting phone numbers
• Normalizing identifiers
• Preparing data for reporting
• Enterprise ETL automation

Usage:

The Transform Using Regular Expression step is typically placed after importing data but before validation, aggregation, or destination components.

During execution, each configured transformation evaluates the specified column values using the configured regular expression pattern and applies the requested modification. The transformed values are then passed to downstream ETL components.

A typical ETL pipeline might look like this:

Typical transformation scenarios include:

• Removing special characters
• Standardizing phone number formats
• Extracting postal codes
• Masking confidential information
• Removing extra whitespace
• Formatting account numbers
• Normalizing imported text values

The transformed dataset can then be validated, aggregated, or exported to destination systems.

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsTransformUsingRegularExpressionDef” and open it in designer mode.
• Drag the “clsInputCSV, clsTransformUsingRegularExpression, 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 "clsTransformUsingRegularExpression" 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 "clsTransformUsingRegularExpression" step to configure its "Required" properties. Click the column button to open the transform list. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the column from the drop-down list. Enter the regular expression to apply to this column. Enter the “match” and “no-match” values. Click the Save button. You may add multiple columns for transformation 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. Execute the ETL process. During execution, each configured regular expression transformation is applied to the selected fields. The transformed values are then made available to downstream ETL components for validation, reporting, aggregation, or export.

Tips:

• Test regular expression patterns using representative sample data before deploying to production.
• Keep transformation rules simple and maintainable whenever possible.
• Use regular expressions to normalize inconsistent imported data before validation.
• Apply transformations before performing data type validation or loading data into destination systems.
• Document complex regular expression rules for future maintenance.
• Combine multiple transformation rules in a single step to simplify ETL pipelines.
• Use this transform together with JSON Input File, CSV Input, Validate Data, Filter Transform, Derived Column, Aggregate Transform, or SQL Output components to build comprehensive ETL workflows.

Notes:

• Regular expression transformations are applied to each processed record during ETL execution.
• Multiple field transformations can be configured within a single step.
• Invalid or improperly constructed regular expression patterns may result in ETL execution errors.
• Transformation rules modify the processed dataset but do not alter the original source data.
• Complex regular expressions may affect ETL performance when processing very large datasets.
• Verify transformed output before loading data into production systems.
• This transform is commonly used for data cleansing, migration, customer master management, reporting, integration, and enterprise data quality initiatives.
• Proper testing should be performed whenever transformation rules are modified.

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:

• Regular expression transformation rules
• Source column mappings
• Destination field mappings
• ETL schema configuration
• Downstream transformation connections
• Environment-specific configuration

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

Click here to download the sample file.