Add special character in column name in Azure Data Factory Pipeline

Question

hi team,

I want to add some special character in the column name dynamically depending on the condition. For eg, if the column data contains Euro sign , then I need to add euro sign in that column header only. How can we achieve that?

Answer 1

Hi
Dinesh Prajapati

We can use conditional split to decide if your column contains an Euro symbol or not. So based on data, there will be two flows, one Euro, another Non Euro. You can use the below expression in the Conditional split to have two flows.

instr(amount,'€')>0 For rows with Euro symbol in Amount column instr(amount,'€')==0 For Rows with No Euro symbol in Amount column

In my example below, i had two rows where i had Euro Symbol and one row did not have. Next to conditional split, i used a select transformation to rename column by appending '€'. Please follow below steps

#1: My source has a column Amount, which brings Euro symbol in two rows and dollar symbol in one row. Check Data preview tab

#2. Added Conditional Split to decide which has Euro and which has not

#3. Use select transformation, on top of the conditional split stream Euro and give Amount(Euro) as given below in column name. Here is where we do the alias naming of column

#4. See the data preview in Select, it shows the column name correctly with Euro symbol

#5. In the other stream, all the rows with non Euro are redirected.

#6. Data preview of the other stream has column name Amount (without euro symbol)

This method will work if you have Euro in all the rows or not present in any of the rows as well.

Please feel free to post questions if you have.

Answer 2

Hi Subashri Vasudevan

Thanks for the reply.

But could you please tell me how can I check the special characters for all the column names. It is not possible for me to use instr() function on each column, by mentioning each column name. Is there any way, to achieve this?

Since in my case there are more than 100 columns in each csv and there are more than 30 csv, so setting this for each column, based on column name is not feasible.

Could you please suggest something on this