Description:

The Transform Text Trim step removes unnecessary whitespace characters from the beginning and end of text values. The transformation is configured via the Configure Transform List property, where ETL designers define one or more text-trimming rules.

Whitespace often appears in imported data from spreadsheets, flat files, user-entered forms, legacy applications, or external systems. Although these spaces may not be visible, they can cause validation failures, duplicate records, incorrect comparisons, failed joins, or inconsistent reporting.

The Transform Text Trim step standardizes text values by removing unwanted leading and trailing spaces while preserving the actual text content.

Unlike text replacement or regular expression transformations, this step focuses specifically on eliminating surrounding whitespace without modifying the characters contained within the text.

The Transform Text Trim step is commonly placed near the beginning of an ETL pipeline before validation, filtering, deduplication, or database loading.

The step supports:

• Leading whitespace removal
• Trailing whitespace removal
• Multiple column transformations
• Data cleansing
• Text normalization
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Cleaning imported CSV data
• Preparing data for validation
• Improving database consistency
• Preventing duplicate records
• Standardizing user-entered values
• Preparing integration data
• Enterprise data quality initiatives

Usage:

The Transform Text Trim step is typically inserted immediately after importing data and before validation, comparison, filtering, or database loading.

During execution, each configured text column is processed independently. Leading and trailing whitespace characters are removed, while the remaining text content is preserved.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Cleaning imported customer names
• Standardizing email addresses before validation
• Removing unwanted spaces from product codes
• Preparing values for database lookups
• Cleaning user-entered form data
• Standardizing imported ERP or CRM records
• Preparing datasets for reporting and analytics

After trimming, downstream components can perform accurate comparisons, searches, joins, and validations using standardized values.

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsTransformTrimDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformTrim, 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 "clsTransformTrim" 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 "clsTransformTrim" 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 text column from the drop-down list. Select the trim type from the drop-down list for transformation. 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. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformTrim” step should trim the text as configured and write it to the output file. The unnecessary leading and trailing spaces have been removed while preserving the original text values. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Apply text trimming early in the ETL pipeline before validation or comparison operations.
• Trim text before removing duplicates to prevent records that differ only by whitespace from being treated as unique.
• Clean imported CSV, Excel, and flat-file data before loading it into databases.
• Combine trimming with validation to improve data quality.
• Preserve original columns by duplicating them first if audit or comparison requirements exist.
• Test trimming operations using representative production datasets.
• Combine this transform with Validate Data, Remove Duplicates, Transform Using Regular Expression, Replace Text, Filter with Conditions, JSON Output File, or SQL Output components to create comprehensive data cleansing workflows.

Notes:

• Only leading and trailing whitespace characters are removed.
• Characters within the text itself remain unchanged.
• Multiple trimming rules can be configured within a single transformation.
• Source text columns must exist within the ETL schema.
• Null or empty values are passed through according to the configured ETL processing behavior.
• Invalid column mappings or configuration errors may result in ETL execution errors.
• This transform is commonly used for customer records, product catalogs, financial data, HR systems, CRM integrations, ERP imports, and enterprise data migration.
• Trimming should generally be performed before validation, deduplication, searching, joining, or exporting.

Transform Text Trim vs. Transform Using Regular Expression:

Although both transformations modify text values, they address different requirements:

As a general guideline:

• Use Transform Text Trim when the objective is simply to remove unnecessary whitespace from text values.
• Use Transform Using Regular Expression when advanced text matching, extraction, replacement, or validation is required.

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:

• Text trimming rules
• Source column mappings
• 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.