Split Transform

split col: MyValues on: ',' limit: 3

Output: Splits the source MyValues column into four separate columns. Values in the columns are determined based on the comma (,) delimiter. If a row only has two commas in it, then the final generated column is null.

split col:column_ref [quote:'quoted_string'] [ignoreCase:true|false] [limit:int_num] [after:start_point | from: start_point] [before:end_point | to:end_point] [on:'exact_match'] [at:(start_index,end_index)] [delimiters:'string1','string2', 'string3'] [positions: int1,int2,int3] [every:int_num]

For more information on syntax standards, see Language Documentation Syntax Notes.

The split transform supports the following general methods for specifying the delimiters by which to split the column. Depending on your use of the transform, different sets of parameters apply.

Shared parameters:

The following parameters are shared between the operating modes:

Single-pattern delimiter parameters:

Multi-pattern delimiter parameters:

Identifies the column to which to apply the transform. You can specify only one column.

split col: MyCol on: 'MyString'

Output: Splits the MyCol column into two separate columns whose values are to the left and right of the MyString value in each cell.

Usage Notes:

Indicates whether the match should ignore case or not.

split col: MyCol on: 'My String' ignoreCase: true

Output: Splits the MyCol column on case-insensitive versions of the on parameter value, if they appear in cell values: My String, my string, My string, etc.

Usage Notes:

The limit parameter defines the maximum number of times that a pattern can be matched within a column.

A set of new columns is generated, as defined by the limit parameter. Each matched instance populates a separate column, until there are no more matches or all of the limit-generated new columns are filled.

split col: MyCol on: 'z' limit: 3

Output: Splits the MyCol column on each instance of the letter z, generating 4 new columns. If there are fewer than 3 instances of z in a cell, the corresponding columns after the split are blank.

Usage Notes:

A pattern identifier that precedes the value or pattern to match. Define the after parameter value using string literals, regular expressions, or Wrangle .

split col: MyCol after: '\' before:'|'

Output: Splits values in MyCol based on value between the two characters. The first column contains the part of the MyCol that appears before the backslash (\), and the second column contains the part of MyCol that appears after the pipe character (|). The content between the delimiting characters is dropped.

Usage Notes:

Identifies the start and end point of the pattern to interest.

Parameter inputs are in the form of x,y where x and y are positive integers indicating the starting character and ending character, respectively, of the pattern of interest.

split col: MyCol at: 2,6

Output: Splits the MyCol column on the value that begins at the second character in the column and extends to the sixth character of the column. Contents before the value are in the first column, and contents after the value are in the second column.

Usage Notes:

The at parameter cannot be combined with any of the following: on, after, before, from, to, and quote. See Pattern Clause Position Matching.

A pattern identifier that occurs after the value or pattern to match. Define the pattern using string literals, regular expressions, or Wrangle .

split col: MyCol before: '/' from:'Go:'

Output: Splits contents of MyCol into two columns. The first column contains the values that appear before the Go: string, and the second column contains the values after the backslash.

Usage Notes:

Identifies the pattern that marks the beginning of the value to match. It can be a string literal, Wrangle , or regular expression. The from value is included in the match.

split col: MyCol from: 'go:' to:'stop:'

Output: Splits contents of MyCol from go:, including go: to stop:, including stop:. Contents before the string appear in the first column, contents after the string appear in the second one.

Usage Notes:

Identifies the pattern to match, which can be a string literal, Wrangle , or regular expression.

If the value does not appear in the source column, the original value is written to the first column of the split columns.

split col: MyCol on: `###ERROR`

Output: Column into two columns. The first column contains values in the column appearing before ###ERROR, and the second column contains the values appearing after this string.

Usage Notes:

Identifies the pattern that marks the ending of the value to match. Pattern can be a string literal, Wrangle , or regular expression. The to value is included in the match.

split col:MyCol from:'note:' to: ` `

Output: Splits MyCol column all contents that appear before note: in the first column and all contents that appear after the first space after note: in the second column.

Usage Notes:

Can be used to specify a string as a single quoted object. This parameter value can be one or more characters.

split col: MyLog on: `|` limit:10 quote: '"'

Output: Splits the MyLog column, on the pipe character (|), while ignoring any pipe characters that are found between double-quote characters in the column. Based on the value in the limit parameter, the transform is limited to creating a maximum of 10 splits.

Usage Notes:

The delimiters parameter specifies a comma-separated list of string literals or patterns to identify the delimiters to use to split the data. Values can be string literals, regular expressions, or Wrangle .

split col:myCol delimiters:'|',' ','|'

Output: Splits the myCol column into four separate columns, as indicated by the sequence of delimiters.

Usage Notes:

The positions parameter specifies a comma-separated list of integers that identify zero-based character index values at which to split the column. Values must be Integers.

split col:myCol positions:20,55,80

Output: Splits the myCol column into four separate columns, where:

Usage Notes:

The every parameter can be used to specify fixed-width splitting of the source column. This Integer value defines the number of characters in each column of the split output.

If needed, you can use the every parameter with the limit parameter to define the maximum number of output columns:

split col:myCol every:20 limit:5

Output: Splits the myCol column every 20 characters, with a limit of five splits. The sixth column contains all characters after the 100th character in the cell value.

Usage Notes:

When you build or edit a split transform step in the Transform Builder, you can select one of the following pattern groups to apply to your transform. A pattern group is a set of related patterns that define a method of matching in a cell's data. Some pattern groups apply to multiple transforms, and some apply to the split transform only.

Source:

Transformation:

ColA: You can use the following transformation to split on the variations of My String: In this case, the ignoreCase parameter ensures that all variations on capitalization are matched:

ColA

On pattern

'My String'

true

ColB: For this column, the letter x is the split marker, and the data is consistently formatted with three instances per row:

ColB

On pattern

'X'

3

ColC: In this column, the double-letter marker varies between the rows. However, it is consistently in the same location in each row:

ColC

Sequence of positions

2,4

Results:

When the above transforms are added, the source columns are dropped, leaving the following columns:

This example demonstrates how to use quote parameter for more sophisticated splitting of columns of data using the split transform.

Source:

In this example, the following CSV data, which contains contact information, is imported into the application:

LastName,FirstName,Role,Company,Address,Status
Wagner,Melody,VP of Engineering,Example.com,"123 Main Street, Oakland, CA 94601",Prospect
Gruber,Hans,"Director, IT",Example.com,"456 Broadway, Burlingame, CA, 94401",Customer
Franks,Mandy,"Sr. Manager, Analytics",Tricorp,"789 Market Street, San Francisco, CA, 94105",Customer

Transformationn:

When this data is pulled into the application, some initial parsing is performed for you:

When you open the Recipe Panel, you should see the following transforms:

column1

\n

\"

\"

column1

On pattern

','

5

\"

The first transform splits the raw source data into separate rows in the carriage return character (\r), ignoring all values between the double-quote characters. Note that this value must be escaped. The double-quote character does not require escaping. While there are no carriage returns within the actual data, the application recognizes that these double-quotes are identifying single values and adds the quote value.

The second transform splits each row of data into separate columns. Since it is comma-separated data, the application recognizes that this value is the column delimiter, so the on value is set to the comma character (,). In this case, the quoting is necessary, as there are commas in the values in column4 and column6, which are easy to clean up.

To finish clean up of the dataset, you can promote the first row to be your column headers:

Use row(s) as column names

Use a single row to name columns

1

You can remove the quotes now. Note that the following applies to two columns:

Address,Role

'\"'

''

true

Now, you can split up the Address column. You can highlight one of the commas and the space after it in the column, but make sure that your final statement looks like the following:

column1

On pattern

','

2

Notice that there is some dirtiness to the resulting Address3 column:

Use the following to remove the comma. In this case, it's important to leave the space between the two values in the column, so the on value should only be a comma. Below, the width value is two single quotes:

Address3

','

''

true

You can now split the Address3 column on the space delimiter:

Address3

by delimiter

' '

2

Results:

After you rename the columns, you should see the following:

This example shows how you can split data from a single column into multiple columns using delimiters.

Source:

In this example, your CSV dataset contains status messages from a set of servers. In this case, the data about the server and the timestamp is contained in a single value within the CSV.

Server|Date Time,Status
admin.examplecom|2016-03-05 07:04:00,down
webapp.examplecom|2016-03-05 07:04:00,ok
admin.examplecom|2016-03-05 07:04:30,rebooting
webapp.examplecom|2016-03-05 07:04:00,ok
admin.examplecom|2016-03-05 07:05:00,ok
webapp.examplecom|2016-03-05 07:05:00,ok

Transformation:

When the data is first loaded into the Transformer page, the CSV data is split using the following two transformations:

column1

\n

column1

On pattern

','

\"

You might need to add a header as the first step:

Use row(s) as column names

Use a single row to name columns

1

At this point, your data should look like the following:

The first column contains three distinct sets of data: the server name, the date, and the time. Note that the delimiters between these fields are different, so you should use a multi-pattern delimiter to break them apart:

Server|Date Time

Sequence of patterns

','

' '

When the above is added, you should see three separate columns with the individual fields of information. Note that the source column has been automatically dropped.

Now, you decide that it would be useful to break apart the date information column into separate columns for year, month, and day. Since the column delimiter of this field is consistently a dash (-), you can use a single-pattern delimiter with the following transformation:

Server|Date Time2

By delimiter

'-'

2

Results:

After you rename the generated columns, your dataset should look like the following. Note that the source timestamp column has been automatically dropped.