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

Question

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

KarthikBhyresh-MT · Accepted Answer · 2021-12-23 12:12:36Z

2

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.

edited Dec 23, 2021 at 12:12

answered Dec 22, 2021 at 17:59

KarthikBhyresh-MT

5,1142 gold badges7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ramesh Over a year ago

Thanks Karthik for looking into it. I missed the comma just in the post here. The code did have the comma, otherwise I would have got syntax error. I have added a screenshot with the statement I am running (that includes the comma) and also the error. It is strange that this works for you. Anything you can suggest. I am working on a macbook and the file was created in the mac too.

KarthikBhyresh-MT Over a year ago

Can you try preview the csv in adls portal to see if the structure is right

Ramesh Over a year ago

Yes, the data looks in the portal looks OK to me. I have just added the screenshots of both Edit and Preview mode to the question above. Thanks again for looking into this.

KarthikBhyresh-MT Over a year ago

I think I just found during repro just secs before, updated answer now 😊

Ramesh Over a year ago

Thanks for pointing me at the right direction. The issue was the additional space between the 2 columns after the comma. Removing the additional space fixes the issue. Thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related