topic Copy data DelimitedTextBadDataDetected in Pipelines

Copy data DelimitedTextBadDataDetected

Anonymous — Thu, 24 Oct 2024 11:05:39 GMT

Hello all,

I'm trying to migrate some pipelines from ADF to Fabric Pipelines. In the copy activity in Fabric pipeline for a delimited text file I get an error with the message DelimitedTextBadDataDetected. The settings for the delimited files are exactly the same:

Column delimiter: ;

Quote char: "

Escape char: \

The problem seems to be some rows where a field contains the double quotes as value. For example:

first_col;this is a field with "inside" quotes; last_col -> works fine in ADF, but not in Fabric

first_col;"fully quoted; with delimiter";last_col ->works fine in both

The expected beahivor is that the data get's copied correctly for both rows with the provided settings, because the double quotes aren't inside other double quotes for the first row. What can I do to get this work?

Regards

Jan

Re: Copy data DelimitedTextBadDataDetected

FabianSchut — Thu, 24 Oct 2024 11:28:25 GMT

Hi Anonymous,

I've read in another post that Fabric has made some by-design behaviour redaring escapes and delimiters: Solved: Bug: Copy Data Delimited File Settings - Microsoft Fabric Community. You may need to escape the quotes inside your input as follows: first_col;this is a field with \"inside\" quotes; last_col
Could you try if that works for you?

Re: Copy data DelimitedTextBadDataDetected

Anonymous — Fri, 25 Oct 2024 06:30:51 GMT

Hello FabianSchut

Thank you for your suggestion, I tried it and could get it working with some minor adjustmens by adding double quotes at the beginning and the end of the field like this:

first_col;"this is a field with \"inside\" quotes"; last_col

So it seems that Fabric Pipeline is a bit more strict than ADF and in order to use double quotes I need to put the whole field in quotes an escape them...

Re: Copy data DelimitedTextBadDataDetected

Tim_D — Tue, 29 Oct 2024 21:55:28 GMT

Thanks for the help on this. I just want to add some comments in case it can help others looking at this same issue. We encountered the DelimitedTextBadDataDetected reading a 3rd-party text file in Fabric pipeline Copy Data activity. It worked fine in ADF, but not in Fabric. I noticed the bad source records had repeating double-quotes, and I realized the file was using double-quotes as both a Quote character and also as the Escape character. It ran fine after I updated the file format settings accordingly.

Also, a related observation: before I fixed the issue, we had trouble with the Import Schemas feature in the Mapping section. It would spin for awhile and then just display a blank screen, with no errors generated. After I updated the file format settings, it worked just fine. I'm guessing the DelimitedTextBadDataDetected error was happening in the background but it wasn't apparent in the UI. It would be nice if Microsoft could adjust this behavior to give feedback in the UI on why the schema mapping wasn't generated.