Search code examples
external-tablesazure-synapse

Azure Synapse - String Delimiter

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

Solution

  • 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:

    My results