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

Question

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

Answer 1

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

Answer 2

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');

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