Split Columns in a Data Flow

You can strip out useful data from columns of concatenated data. For example, if a column contains 001011Black, you might split this data into two separate columns, 001011 and Black.

Use the Split Columns step in the data flow editor.

Before you start, turn on Data Preview so that you can see the new columns as you configure the split. If your data source has many columns, use a Select Columns step to remove extraneous columns first to improve the preview.

1. Click Add a step (+), and select Split Columns.
2. Use the options on the Split Columns panel to configure the data flow.

   Field	Description
   Split Column	Click Select Column to specify the data column you'd like to split. If a column is already chosen, click the column name to choose a different column.
   On	Specify whether to split the column by delimiter or by position. Select Delimiter if the column has separator characters, such as commas or spaces. Select Position if the column doesn't have separator characters. If you split on position, you can only create two new columns.
   Delimiter	(Displayed when On is set to Delimiter) Specify the separator used in your data column (for example, space, comma, custom).
   Position	(Displayed when On is set to Position) Specify where the second column starts. For example, if your column contains AABBBCCCDDD, specify 6 to put AABBB in the first column and CCCDDD in the second column.
   Number of parts to create	Specify the number of new columns to create when On is set to Delimiter (you can't change the default value 2 if On is set to Position). For example, if your source data column contains AA BBBBB CCC DD, you might select 4 to put each sub-string into a different column.
   Occurrence	Specify how many of the sub-strings in the source column to include in each new column. Examples based on data AA BBBBB CCC DD with Delimiter set to Space: If you set Occurrence to 1, Number of parts to create to 1, the new column contains AA. If you set the Occurrence to 2, the new column contains AA BBBBB. If you set Occurrence to 1, Number of parts to create to 2, the first new column contains AA and the second new column contains BBBBB CCC DD. If you set Occurrence to 1, Number of parts to create to 4, the first new column contains AA, the second new column contains BBBBB, the third new column contains CCC, and the fourth new column contains DD.
   New column <number> name	Change the default name (New column <number>) for new columns to a more meaningful name. Use the adjacent check box to display or hide new columns.