Is it possible to Read CSV files where description has a quote (not delimited by quotes) in Synapse SQL On Demand?

silvan 1 Reputation point
2020-07-02T21:33:17.08+00:00

Hello,

my question is very sample if have the following CSV file in my datalake

|Id|Description
|1|my description
|2| descry 3 " inches

is it possible to use TSQL BULK an openquery to read the above content?

Thank you so much

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,669 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2020-07-07T01:34:01.583+00:00

    Hello @silvan-9125,

    Thanks fo the question .

    The below TSQL will work for the first row , but it breaks for the last one . I am trying to get more help/clarity on this internally , I will update uou once I hear from our internal team .

     COPY INTO dbo.test FROM 'https://…..test.csv'
     WITH (
        FIELDTERMINATOR='|',
        FIRSTROW = 2,
        ENCODING = 'UTF8',
        CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='’)
     ) OPTION (LABEL = 'COPY: dbo.test');
    

    Thanks
    Himanshu

    0 comments No comments

  2. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2020-07-07T22:17:44.837+00:00

    Hello @silvan-9125,

    The below query will work . Please beware that in your case as you called out that the file is CSV but the we have "|" as the field separator and also the "|" is something which is the first element in all the rows .

    This is what i tried and I created a table with a dummy field .

     CREATE TABLE dbo.test
     (
       dummycolum varchar(100),
       id int  null
       ,Description varchar(200)  NULL
        
     )
    
    
     COPY INTO dbo.test FROM '........../test.csv'
     WITH (
        FIELDTERMINATOR='|',
        FIRSTROW = 2,
        FIELDQUOTE='',
        ENCODING = 'UTF8',  
        CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET=''.........')
     ) OPTION (LABEL = 'COPY: dbo.test');
    

    11506-1.gif

    Thanks Himanshu

    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members