Split columns
Sometimes, data can include multiple fields in the same column. Use the Split columns tool to remove any columns or rows with multiple values inside them. Define the number of columns or rows to output and use a delimiter to split up the data. By using the Split columns tool, the data will have its own column or row.
The Split columns tool only works on text columns. You can use this tool in either the standard pipe or the big pipe.
If there is extra data, you can leave it in the last column or delete it.
Input
The Split columns tool requires one data input.
Configuration
Use the following configuration options to configure the Split Column tool.
Go to the Pipes module from the side navigation bar.
From the Pipes tab, click an existing pipe to open, or create a new pipe. To create a new pipe, read the Creating a pipe documentation.
In the Pipe builder, add a data source to your pipe. For more information on adding a data source, see the Data Input tool.
Click
Tool.The Tools modal opens, where you can add tools, such as the Aggregate tool, to your pipe.
In the Tools modal, search for the Split Columns tool.
Tip
You can also find the Split Columns tool in the Organize section.
Click the tool node and drag the line to the next tool to connect the tools. If you need to undo the action, click the line and then click Unlink.
In the configuration pane, under Split mode, switch the toggle to choose between Split to Columns or Split to Rows
In the Split to Columns mode, under Columns to Split, select the column that you want to split.
Under Delimiters, enter the delimiter you want to use:
Delimiters
Delimiters continued
Ampersand
&Greater than
>Asterisk
*Hash tag / Pound
#At symbol
@Less than
<Backslash
\New line
\nCaret
^Percent
%Colon
:Pipe
|Comma
,Plus sign
+Dash
-Semicolon
;Dollar sign
$Slash
/Dot
.Space
\sEqual sign
=Tilde
~Exclamation mark
!Underscore
_Note
Specify any whitespace characters with the following characters:
Space
\sNew line
\nTab
\t
Under Number of new columns, enter the number of new columns. The number must be greater than or equal to 2.
Under Extra characters, select from Leave extra in the last column or Drop extra.
In the Split mode, under the Split to Rows mode, under Columns to Split, select the column that you want to split.
Under Delimiters, enter the delimiter to use:
Delimiters
Delimiters continued
Ampersand
&Greater than
>Asterisk
*Hash tag / Pound
#At symbol
@Less than
<Backslash
\New line
\nCaret
^Percent
%Colon
:Pipe
|Comma
,Plus sign
+Dash
-Semicolon
;Dollar sign
$Slash
/Dot
.Space
\sEqual sign
=Tilde
~Exclamation mark
!Underscore
_
Usage example
For example, you have some data that has multiple values in a single column. You want to split it out so the values are in their own column.
Example input data:
Payee | Customer Data |
|---|---|
ABC | John, Doe, 30 |
DEF | Alice, Smith |
When you use the Split columns tool, the following table shows the example output of the data:
Payee | Customer Data | SplitColumn_1 | SplitColumn_2 | SplitColumn_3 |
|---|---|---|---|---|
ABC | John, Doe, 30 | John | Doe | 30 |
DEF | Alice, Smith | Alice | Smith | Null |
Note
If a column has a null value, as we see in the Example output data table, the column is still created for the null value. The same behaviour occurs if you are splitting rows.