'COPY INTO fails when one of the csv columns has quotes as part of the data

I have a CSV file with a column contains " as part of the data The file is initially copied in ASF from SFTP location to ADLS in the copy activity I tried adding quotes as the Quote character so my data looks like (this is just an example, the actual file is bigger)

ID ^ Name

"1" ^ "Galaxy 7" "

what breaking the Azure Synapse COPY INTO command is the inches sign (") next to Galaxy 7

I even tried adding \ as an escape character in the data source that outputs the CSV into adls but did not help

always getting same error

Bulk load failed due to invalid column value in CSV data file /dev/switchingdb/avi_1.txt in row 2, column 21.

copy into command

COPY INTO stg.cdm_stg_Switching_DB FROM 'https://prdacrdatalake.dfs.core.windows.net/adf/dev/switchingdb/avi_1.txt'
                WITH (
                        FILE_TYPE = 'CSV'
                    --  ,COMPRESSION = 'gzip'
                        ,FIELDQUOTE ='"'
                        ,FIRSTROW =2
                        ,FIELDTERMINATOR ='^'
                        ,CREDENTIAL = (IDENTITY = 'Managed Identity')
                )

csv azure-synapse

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'COPY INTO fails when one of the csv columns has quotes as part of the data

Sources

Related Questions