Azure Data Factory: additional column in copy data activity reuse column values in a function?

Question

I am struggling with an Azure Data Factory pipeline action which is a copy data.

I have an input CSV file which I need to copy over to a blob storage as a CSV file but one of the column's values must get URLEncoded.

Problem which I currently have is that I don't seem to have the option to add an additional column in my 'Copy Data'-activity sink where I can reference the column and manipulate the data. I can purely copy paste the data from that column but can't make any adjustments to it.

This is what I am trying to do which is not working:

When I just try to reference the column on its own, it does work:

How can I mix the 2 together so I can manipulate my input data?
I already checked it with a Data Flow but there is no URLEncoding function available there.

Thanks

Answer 1

@Bram Vanhautte

It seems like you're trying to add an additional column in your Azure Data Factory (ADF) Copy Data activity and manipulate the data within that column. Unfortunately, the Copy Data activity doesn't directly support data transformation like URLEncoding. However, you can achieve this by using a combination of activities.

First, use a Lookup activity to read the data from your CSV file. This will allow you to manipulate the data before copying it to the blob storage.

Although you mentioned that Data Flow doesn't have a URLEncoding function, you can use a custom expression to achieve this. You can create a custom function in Data Flow to encode the URL.

In the Copy Data activity, you can add an additional column by using dynamic content. Here's how you can do it:

• In the Source tab of the Copy Data activity, click on "Additional Columns"
• Add a new column and use an expression to reference the column you want to manipulate. For example, you can use @concat('encoded_', item().yourColumnName) to create a new column with the encoded value.
• In the Mapping tab, map the new column to the appropriate column in your sink dataset.

please refer https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview#add-additional-columns-during-copy

Hope this helps. Do let us know if you any further queries.