Azure Synapse Serverless SQL Pool - Error while selecting from quoted fields-CodePudding

I am getting an error while selecting from a CSV file that contains quoted fields using Serverless/ OnDemand SQL Pool in Azure Synapse. The data contains the field terminator (,) within the fields, but it's quoted with double quotes. I have even tried to specify the FIELDQUOTE explicitly even though I am using the default quote in the data.

My file contains the data as below

"number", "text"
1, "one"
2, "two"
11, "one, one"
12, "one, two"

The SQL that I ran is as below

SELECT
    *
FROM
    OPENROWSET(
        BULK 'https://mydatalake.dfs.core.windows.net/data/test_quoted_fields.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIELDQUOTE = '"',
        FIELDTERMINATOR = ',',
        HEADER_ROW = TRUE
    ) AS [result]

And the error message is as below

Error handling external file: 'Quotes '' must be inside quoted fields at [byte: 10]. '. File/External table name: 'https://mydatalake.dfs.core.windows.net/data/test_quoted_fields.csv'

Please note that I am running the query using Serverless/ OnDemand SQL Pool.

Can someone help please? Thanks

Data in ADLS Portal looks as below In Edit mode

In Preview mode

CodePudding user response：

I so appears that having .csv in excel and after uploading to ADLS would add additional " " around quoted fields so as to be seen as a string. And it does seem to work well.