Field-to-Column Splitting

Version

Function Overview

The Field-to-Column Splitting operator is added, which can split field values according to specific rules (separators or the number of characters) and generate multiple new columns to store the split values, as shown in the following figure.

Only fields with text-type data can be split.

Scenario Description

You can download the example table: Address1.xls.

The values of the Address field in the data table consist of three parts connected by hyphens (-): country, state, and borough. You can split field values by hyphen (-) to form three new columns: Country, State, and Borough.

Table Data Reading

1. Create a scheduled task, drag a Data Transformation node onto the page, and enter the Data Transformation editing page.

2. Drag a DB Table Input operator onto the Data Transformation page and configure the operator to read the data to be processed, as shown in the following figure.

Field-to-Column Splitting

1. Drag a Field-to-Column Splitting operator onto the Data Transformation page and configure it to split the Address field into multiple columns, as shown in the following figure.

The following table describes the setting items.

Setting Item	Description
Field to Be Split	You can select a field with text-type data from the upstream output for splitting.
Split Method	1. By Character Length You can enter any positive integer (N), and the field values will be split once every N characters. 2. By Separator Available separators include Semicolon, Comma, Colon, Space, and Custom. If you select Custom as Separator, you can input any character or string. The whole input content will be used as the separator to split field values. Note: If you use a special character to split fields, prefix the special character with an escape character (\). The logic of splitting fields by separator is described below. The separator is matched from left to right when splitting field values. The separator is used as the boundary to split fields. If no character is on either side of the separator, the new columns will be filled with null values. Field Value Separator Column After Split A A Empty Empty AAA A Empty Empty Empty Empty AAA AA Empty A
Split Result	For Number of New Columns After Split: You can specify the number of columns to retain. Column counting starts from the first split result. The value must be a positive integer from 1 to 100. The default value is automatically generated based on the first 5,000 data records. If the set value exceeds the actual number of split segments, the field values in the extra columns will be empty in Split Result and NULL in Data Preview. The new columns after the split are named Field name_n (the ordinal number of the split segment). In case of duplicate names, n will be incremented by one until the name becomes unique. The data type of the newly generated fields is text by default.

2. Click Data Preview, as shown in the following figure.

On the Data Preview page, you can preview the first 1000 rows by default, with 20 rows per page.

Field Renaming

Drag a Field Setting operator onto the Data Transformation page. Rename the Address_1, Address_2, and Address_3 fields, as shown in the following figure.

Data Output

1. Add a DB Table Output operator and configure it to output the data after processing, as shown in the following figure.

2. Click Save in the upper right corner.

Effect Display

1. Click Run to execute the task. The running result in Log upon successful execution is shown in the following figure.

2. The table data in the database is shown in the following figure.