Problem querying a CSV File with Synapse SQL Serverless Pool

Question

Hello,

trying to query a very simple CSV file, Strings have Double quote (").

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://xxxx.dfs.core.windows.net/sample/csv/backlog-20201207-2.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
firstrow = 2,
FIELDQUOTE = '"'
) AS [result]

Keep getting this error:

Error handling external file: 'Quotes ' ' must be inside quoted fields at [byte: 206]. '. File: 'https://dfs.core.windows.net/sample/csv/backlog-20201207-2.csv'.

Any help?

thank you so much

Answer 1

Hello Himanshu,

Thank you so much!

It works also for me but there is an issue.

My field quote for strings is the double quote “

As you can see now the strings have double quotes.

I thought I could have just specify as Field quote

FIELDQUOTE = '”'

But it is not working.

Is there a way I can get the string without double quote.

If I filter for example with a WHERE condition like

WHERE C5 = '1'

I get the following error in the messages

Potential conversion error while reading VARCHAR column 'C5' from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. )
Error handling external file: 'Quotes ' ' must be inside quoted fields at [byte: 59782]. '. File: 'https://myblob.dfs.core.windows.net/file.csv'.

Thank you so much

--silvano

Answer 2

Hello @Silvano Paxia ,

Welcome to the Microsoft Q&A platform.

As per my repro, I'm able to successfully run the query using a serverless SQL pool in Azure Synapse Analytics.

In order to investigate further, could you please share the sample data to test on your dataset?

Meanwhile, you can go through How to use OPENROWSET using serverless SQL pool in Azure Synapse Analytics.

Hope this helps. Do let us know if you any further queries.

------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Answer 3

Hello @Silvano Paxia ,

The below works for me .

SELECT  
    TOP 100 *  
FROM  
    OPENROWSET(  
        BULK 'https://myblob.blob.core.windows.net/himanshu/File2/backlog-20201207-2.csv',  
        FORMAT = 'CSV',  
        PARSER_VERSION='2.0',  
       FIELDTERMINATOR ='   ',    
        firstrow = 2,         
        FIELDQUOTE = ''''  
) AS [result]  

Let me know how it goes .

Thanks
Himanshu

Answer 4

Hello @HimanshuSinha-msft

can you please help me? I sent you an email last week.

Thanks

Answer 5

Have you checked if this header is OK?
It seems it is trying to load the header anyway and it has problems.
Set FIRSTROW=1 for trying to avoid reading the first row as a header.