Share via

Quote character and escape character in ADF

M, Murugeswari (Cognizant) 461 Reputation points
2023-02-23T05:02:01.5533333+00:00

Hi,

We need to load data from delimited file in storage account to table in dedicated sql pool.. All the record values are enclosed with double quotes (" " ) and column delimiter is pipe (|)

For example :- "abc"|"7809"

Hence, I have set the quote character as double quotes and escape character as backslash

The issue is I am getting wrong record value

Example Source :- ""ABC""""

After loading to dedicated pool, the record shows as ABC"""", but the record should be "AVE""", right.

Please anyone clear me

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. M, Murugeswari (Cognizant) 461 Reputation points
    2023-02-24T16:28:58.0366667+00:00

    Hi @AnnuKumari-MSFT

    Sorry for my confused statement. Let me try to explain clearly

    As said, my source is delimited file and all the record values are enclosed with double quotes (" " ) and column delimiter is pipe (|)

    There I have few records where double quote is present inside the quoted values

    Example :- I""ABC""""|"123" [Here one double quote before ABC and Three double quotes after ABC together all constitutes the value] which is enclosed within double quotes

    Expected Output :- "ABC""" 123

    But I got the result as ABC"""" I believe only double quotes present at start and end of the value should be removed in the sink, How come two Double quotes before ABC is removed

    And I have few varchar columns where I get values for few records like |"""| ( Double quote enclosed within double quotes)--> For these records the pipeline is considering as incompatible rows and rejecting it.

    Could you please clear me

    Was this answer helpful?

    0 comments
  2. AnnuKumari-MSFT 34,571 Reputation points Microsoft Employee Moderator
    2023-02-24T09:41:24.1133333+00:00

    Hi @Anonymous ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your query, you are trying to load the data from .csv file to dedicated sql pool using copy activity in synapse pipeline. However, you were expecting a different result that the actual output. Please let me know if that is not the case.

    First of all, the example you shared is not very clear. you said source has "abc"|"7809" as data but again you said source contains""ABC"""" which is confusing. Kindly share screenshot of source data and target data if possible.

    With the same settings as yours, I tried to load the data to dedicated sql pool , it allowed me to have different values for quote character and escape character only if I enable staging. After enabling staging, I ran the pipeline having copy command as the copy method and same settings for source dataset , it resulted in this output:

    Input: User's image

    Source dataset settings:

    User's image

    Sink table in ded sql pool: User's image

    Hope it helps. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

    Was this answer helpful?

    0 comments

Your answer

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