Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,646 questions
Additional quote inserted while copying value from csv file using ADF
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 66%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKK%3C/text%3E%3C/svg%3E)
Kunal Kumar Sinha
171
Reputation points
While inserting value to Azure Sql DB from csv file additional "" is getting appended and while extracting the escape characters are coming, because of this the same value is getting updated again and again with additional quote Ex.
Original value - Fundacion Bancaria Caixa d'Estalvis i Pensions de Barcelona, "la Caixa"
Inserted value - Fundacion Bancaria Caixa d'Estalvis i Pensions de Barcelona, "la Caixa"""
Extracted value from table - Fundacion Bancaria Caixa d'Estalvis i Pensions de Barcelona, \la Caixa\"\"\""
when my pipeline runs next time the value to be inserted becomes Fundacion Bancaria Caixa d'Estalvis i Pensions de Barcelona, \la Caixa\"\"\"" and again the same process continues where same value is updated with additional quotes "".
I'm attaching screenshot of my upload and download datasets, please let me know how i can avoid this.
Thanks,
Kunal
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
MartinJaffer-MSFT 26,036 Reputation points2021-03-11T01:06:05.377+00:00 Hello @Kunal Kumar Sinha and weldomce back to Microsoft Q&A.
I notice you have the
Quote characterset toDouble quote(")in both the source and sink datasets. Could you try changing it to None in both datasets?Your data contains double quotes ("la Caixa"). The string fields are not surrounded by quotes. This means double quote is not suitable as a quote character.
-
Kunal Kumar Sinha 171 Reputation points
2021-03-11T04:02:23.19+00:00 Hi @MartinJaffer-MSFT Thank you for replying !
When I set quote character to None in both datasets, then while downloading some part of column values are getting placed to next columns. and while uploading it's throwing error as ErrorCode=DelimitedTextMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error found when processing 'Csv/Tsv Format Text' with row number 6: found more columns than expected column count
I'm attaching the sample data in txt format.
-
MartinJaffer-MSFT 26,036 Reputation points
2021-03-11T21:52:45.013+00:00 Thank you for the sample data. This will make reproducing the issue, (and maybe a solution) much easier!
-
MartinJaffer-MSFT 26,036 Reputation points
2021-03-12T17:55:40.56+00:00 Err, the sample data contains the extra quotes already!
-
Kunal Kumar Sinha 171 Reputation points
2021-03-12T20:06:15.7+00:00 HI @MartinJaffer-MSFT , Since i can't attach excel here so I opened the excel in notepad++ and have taken some data from there for reference.
Sign in to comment