Skip to main content
Microsoft Idea

Data Factory

Needs Votes

Copy Activity - Allow Quote Character Without Escape Character

Vote (2) Share
Marcus Brubaker's profile image

Marcus Brubaker on 16 Nov 2023 21:15:27

When using a CSV file as the source in the Copy Activity, if you specify a Quote character you are required to specify an Escape character. The escape character should be optional.


Example: CSV has fields that represent free text from the source system. These fields are quoted in the CSV.

Quote character="

Escape character=\

 

"abc","123","x,y,z"  <-this row works correctly.

"abc","123","x\y\z" <-this row fails because copy activity thinks \ is an escape character even though it is between the quotes. It is not escaping a " within the text field.


Current workarounds:

  1. Set the escape character to something highly unlikely, such as ^.
  2. If the escape character legitimately appears in a quoted field the job will fail.
  3. Add a process before the Copy Activity which will parse the CSV and fix occurrences of the escape character.
  4. Process must be kept in-sync with the specified escape character.
  5. Process may need to be customized for each CSV with a unique layout.