Forum Discussion

Icon for Post Prodigy rank

Post Prodigy

2 years ago

Solved

How to handle quotes within a string in Copy Activity?

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"...

Anonymous
2 years ago
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

Anonymous

Not applicable

2 years ago

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
Post Prodigy
2 years ago
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
  Not applicable
  2 years ago
  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
    Post Prodigy
    2 years ago
    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.