Split Field Component
Split Field Component
Split up the value of a column in an input flow according to a specified delimiter. This delimiter can be any valid character or sequence of characters.
|Name||Text||The descriptive name for the component.|
|Input Column||Select||The input field to split.|
|Delimiter||Text||The delimiter to split on.
Unlike when loading text files, the delimiter is not limited to a single character.
|Output Columns||Position||The Position is the index number of the split field to extract.
Programmers beware: this index starts at 1, not 0
|Output Column||The new field name to hold the extracted data. All target field names are text, but if you are certain they represent, e.g., a Date or Number type, you can use Convert Type as a subsequent component to cast the data type appropriately.|
|Include Input Column||Select||Choose whether or not to keep the original field in the output. If you have extracted all the parts of the field, you may not need the original anymore.|
In this example we have a table that is filled with YouTube video data. One of the fields used contains the datetime data type in spite of the time being redundant since it is always set for midnight. We want to take this field and split it into year, month and day while completely dropping the time section so that the data can be easily transformed later on. To do this, we use a split field component multiple times a shown below.
The properties for the first Split Field component are shown below. In this component, we want to split up the years, months and days which we already know are separated by dashes, so we will set this as our delimiter. This delimiter only acts on the field(s) in the 'Input Column' which will be the StartDate field.
The Output Columns property allows us to define the new fields created by splitting up the old one. This is done by mapping the positions of each value returned by the split to a named field as shown below. Note that the positions start from 1 and not 0.
The original data is shown below. In it, we can see the 'startdate' field as expected.
After passing through the first Split Field component, the data becomes as follows.
Note the new fields that have been created in the process of splitting up 'startdate'. Also note that the day_drop field contains both the day and a redundant timestamp. The second Split Field component is set up similarly but acts on the day_drop field to split by spaces, and maps only the first field ('day') as an output, thus removing the timestamp completely.
This can now be linked to a Table Output that will write the finished data into a table for later use.