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

Question

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

score 0 · Answer 1 · answered Jun 24 '23 at 14:01

You can try using an escape character in the 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',
        FIELDQUOTE = '"',
        ESCAPE = '\\',
        FIRSTROW = 2,
        FIELDTERMINATOR = '^',
        CREDENTIAL = (IDENTITY = 'Managed Identity')
    )

It will tell the COPY to treat the backslash character (\) as an escape character. By using double backslashes (\\), you can include a literal double quote (") within a field without it being interpreted as the closing quote.

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

1 Answers1