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

Separate delimited text

You can separate the values from a cell into new rows if the values areseparated 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 theData tab, go to a column name and click thearrow_drop_downexpander arrow.
3. ClickExplode> Delimited text.
4. Choose a delimiter—for examplePipe.

5. ClickExtract.

   Note: If you select Custom separator, define the delimiter with a regular expression.

Wrangler splits the fields based on the selected delimiter and adds thesplit-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 commadelimiter:

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

Separate arrays

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

What's next

• Learn more aboutWrangler directives.