Bug: Copy Data Delimited File Settings

[MarcusJBrubaker]

See Solved: Sudden "DelimitedTextBadDataDetected" Error - Microsoft Fabric Community for background.

Copy Data's delimited file settings require that if you have a quote character defined you also have to have an escape character. BUG: The load will fail if your escape character is in your quoted text.

Example:

Quote character="

Escape charater=\

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

"abc","123","x\y\z" <-this row fails because Fabric thinks the \ is an escape character even though it is between the quotes. 

How this SHOULD work is the escape character should only be considered an escape character when paired with the quote charcter. Example:

"abc","123","x\y\z" <-this SHOULD work fine. 3rd value should be x\y\z

"abc","123","x\"y\"z" <-this should make 3rd value x"y"z

@v-nikhilan-msft 

[v-nikhilan-msft]

Hi @MarcusJBrubaker 
Thanks for the ask and using Microsoft Fabric Community.

At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.

Appreciate your patience.
Thanks.

[v-nikhilan-msft · Accepted solution]

Hi @MarcusJBrubaker ,
The internal team has confirmed that its not a bug. This is by-design behavior. Escape char should also be escaped. The correct csv value is "x\\y\\z"

Hope this helps. Please let us know if you have any further questions.

[MarcusJBrubaker]

This is a dangerous design. The point of a quoted field is to treat anything between the quotes as free text. Being required to specify an escape charater means there will always be a chance that the job will fail because the free text data could legitimatey contain that escape character. To make this work I'd have to create a process to pre-screen the data looking for escape characters and adjusting them before loading. 

A far better solution is to remove the requirement to have an escape character. If I could set it to "none" then I'll never have a problem loading a quoted free-text field. 

[v-nikhilan-msft]

Hi @MarcusJBrubaker 
It should be delimited text expected behavior. If the escape char doesn't be escaped , how could we handle the case when escape character is the last char of column. It will make it bad data.
Take below example, csv data is "xyx\". If the last escape character isn't escaped, it will wrongly escape the quote character and make the whole data bad. The correct data should be "xyx\\". Then it will be consumed correctly.

[MarcusJBrubaker]

Specifying both a quote and an escape character should not be manditory.

The point I'm trying to make is my data doesn't have any escape characters ever. I want the option to specify a quote character and also specify no escape characters. If the quoted string intentionally has a \, it should be treated as a \, not an escape.

My only recource is to parse through every CSV to find and replace an escape character before I load them. This requires extra development and processing steps which could be eliminated if the escape character was not manditory.

Having the option to specify an escape charater is a good thing. But it isn't an option. I'm forced to always specify an escape charater. I can pick a very unlikely character, such as ^, to reduce the chances of a failure. But sooner or later a CSV file will show up with ^ in a quoted string field. Then the job will fail. 

[v-nikhilan-msft]

Hi @MarcusJBrubaker 
Thanks for your feedback. As per the design escape character should also be escaped. The correct csv value is "x\\y\\z".
Appreciate if you could share the feedback on our feedback link , which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.
Feedback Link : Home (microsoft.com)

Thanks.