CODEWITHSUNDEEP
external-tablesazure-synapse
Sandeep T
Sandeep T

Reputation: 441

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

Upvotes: 3

Views: 2638

Answers (2)

Kashyap
Kashyap

Reputation: 17411

CREATE EXTERNAL FILE FORMAT does not support STRING_DELIMITER char within the value of a column.

https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/9882219-fix-string-delimiter-implementation-in-polybase

Upvotes: 0

wBob
wBob

Reputation: 14379

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

Upvotes: 2

Related Questions