Unsolving escape character for an external table - Azure Synapse Analytics

Question

Hi,
I've an ADF pipeline that reads a SAP table and then writes to an ADLS gen2 sink in csv format.

The SAP table has an address field having the comma character (",") between the street and
the house number: this comma is a character to consider and it isn't a column delimiter.
So, in ADF for the sink data set I've:
column delimiter = comma;
row delimiter = default;
encoding = default (UTF-8);
escape character = backslash;
quote character = no quote character.

Inside Synapse Analytics (SQL servless pool), in order to create a related external table from
the corresponding ADLS gen2 csv it was created an external file format with these options:
format type = DELIMITEDTEXT;
format options = (FIELD_TERMINATOR = N',', USE_TYPE_DEFAULT = False).

Viewing the data in the SQL external table the data next the address are wrong
because the escape character was bad interpreted: the backslash was interpreted
as a field terminator.

Now, any suggests to me to solve a such issue? Thanks

Answer 1

Hi @pmscorca ,

Thank you for posting query in Microsoft Q&A Platform.

Easiest way to avoid this issue is instead of using comma use some other character(may be | symbol) as Column delimitator in Sink Dataset while writing csv and same column delimitator you can use in your external format settings as well.

Below are few useful resources.

• External Data sources in Synapse
• External File Formats in Synapse
• External table in Synapse

Hope this will help. Please let us know if any further queries.

-------------

Please consider hitting Accept Answer button. Accepted answers helps community as well.

Answer 2

Hi, unfortunately in the syntax of the CREATE EXTERNAL FILE FORMAT it isn't possible to specify any keywords related to escape a character.
So, I've used a tab as a column delimiter, namely a char doesn't used in the text data of the csv. Moreover, I don't use any escape characters
and quote characters.
Unfortunately, it is a workaround solution.

Answer 3

Just for reference if anyone else is struck with this.

In my case changing the settings like below, did the trick.

ADF
column delimiter = comma;
row delimiter = default;
encoding = default (UTF-8);
escape character = Double quote;
quote character = Double quote.

Synapse
PARSER_VERSION='2.0',
HEADER_ROW = true,
FIELDTERMINATOR = ',',
FIELDQUOTE = '"',
ESCAPECHAR ='' (leave it empty or don't even mention this setting)

**In my case I also had to specify the schema for columns as well

Reference https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file