Synapse External table with double quote

Sample Data

“ID”,”Website”,”URL”,”Description”
“1”,”TechTerms”,”https://techterms.com”,”TechTerms the Computer Dictionary”
“2”,”Slangit”,”https://slangit.com”,”Slangit the Clean Slang Dictionary”

–Drop EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = ‘SynapseDelimitedTextFormat’) 

    CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 

    WITH ( FORMAT_TYPE = DELIMITEDTEXT ,

           FORMAT_OPTIONS (

             FIELD_TERMINATOR = ‘,’,

             STRING_DELIMITER = ‘”‘,

             FIRST_ROW=2,

             USE_TYPE_DEFAULT = FALSE

            ))

GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = ‘azsyncusc001fs_azsyncusc001stor_dfs_core_windows_net’) 

    CREATE EXTERNAL DATA SOURCE [azsyncusc001fs_azsyncusc001stor_dfs_core_windows_net] 

    WITH (

        LOCATION = ‘abfss://azsyncusc001fs@azsyncusc001stor.dfs.core.windows.net’ 

    )

GO

–drop EXTERNAL TABLE testtable3

CREATE EXTERNAL TABLE testtable3 (

    [ID] nvarchar(1),

    [WebSite] nvarchar(40),

    [URL] nvarchar(100),

    [Comments] nvarchar(200)

    )

    WITH (

    LOCATION = ‘rawzone/doublequotesamplefile.csv’,

    DATA_SOURCE = [azsyncusc001fs_azsyncusc001stor_dfs_core_windows_net],

    FILE_FORMAT = [SynapseDelimitedTextFormat]

    )

GO

SELECT TOP 100 * FROM dbo.testtable3

GO