Share via

Add special character in column name in Azure Data Factory Pipeline

Dinesh Prajapati 126 Reputation points
Feb 6, 2023, 5:17 PM

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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,936 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Subashri Vasudevan 11,206 Reputation points
    Feb 8, 2023, 2:49 PM

    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

    Screenshot 2023-02-08 at 7.57.40 PM

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

    Screenshot 2023-02-08 at 7.57.55 PM

    #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

    Screenshot 2023-02-08 at 7.58.28 PM

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

    Screenshot 2023-02-08 at 7.59.09 PM

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

    Screenshot 2023-02-08 at 8.00.04 PM

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

    Screenshot 2023-02-08 at 8.16.22 PM

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Dinesh Prajapati 126 Reputation points
    Feb 12, 2023, 6:36 PM

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.