Forum Discussion
Bug: Copy Data Delimited File Settings
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
Anonymous
6 Replies
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.