Explode data from fields

This page explains how to separate data from a field (a cell) into multiple rows when you prepare data in the Wrangler workspace of the Cloud Data Fusion Studio.

Separate delimited text

You can separate the values from a cell into new rows if the values are separated by the following delimiters:

• Comma
• Tab
• Pipe
• Whitespace
• Custom separator

If a cell doesn't contain the chosen delimiter, no new row is inserted.

To split values based on a delimiter, follow these steps:

1. Go to Wrangler workspace in Cloud Data Fusion.
2. On the Data tab, go to a column name and click the arrow_drop_down expander arrow.
3. Click Explode > Delimited text.
4. Choose a delimiter—for example Pipe.

5. Click Extract.

Wrangler splits the fields based on the selected delimiter and adds the split-to-row directive to the recipe. When you run the data pipeline, Cloud Data Fusion applies the transformation to all values in the column.

In this example, a dataset has a column of string values containing the comma delimiter:

ID	Name
1	Lee,Lucian,Luka
2	Mahan,Noam

To divide the value into separate rows, Wrangler deletes the original column and creates a new column with one row for each value. The other column values from the original row are copied into the new rows:

ID	Name_1
1	Lee
1	Lucian
1	Luka
2	Mahan
2	Noam

Separate arrays

The flatten directive separates items in arrays, such as ["ELEMENT_1", "ELEMENT_2", "ELEMENT_3"], into new rows. The other column values from the original record are copied into the new records.

What's next

• Learn more about Wrangler directives.