Description:

The Transform Create Substring step extracts a defined portion of a text value from one or more columns within an ETL dataset. The transformation is configured through the Configure Substring property, where ETL designers define one or more substring extraction rules.

Each rule specifies the source text column, along with substring parameters such as the starting position and the number of characters to extract. The resulting substring may replace the original value or populate a separate destination column, depending on the configured transformation.

Substring extraction is commonly used to isolate business identifiers, prefixes, suffixes, region codes, account segments, document numbers, product categories, or other structured information embedded within larger text values.

Unlike Transform Text Trim, which removes surrounding whitespace, or Transform Using Regular Expression, which performs advanced pattern matching, this step performs straightforward positional extraction based on character locations.

The Transform Create Substring step is typically placed after data cleansing and before validation, grouping, exporting, or loading into downstream systems.

The step supports:

• Fixed-position substring extraction
• Multiple substring transformations
• Text parsing
• Identifier extraction
• Structured text processing
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Extracting customer prefixes
• Parsing account numbers
• Isolating department codes
• Creating shortened identifiers
• Product code processing
• Preparing integration fields
• Enterprise reporting

Usage:

The Transform Create Substring step is typically inserted after importing and cleaning text data, but before validation, filtering, grouping, or exporting.

During execution, each configured transformation extracts the specified section of the source text. The resulting substring becomes available for downstream ETL components and may be used for reporting, integration, filtering, or additional text transformations.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Extracting the first three characters of a department code
• Retrieving country codes from account identifiers
• Creating shortened product identifiers
• Parsing invoice prefixes
• Isolating branch numbers from customer IDs
• Preparing data for external system integrations
• Creating reporting categories from structured codes

After extraction, the substring values can be validated, grouped, sorted, 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 “clsTransformSubstringDef” example.          

• Create a new definition named “clsTransformSubstringDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformSubstring, 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 "clsTransformSubstring" 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 "clsTransformSubstring" 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 source text column from the drop-down list. Enter the delimiter text for the split transformation. Enter the index value. Select the target text column from the drop-down list. You may also type a new Out column name and press Enter to add it. 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 and specify the variable or global reference to hold 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 “clsTransformSubstring” step should generate the configured text substring and write it to the output file. The extracted substring values are now available for downstream validation, reporting, grouping, or integration. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Trim imported text before extracting substrings to ensure character positions remain consistent.
• Use substring extraction when the source data follows a predictable fixed-length format.
• Preserve the original column if downstream systems require both the complete value and the extracted portion.
• Validate extracted values before using them in business rules or database lookups.
• Use meaningful destination column names such as CountryCode, BranchID, or ProductPrefix.
• Test substring configurations using representative datasets to verify character positions and lengths.
• Combine this transform with Transform Text Trim, Validate Data, Filter with Conditions, Group By Transform, Output Column Transform, JSON Output File, or SQL Output components to create comprehensive ETL data processing workflows.

Notes:

• This transformation extracts text based on configured character positions.
• Multiple substring extraction rules can be configured within a single transformation.
• Source text columns must exist within the ETL schema.
• Incorrect start positions or lengths may produce empty or truncated results, depending on the source data.
• Invalid column mappings or transformation configurations may result in ETL execution errors.
• This transform is commonly used for parsing structured identifiers, customer numbers, invoice references, product codes, branch identifiers, and enterprise integration keys.
• Review downstream mappings whenever extracted fields are added to the ETL schema.
• For variable-length or pattern-based extraction requirements, consider using Transform Using Regular Expression instead.

Transform Create Substring vs. Transform Using Regular Expression:

Although both transformations extract information from text, they are intended for different scenarios:

As a general guideline:

• Use Transform Create Substring when data follows a predictable fixed-length structure.
• Use Transform Using Regular Expression when text patterns are variable or require advanced matching and extraction capabilities.

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:

• Substring extraction rules
• Source column mappings
• Character position and length settings
• 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.