I've got a pipeline built in Azure Data Factory that's pulling data from SQL and storing it as csv. My Dataset for the csv has Escape Character set to a blackslash
(\) and Quote Character set to Double Quote
("). The problem this causes is when I have an empty string with just a backslash (or at the end of a string value), which exists in my data quite a bit. Dozens of tables and columns.
When this happens the backslash at the end of the string (example:
"this is my test\" can't be interpreted by other systems to load. The quote after the blackslash is ignored because it's being escaped and throws off the columns. I have tried using other characters such as
"^" to escape but I also have
"^" in many values across many table columns and the same issue can happen.
It seems this is an issue with ADF and whenever an escape character exists in data that is at the end of a value (or the only value) it will cause an error. How can I handle this without having to address it on the source side? Why doesn't data Factory convert escape characters to double backslash when it's in data? I would expect a backslash in a string value to get converted to
"\\" even when quoting but it isn't happening. It only happens if you don't use quoting.
Thanks for the help!