How to split column value using azure data factory

Question

I have Source csv file, In which there is one column which have multiple values (data sep.by comma (,)) so I want extract that particular one column using data factory and store that multiple records into table (in database) with different column name

Could you please suggest how should I design that azure data factory pipeline ?

Answer 1

You can use the split function in the Data flow Derived Column transformation to split the column into multiple columns and load it to sink database as below.

Source transformation:

Derived Column transformation:

Using the split() function, splitting the column based on delimiter which returns an array.

Derived Column data preview:

Here 2 new columns are added in the derived column which stores the split data from the source column (name).

Select transformation (optional):

In Select transformation, we can remove columns which are not used in sink and only select required columns.

Sink:

Connect sink to the database and map the column to load the data.