Quote character and escape character in ADF

Question

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

Answer 1

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:

Source dataset settings:

Sink table in ded sql pool:

---

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'

Answer 2

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