Query .csv file using Serverless Pool Synapse

Question

Query .csv file using Serverless Pool Synapse

39861377 141

Hi,

I'm trying to query a .csv file stored in blob storage using Serverless Pool. None of the fields in the file have quoted strings. When querying it throws error. The file is '|' pipeline delimited.

User's image

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-03-09T22:59:59.2666667+00:00

Hello @37651609,

Welcome to the MS Q&A platform.

Could you let us know your query and the sample CSV file you use here?

I am looking forward to your reply.
39861377 141 Reputation points

2023-03-10T05:14:41.1366667+00:00

Hi @Bhargava-MSFT ,

There are some text columns (comments data) that contains quotes(") as values. I believe that's what causing this issue because I tried querying similar file (without quotes as values) and it worked fine. Is there a way to handle the quotes present as values provided we don't have field quotes?

2 answers

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-03-09T22:59:59.2666667+00:00

Hello @37651609,

Welcome to the MS Q&A platform.

Could you let us know your query and the sample CSV file you use here?

I am looking forward to your reply.
39861377 141 Reputation points

2023-03-10T05:14:41.1366667+00:00

Hi @Bhargava-MSFT ,

There are some text columns (comments data) that contains quotes(") as values. I believe that's what causing this issue because I tried querying similar file (without quotes as values) and it worked fine. Is there a way to handle the quotes present as values provided we don't have field quotes?

Answer 1

39861377 141

Hi @Bhargava Gunnam , @Jaison Menezes

Found that a quote character can be escaped using another character. So I used '=' as the quote character since it was not appearing in any of the values.

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://<storage>.blob.core.windows.net/Customer/Customer_2023-03-08_07-30.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIELDTERMINATOR = '|',
        HEADER_ROW=TRUE,
        FIELDQUOTE = '='
    ) AS [result]

Jaison Menezes 0 Reputation points

2023-03-10T15:34:34.5533333+00:00

Glad you found the answer!
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-03-10T17:52:38.85+00:00

Thank you @Jaison Menezes

Hi 39861377,

Thank you for sharing the resolution.
yetalaga 0 Reputation points

2023-09-20T07:03:48.48+00:00

Thank you so much 39861377! It works for me

Answer 2

Jaison Menezes 0

Yes you can use double quotes in CSV in order to escape the double quotation marks try to enclose them within another double quotation mark.

39861377 141 Reputation points

2023-03-10T06:57:06.3633333+00:00

@Jaison Menezes , this has to be done in the source file right? Is there a way to handle it in query?
39861377 141 Reputation points

2023-03-10T06:59:55.6866667+00:00

@Jaison Menezes , this has to be done in the source file right? Is there a way to handle it using query?
Jaison Menezes 0 Reputation points

2023-03-10T07:12:17.4166667+00:00

Since serveless is directly taking the source as it is and showing as an SQL table or view. I think you mostly have to make this transformation at source level.

Share via

Query .csv file using Serverless Pool Synapse

2 answers

Your answer