How to remove or replace "\" from the output file generated in Synapse (using copy stge)

Karuna 0
Viewed 71

I tried converting data in table to a file and able to achieve. But, in the output file generated,there is "\" comming in. Can you please help how to eliminate or remove "\" from file. Note: Have used Copy stage to move data from oracle sql table to storage account.

Output:

"\"we16\",\"ACTIVE\",\"we16\",01-JAN-22,\"Tester\""

"\"sb64\",\"ACTIVE\",\"sb64\",01-JAN-22,\"\"Operations"

"\"sb47\",\"ACTIVE\",\"sb47\",01-JAN-22,\"\"Developer"

"\"ud53\",\"ACTIVE\",\"ud53\",01-JAN-22,\"\"Manager"

"\"hk72\",\"ACTIVE\",\"hk72\",01-JAN-22,\"\"Tester"

"\"sk99\",\"ACTIVE\",\"sk99\",01-JAN-22,\"\"Tester"

enter image description here

enter image description here

azure-synapse azure
1 Answers

I have reproduced the above with SQL as source and Blob as sink in copy activity.

This is my sample SQL data with Double Quotes on a column.

enter image description here

sink dataset:

enter image description here

When I set the Escape character to Backslash and Quote character to Double quote("), I got the same result as above.

enter image description here

In the above we have the data with double quotes and our Quote character also a double Quote, so that's why it is escaping the extra double quotes with the Escaping character.

As our table has values with double quotes, we need to set the Quote characters to any other values apart from double quotes. So, when I have set it to single quotes('), I got proper result.

enter image description here

Result data of double quotes with single quotes as Quote characters:

enter image description here

When you are using this dataset in synapse or ADF, set it to the single Quote so that you can use the correct data.

Rakesh Govindula0
Related