Question: Can we specify an Escape Character when creating an external table on Azure Synapse Dedicated SQL Pool?
Requirement: There is a need for an external table on a dedicated sql pool to be pointed to a CSV file on ADLS Gen2. The CSV file has the following properties:
Column delimiter (The character used to separate columns in a file): Pipe (|)
Quote character (The character used to quote column values if it contains column delimiter): Double quote (“)
Escape Character (The character used to escape quotes inside a quoted value): Double quote (“)
Based on the above, I was trying to create an external table on Synapse Dedicated pool but I can't find any Microsoft documentation on how to set the property for Escape character. Here below is my external file format set and the table syntax -

CREATE EXTERNAL TABLE [Test].[tblStaging_Page]
(
SessionID [INT] NOT NULL,
Date [Date] NOT NULL,
Article VARCHAR(2000) NULL,
Content VARCHAR(2000) NULL
)
WITH (
LOCATION = '/Test/Inbound/Digital_Test/Page/Landing',
DATA_SOURCE = ADLSGen2TestDataSource,
FILE_FORMAT = DelimitedText_PipeTerminator_QuoteDelimiter_FirstRowAsHeader
);
When I create an external table without specifying an escape character, I get the error 'Could not find a delimiter after string delimiter'. Therefore, I need to specify the Escape character on my external table in order to overcome this issue. Below is the sample data for which I get the error -
SessionID|Date|Article|Content
13354|2023-03-15|"Home | Facebook"|"Content is about 'VVIP"" people"