question

KunalKumarSinha-4793 avatar image
0 Votes"
KunalKumarSinha-4793 asked ·

Additional quote inserted while copying value from csv file using ADF

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.

76358-uploadsinkds.png
76337-downloadsinkds.png

Thanks,
Kunal


azure-data-factory
uploadsinkds.png (25.8 KiB)
downloadsinkds.png (26.9 KiB)
· 5
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @KunalKumarSinha-4793 and weldomce back to Microsoft Q&A.

I notice you have the Quote character set to Double 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.

0 Votes 0 ·

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.

76500-sampledata.txt
76546-image.png


0 Votes 0 ·
sampledata.txt (5.9 KiB)
image.png (36.1 KiB)
MartinJaffer-MSFT avatar image MartinJaffer-MSFT KunalKumarSinha-4793 ·

Thank you for the sample data. This will make reproducing the issue, (and maybe a solution) much easier!

0 Votes 0 ·
MartinJaffer-MSFT avatar image MartinJaffer-MSFT KunalKumarSinha-4793 ·

Err, the sample data contains the extra quotes already!

0 Votes 0 ·
Show more comments

0 Answers