Concatenate values from 2 or more columns in a csv to single column in Azure Data Factory

1
votes

In Azure Data Factory v2 (via the Azure Portal), I'm using a csv file as my source file, and an Azure SQL Server table as the destination. How in ADF can I concatenate the values of 3 columns from the csv file into one field in the database table? It's only giving me options for 1-to-1 column mapping. Is a Data Flow element needed for this, or can it all be done inside a Copy Data pipeline?

FYI: this is not the answer to my question: Azure Data Factory mapping 2 columns in one column

1
azure-data-factory

1 Answers

2
votes

Azure Data Flow Derived Column can help you concatenate the values of 3 columns from the csv file into one field in the database table.

You can reference my example.

My CSV data:

enter image description here

Create a mapping Data Flow in pipeline: set the csv file as Source+ DerivedColun+ Sink:

enter image description here

Derived Column: create a Visual Expression to concatenate the values of 3 columns from the csv file. enter image description here

I concatenate 'b','c','d' columns to 'b': enter image description here

Sink Mapping settings: choose the Derived Column instead of the column b enter image description here

Data Preivew: check the data will insert to my SQL database, and run the pipeline.

enter image description here

Hope this helps.