Error with No Escape Character in Mapping Data Flow in Data Factory

Balogh, Beatrix 21 Reputation points
2020-07-16T21:19:25.377+00:00

We have bad data from a source that is unlikely to update it on their end. They have multiple columns where the value in the column is 01F\ or 8239\ and the backslash is written in their spec to be part of the value, not to be considered an escape character like it is standardized to be in the rest of the world.

The overall set up of the files are that they are comma delimited, each column's contents is in " ", and we have all of the normal new line characters. It's just the backslash that is not complying with standards. E.g.

"Column 1","Column 2","Column 3","Column 4"  
"John","01F\","34","NY"  
"Jane","3K","8239\","CA"  

|---------------------|------------------|------------------|------------------|  
|      Column 1       |     Column 2     |     Column 3     |     Column 4     |  
|---------------------|------------------|------------------|------------------|  
|        "John"       |       "01F\"     |       "34"       |         "NY"     |  
|---------------------|------------------|------------------|------------------|  
|        "Jane"       |        "3K"      |      "8239\"     |         "CA"     |  
|---------------------|------------------|------------------|------------------|  

In Azure Data Factory we are trying to see if we can make it ignore the \ as being an escape character. (FYI when we leave it as being treated as an escape character, it pulls the column right after the column with the backslash into one column). We can see in the data set where to set it so that there is no escape character.

12687-image.png

However when we then add that data set to our data flow and attempt to preview the data there, we get an error that we can't have no escape character in the data flow, and that quote character should be no quote character when we have no escape character.

12660-image.png

If we try to go back and also set no quote characters (which we don't actually want to do, just to test if that'll let it work), we get an error that the data flow can't have no escape or quote characters.

12791-image.png

Are these two options supposed to work in Azure Data Factory? Or is there somewhere else we need to update additional settings to make this work?

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

Accepted answer
  1. KranthiPakala-MSFT 46,502 Reputation points Microsoft Employee
    2020-07-21T00:30:02.79+00:00

    Hi @BaloghBeatrix-5038,

    Welcome to Microsoft Q&A platform and thank you for your query.

    Could you please try to update your dataset configuration as below :

    13204-backslashescape.png

    And then try to use the dataset in dataflow, use replace function derive column transformation to remove backslash from the column as below (sample)

    Example Function to use - replace(Column3, '\\', '')

    13182-replacefunction.png

    13221-derivedcolumnreplace.png

    This "replace" function can be used in a derived column transformation to achieve your requirement.

    Hope this helps. Do let us know how it goes.


    Thank you
    Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Venkatesh 1 Reputation point
    2020-12-29T11:47:49+00:00

    Hi,

    i'm facing same issue, have tried with same settings but no use.

    My data is like..

    Column1
    "abc" def
    "ghi" jkl

    Data generated in csv:

    Column1
    "\"abc\" def"
    "\"ghi\" jkl"

    Note: i'm using Common data model and generating csv file.

    Please help.

    51910-dataset-settings.png

    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.