Description:

The Transform Split Column Data step divides the contents of a text column into multiple values using a configured delimiter. The transformation is configured through the Configure Split property, where ETL designers define the source column, delimiter, and destination columns that will receive the extracted values.

Many external systems, flat files, spreadsheets, and legacy databases store multiple pieces of information within a single field using separators such as commas, semicolons, pipes (|), tabs, slashes, or hyphens. This transformation separates those values into individual columns, making the data easier to validate, search, filter, group, and load into relational databases.

Unlike Transform Create Substring, which extracts text based on fixed character positions, this step splits data using configurable delimiters, making it ideal for variable-length values with consistent separators.

The Transform Split Column Data step is commonly placed after data cleansing and before validation, filtering, or database loading.

The step supports:

• Delimiter-based text splitting
• Multiple destination columns
• Variable-length field parsing
• Data normalization
• Structured text processing
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Splitting full names into separate fields
• Separating addresses into components
• Parsing delimited product information
• Breaking composite identifiers into segments
• Processing CSV-style embedded values
• Preparing relational database imports
• Enterprise system integration

Usage:

The Transform Split Column Data step is typically inserted after importing and cleansing text data but before validation, grouping, filtering, or loading the dataset into downstream systems.

During execution, each configured source column is parsed using the specified delimiter. The resulting values are assigned to the configured destination columns, making them available for subsequent ETL transformations.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Splitting "John Smith" into FirstName and LastName
• Separating City, State, ZIP into individual columns
• Parsing product category hierarchies
• Breaking account numbers into branch and customer identifiers
• Processing imported CSV values stored within a single database column
• Preparing normalized datasets for reporting
• Loading structured data into relational database tables

The resulting columns can then be validated, searched, 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 “clsTransformSplitColumnDef” example.          

• Create a new ETL definition named “clsTransformSplitColumnDef” and open it in designer mode.
• Drag “clsInputCSV, clsTransformSplitColumn, 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 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 "clsTransformSplitColumn" 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 "clsTransformSplitColumn" step to configure its "Required" properties. Click the 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 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 the Enter key to add it. 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, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformSplitColumn” step should split column data as configured and write it to the output file. The separated values are now available for downstream validation, reporting, database loading, and additional ETL transformations. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Trim text values before splitting to eliminate unnecessary whitespace around delimiters.
• Select delimiters that are consistently used throughout the source dataset.
• Verify that destination columns match the expected number of split values.
• Validate split results before loading them into production databases.
• Preserve the original source column if auditing or troubleshooting may be required.
• Test delimiter configurations using representative production data before deployment.
• Combine this transform with Transform Text Trim, Validate Data, Filter with Conditions, Group By Transform, Rename Column, JSON Output File, or SQL Output components to build comprehensive ETL data preparation workflows.

Notes:

• This transformation separates text values using a configured delimiter rather than fixed character positions.
• Multiple split rules can be configured within a single transformation.
• Source columns must exist within the ETL schema.
• Destination columns are added to the ETL dataset and are available to downstream components.
• Source values containing unexpected delimiters or missing segments may produce incomplete output depending on the configured transformation.
• Invalid column mappings or configuration errors may result in ETL execution errors.
• This transform is commonly used for data normalization, customer information processing, address parsing, product catalog preparation, ERP integration, CRM migration, and enterprise reporting.
• Review downstream mappings whenever additional columns are introduced into the ETL schema.

Transform Split Column Data vs. Transform Create Substring:

Although both transformations extract portions of text, they are designed for different scenarios:

As a general guideline:

• Use Transform Split Column Data when values are separated by known delimiters.
• Use Transform Create Substring when values occupy fixed character positions within the source text.

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:

• Split transformation rules
• Source column mappings
• Delimiter configuration
• Destination 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.