Can we specify an Escape Character when creating an external table on Azure Synapse Dedicated SQL Pool?

Vivek Komarla Bhaskar 936 Reputation points
2023-05-30T16:03:23.7466667+00:00

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 -

Screenshot 2023-05-09 at 10.46.13 pm

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"
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,172 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Vinodh247 27,381 Reputation points MVP
    2023-05-31T07:08:05.1066667+00:00

    Hi,

    Thanks for reaching out to Microsoft Q&A.

    See below link, seems this is a limitation in synapse.

    https://stackoverflow.com/questions/68590991/could-not-find-a-delimiter-after-string-delimiter

    https://stackoverflow.com/questions/60813776/azure-synapse-string-delimiter/62680022#62680022

    But these answers are at least a year old, there are chances this might have been changed but I couldn't find the exact mention about this in official ms docs to provide you with.

    Please Upvote and Accept as answer if the reply was helpful, this will be benefitting the other community members who go through the same issue.

    0 comments No comments

Your answer

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