How to handle quotes within a string in Copy Activity?

[arpost]

Greetings, community. I have a CSV file I am trying to load into a Lakehouse using a Copy Activity and am having some trouble related to characters in the file. Here is a fake data sample:

"ColumnA","ColumnB"
"1","Alpha"
"2","Alphabet
"Soup""
"3","Beta"

I believe the error is being caused by the quotes around "Soup" as they aren't escaped in the file from my data source. The final output I want to see in a lakehouse table is:

Does anyone know of a way I can configure my Copy activity's settings to solve for this?

[Anonymous]

Hi @arpost ,

Thanks for using Fabric Community.
I would like to know the input file format?

Is it this ?

"ColumnA","ColumnB"
"1","Alpha"
"2,"Alphabet "Soup""
"3","Beta"

or this ?

"ColumnA","ColumnB"
"1","Alpha"
"2,"Alphabet 
"Soup""
"3","Beta"

 
Can you please help me understand this?

[arpost]

@Anonymous, it is the second one, and this is the representation of the data in CSV format. ColumnB is a field that can contain user comments that may include everything from line breaks to quotation marks.

"ColumnA","ColumnB"
"1","Alpha"
"2","Alphabet
"Soup""
"3","Beta" 

Here's an example of how the data would look in a tabular format:

ColumnA	ColumnB
1	Alpha
2	Alphabet "Soup"
3	Beta

[Anonymous]

Hi @arpost ,

Please try to check this video - Link 

I can also find issue with you data - Double Quotes is missing.

I guess this should be like this -

"ColumnA","ColumnB"
"1","Alpha"
"2","Alphabet
"Soup""
"3","Beta" 

 
Please let me know if this works for you.

[arpost]

Thank you for the reply, @Anonymous, and for pointing that out. I fixed that in the examples; must have dropped it somehow. Corrected data is as follows:

"ColumnA","ColumnB"
"1","Alpha"
"2","Alphabet
"Soup""
"3","Beta"

The text in orange above is the incoming data that is malformed (no additional escape characters around the quotes). What I'm trying to do is see if Data Factory can overcome this issue with any of its settings.

I've been able to solve for this pretty easily with a dataflow, but I want to use a pipeline for this operation.

[Anonymous · Accepted solution]

Hi @arpost ,

As I understand, without having any escape characters we cannot handle this kind of data in data pipeline. This data will be treated as bad data and make it to null. You can continue using Data flow as you were able to find a solution with the help of it.

Thank you

[arpost]

@Anonymous, bummer. Thanks for the info and for looking into it, though. Dataflow really isn't ideal (if for no other reason than dataflows aren't supported in deployment pipelines), so I'll keep looking.

[Anonymous]

Hi @arpost ,

Glad to know that you got some insights over your query. Please continue using Fabric Community on your further queries.