Description:

The Transform Remove Character step removes one or more configured characters from text values in one or more ETL columns. The transformation is configured through the Configure Character to be Removed property, where ETL designers specify the source column and the characters that should be removed during execution.

Many imported datasets contain unwanted symbols, separators, punctuation, formatting characters, or special characters that interfere with validation, searching, reporting, or database storage. This transformation provides a simple and efficient method for cleansing textual data by removing only the specified characters while leaving all other content unchanged.

Unlike Transform Text Replace, which substitutes one value with another, Transform Remove Character completely removes selected characters from the text.

The Transform Remove Character step is commonly used early in an ETL pipeline after importing data and before validation, filtering, deduplication, or exporting.

The step supports:

• Character removal
• Multiple character removal rules
• Text cleansing
• Data normalization
• Special character removal
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Removing punctuation
• Eliminating formatting symbols
• Cleaning imported identifiers
• Removing separators from account numbers
• Standardizing telephone numbers
• Preparing integration data
• Enterprise data quality initiatives

Usage:

The Transform Remove Character step is typically inserted after importing text data and before validation, filtering, searching, grouping, or loading data into downstream systems.

During execution, each configured source column is scanned for the specified characters. Whenever a matching character is found, it is removed from the text. The cleaned values are then passed to subsequent ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Removing hyphens from account numbers
• Eliminating parentheses from telephone numbers
• Removing commas from numeric values
• Cleaning quotation marks from imported text
• Removing slashes from product codes
• Preparing identifiers for database matching
• Standardizing imported ERP and CRM data

The cleaned 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 “clsTransformRemoveCharacterDef” example.          

• Create a new definition named “clsTransformRemoveCharacterDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformRemoveCharacter, 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 "clsTransformRemoveCharacter" 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 "clsTransformRemoveCharacter" 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 “characters text” to remove during the transformation. 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 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 “clsTransformRemoveCharacter” step should remove the characters as configured and write the output to the file. The unwanted formatting characters have been removed, producing standardized values for downstream processing. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Trim text values before removing characters to eliminate unnecessary leading and trailing spaces.
• Remove formatting characters before validation or duplicate detection.
• Preserve the original source column by duplicating it if audit or comparison requirements exist.
• Verify that removing characters does not alter business-critical identifiers.
• Test removal rules using representative production datasets before deployment.
• Document character removal rules to maintain consistency across ETL definitions.
• Combine this transform with Transform Text Trim, Transform Text Replace, Validate Data, Remove Duplicates, Filter with Conditions, JSON Output File, or SQL Output components to build comprehensive ETL data cleansing workflows.

Notes:

• Only the configured characters are removed; all remaining text is preserved.
• Multiple character removal rules can be configured within a single transformation.
• Source text columns must exist within the ETL schema.
• Values that do not contain the configured characters remain unchanged.
• Invalid column mappings or configuration errors may result in ETL execution errors.
• This transform is commonly used for customer data normalization, product code cleanup, financial data processing, ERP migration, CRM integration, reporting, and enterprise data quality initiatives.
• Review downstream validation and matching logic whenever character removal rules are modified.
• For replacing characters with alternative values rather than removing them entirely, consider using Transform Text Replace.

Transform Remove Character vs. Transform Text Replace:

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

As a general guideline:

• Use Transform Remove Character when unwanted symbols or formatting characters should be deleted.
• Use Transform Text Replace when existing text should be substituted with standardized or corrected values.

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:

• Character removal rules
• Source column mappings
• Character selection
• 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.