Skip to main content

Varicent ELT Help Center

Split columns

Abstract

Use the Split columns tool to remove any columns or rows with multiple values inside.

Sometimes data can have multiple fields are included in the same column. Use the Split columns tool to remove any columns or rows that have multiple values inside the column or row. 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 each 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 big pipe.

If there is extra data, you have the option to leave the excess data in the last column or drop the excess data.

Input

The Split columns tool requires one data input.

Configuration

Use the following configuration options to configure the Split Column tool.

Configuring the Split Columns tool

  1. In your , add your data sources.

  2. Click symon_add_icon.png Tool.

  3. In the search bar, search for the Split Columns tool.

  4. Click + Add Tool.

    Tip

    You can also find the Split Columns tool in the Organize section.

  5. Connect the tool to your data set.

  6. In the configuration pane, enter the following information:

    Table 63. Split Column tool configurations

    Field

    Description

    Split mode

    Toggle between Split to Columns and Split to Rows.

    Split to Columns mode

    Column to Split

    Select the column that you want to split.

    Delimiters

    Enter the delimiter to use:

    Delimiters

    Delimiters continued

    Ampersand &

    Greater than >

    Asterisk *

    Hash tag / Pound #

    At symbol @

    Less than <

    Backslash \

    New line \n

    Caret ^

    Percent %

    Colon :

    Pipe |

    Comma ,

    Plus sign +

    Dash -

    Semicolon ;

    Dollar sign $

    Slash /

    Dot .

    Space \s

    Equal sign =

    Tilde ~

    Exclamation mark !

    Underscore _

    Note

    Specify any whitespace characters with the following characters:

    • Space \s

    • New line \n

    • Tab \t

    Number of new columns

    Enter the number of new columns. The number must be greater than or equal to 2.

    New column name

    Enter the name of the new column. The default name is SplitColumn_1.

    Extra characters

    Select from Leave extra in the last column or Drop extra.

    Split to Rows

    Columns to Split

    Select the column that you want to split.

    Delimiters

    Enter the delimiter to use: 

    Delimiters

    Delimiters continued

    Ampersand &

    Greater than >

    Asterisk *

    Hash tag / Pound #

    At symbol @

    Less than <

    Backslash \

    New line \n

    Caret ^

    Percent %

    Colon :

    Pipe |

    Comma ,

    Plus sign +

    Dash -

    Semicolon ;

    Dollar sign $

    Slash /

    Dot .

    Space \s

    Equal 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:

Table 64. 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:

Table 65. Example output 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.