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

Please post your full `CREATE EXTERNAL FILE FORMAT` and `CREATE EXTERNAL TABLE` commands. — wBob, Mar 23 '20 at 18:49

score 2 · Accepted Answer · answered Mar 23 '20 at 19:45

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:

This assumes that none of the values contain field separator char (`|`). — Kashyap, Jul 01 '20 at 15:17

score 0 · Answer 2 · answered Jul 01 '20 at 15:17

0

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

answered Jul 01 '20 at 15:17

Kashyap

15,354
13
64
103

Azure Synapse - String Delimiter

2 Answers2