User Tools

Site Tools



Category: Transform / Advanced


This action splits column values delimited with a separator (e.g. comma) into multiple rows – one row per value.

Values in other columns are duplicated in the new rows.

Use cases

This action can be useful in cases where individual values are concatenated, with a delimiter, in a single column that should be broken out into individual records.

Action settings

ColumnSelect the column containing the delimited text to be split.
SeparatorSelect the delimiting character to split the text by. Options: Comma, Semicolon, Tab, Pipe, Tilde, Space,Line break, and
Custom (and select the character from the dialog that displays).
Ignore quoting When selected, delimiting characters contained within paired quotation marks will be used for splitting text.


Unlike the Split delimited text into columns action, in which you have to define the new columns to receive the split values, this action will create as many new rows as necessary to output all split values in the selected column.

Leading and trailing whitespace are automatically removed from values when the new columns are created.

Import 2 to 3 times faster (experimental feature). This feature affects the "Split delimited text…" actions as well. To enable this feature, check on the "Twice faster CSV import" option in Application Settings (in the About toolbar).


Example 1: A value with 3 words, delimited with semicolons, will be split into three rows:

Source table:

Row no Numbers

Action parameters:

Column: "Numbers"
Delimiter: Semicolon

Result table:

Row no Numbers

Example 2: A column with multi-line text values.

Source table:

Location No Location
1 Mississauga
North America
2 Cambridge
United States
North America

Action parameters:

Column: "Location"
Delimiter: Line break

Result table:

Location No Location
1 Mississauga
1 Ontario
1 Canada
1 North America
2 Cambridge
2 Massachusetts
2 United States
2 North America

Example 3: Split out Order IDs into rows to create unique Customer-Order ID records.

Source table:

Customer IDOrder IDs
CUST001 ORD001, ORD005, ORD010
CUST002 ORD002, ORD003, ORD009
CUST003 ORD004, ORD006, ORD007, ORD008

Action parameters:

Column: "Order IDs"
Separator: Comma

Result table:

Customer IDOrder IDs
CUST001 ORD001
CUST001 ORD005
CUST001 ORD010
CUST002 ORD002
CUST002 ORD003
CUST002 ORD009
CUST003 ORD004
CUST003 ORD006
CUST003 ORD007
CUST003 ORD008

Community examples

See also

transformations/subfield.txt · Last modified: 2023/10/11 20:54 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki