Azure Synapse - String Delimiter

Question

I have a text file with the following format.

"01|""sample""|""Test"|""testing""|""01"|"""".

I have created an external table in Azure Synapse by setting the format option STRING_DELIMITER to '"'. But while processing the file through an sp, i am getting the below-given error.

"Could not find a delimiter after string delimiter"

Is there any solution available for this? Any help would be appreciated.

Regards, Sandeep

Answer 1

In my tests with that sample string, the quotes caused a problem because they are so uneven. You would be better off creating the external table ignoring the quotes and cleaning them afterwards, eg set your external file format like this:

CREATE EXTERNAL FILE FORMAT ff_pipeFileFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = '|',
        --STRING_DELIMITER = '"',  -- removed
        USE_TYPE_DEFAULT = FALSE
        )
);

Clean the quotes out using REPLACE, eg:

SELECT 
    REPLACE( a, '"', '' ) a,
    REPLACE( b, '"', '' ) b,
    REPLACE( c, '"', '' ) c,
    REPLACE( d, '"', '' ) d,
    REPLACE( e, '"', '' ) e,
    REPLACE( f, '"', '' ) f
FROM dbo.yourTable

My results: