topic Re: Sudden "DelimitedTextBadDataDetected" Error in Pipelines

Sudden "DelimitedTextBadDataDetected" Error

karans — Wed, 27 Sep 2023 23:38:34 GMT

Hi All,

I am using a Data Pipeline to copy data from a fabric lakehouse into a fabric data warehouse. I have about 16 files, and I get a full historical extract of the files every week. So far in my process, I have four such extracts (so been getting data for about 4 weeks now). The first two were loaded without any issues. Third and fourth extracts started giving me the below error message for 6/16 files (the same 6 files acorss both weeks). I then dropped all tables in the data warehouse and reloaded from the first week. Suddenly, even the first and second week files were giving the same error for the six files, but to be clear, thjey had not been overwritten, updated or modified in anyway since they were originally recieved - e.g. If bad data had arrived from the source in Weeks 3 and 4, it would not have been in these files.

Files are UTF-8 CSVs, that have no encoding issues (inspected with Python). Additionally, I can load all the files into Synapse using ADF on Azure (outside of fabric). I have turned on logging and selected the skip incompatible rows function so I can see the rows that are causing errors and have inspected them - but they are all encoded correctly and I cannot see anything that would cause an error.

Seems to me like there has been a code change or bug introduced given Fabric is still in Preview?

Here is the error message:
ErrorCode=DelimitedTextBadDataDetected,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Bad data is found at line 375844 in source 20230920_bc_gl_entries.csv.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=CsvHelper.BadDataException,Message=You can ignore bad data by setting BadDataFound to null.
IReader state:
ColumnCount: 0
CurrentIndex: 20
HeaderRecord:
["Entry_No","Account_Id","Add_Currency_Credit_Amount","Add_Currency_Debit_Amount","Additional_Currency_Amount","Adjustment","Amount","BAS_Adjustment","BAS_Doc_No","BAS_Version","Bal_Account_No","Bal_Account_Type","BssiAredScheduleNo","Business_Unit_Code","Close_Income_Statement_Dim_ID","Comment","Consol_BAS_Doc_No","Consol_Version_No","Credit_Amount","Debit_Amount","Description","Dimension_Changes_Count","Dimension_Set_ID","Document_Date","Document_No","Document_Type","External_Document_No","FA_Entry_No","FA_Entry_Type","G_L_Account_Name","G_L_Account_No","Gen_Bus_Posting_Group","Gen_Posting_Type","Gen_Prod_Posting_Group","Global_Dimension_1_Code","Global_Dimension_2_Code","IC_Partner_Code","Job_No","Journal_Batch_Name","Journal_Templ_Name","LMCAddBusUnit","LWSNexonCRMOppNo","Last_Dim_Correction_Entry_No","Last_Dim_Correction_Node","Last_Modified_DateTime","NXN_Assigned_User_ID","No_Series","Non_Deductible_VAT_Amount","Non_Deductible_VAT_Amount_ACY","Posting_Date","Prior_Year_Entry","Prod_Order_No","Quantity","Reason_Code","Reversed","Reversed_Entry_No","Reversed_by_Entry_No","Shortcut_Dimension_3_Code","Shortcut_Dimension_4_Code","Shortcut_Dimension_5_Code","Shortcut_Dimension_6_Code","Shortcut_Dimension_7_Code","Shortcut_Dimension_8_Code","Source_Code","Source_Name","Source_No","Source_Type","System_Created_Entry","SystemCreatedAt","SystemCreatedBy","SystemId","SystemModifiedAt","SystemModifiedBy","Tax_Area_Code","Tax_Group_Code","Tax_Liable","Transaction_No","Use_Tax","User_ID","VAT_Amount","VAT_Bus_Posting_Group","VAT_Prod_Posting_Group","VAT_Reporting_Date"]
IParser state:
ByteCount: 0
CharCount: 285055049
Row: 375844
RawRow: 375844
Count: 83
RawRecord:
Hidden because ExceptionMessagesContainRawData is false.
,Source=CsvHelper,'

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Thu, 28 Sep 2023 10:39:03 GMT

Hi karans ,

Thanks for using the Fabric community and reporting this .

I have reached to the internal for help on this. I will update you once I hear from them.

Appreciate your patience.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Fri, 29 Sep 2023 18:51:06 GMT

Hi karans ,
Can you please provide the input file with which you are facing the error ? That can help us in understanding the issue better.
Please provide the screenshot of the error also.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Mon, 02 Oct 2023 15:03:55 GMT

Hi karans
We haven’t heard from you on the last response and was just checking back to see if you can provide the details asked.
In case if you have any resolution, please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Tue, 03 Oct 2023 14:39:29 GMT

Hi karans ,
We haven’t heard from you on the last response and was just checking back if you can provide the details asked above. Please let us know if you have any further queries.

Re: Sudden "DelimitedTextBadDataDetected" Error

arpost — Tue, 10 Oct 2023 21:06:54 GMT

I've also gotten this, and I can't seem to skip past the validation step or find where to set BadDataFound to Null as the error instructs. Anonymous, I can't provide a sample file given the nature of the data.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Thu, 02 Nov 2023 14:28:34 GMT

I am facing the same issue, Just wondering how can I upload the file here.

Re: Sudden "DelimitedTextBadDataDetected" Error

chatjpt — Sun, 05 Nov 2023 16:23:54 GMT

Also having similar issue!

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Mon, 06 Nov 2023 11:04:15 GMT

Is this issue resolved? As the same .json/.avro files are processed successfully in ADF but giving this error in Fabric.

Any workaround is greatly appreciated.

Re: Sudden "DelimitedTextBadDataDetected" Error

arpost — Tue, 07 Nov 2023 16:12:50 GMT

Anonymous, any update on this or a workaround?

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Tue, 07 Nov 2023 17:38:58 GMT

Hi arpost Anonymous chatjpt Anonymous
Apologies for the delay in response. Thanks for reporting the issue. I have forwarded this to internal team. But they need the CSV files to understand the problem better. You can send us the files through email to AzCommunity[at]Microsoft[dot]com with the below details,

Email subject: <Attn - v-nikhilan-msft :Sudden "DelimitedTextBadDataDetected" Error>

Thanks.

arpost

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Wed, 08 Nov 2023 05:45:35 GMT

Hi arpost ,

Can you please set "skip incompatible rows" as true and enable logging? Then you can check the error rows in the logging file.

You can send us the files through email to AzCommunity[at]Microsoft[dot]com with the below details,

Email subject: <ATTN: PRADEEP :Sudden "DelimitedTextBadDataDetected" Error>

Thanks.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Wed, 08 Nov 2023 18:35:44 GMT

Hi Anonymous ,
Thanks for providing the csv files over an email.
But the original ask in this thread is related to error in pipelines. But you have mentioned that you are using dataflows. As this is deviating from the original ask , I would request you to create a new post with the error details.

It will definitely look into the issue and help you.

Thanks.

Re: Sudden "DelimitedTextBadDataDetected" Error

arpost — Wed, 08 Nov 2023 18:37:48 GMT

Anonymous and karans, doing the above proved useful as it showed some of the problem rows. The issue appears to be that the DelimitedText settings for the Copy Activity had a specific character as an escape character that shouldn't have been there. Not certain if those were defaults or if they were a result of processing the schema.

Either way, I changed the escape character, and that fixed it for mine.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Wed, 08 Nov 2023 18:39:30 GMT

Hi arpost ,
Thanks for the update. Glad that your query got resolved.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Thu, 09 Nov 2023 19:21:31 GMT

Just ran into this and I think I understand the problem. Fabric Data Factory's delimited file settings require that if you have a quote character you also have to have an escape character. This is a bad design because 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

WORK AROUND: Chage the escape character to something that does not appear in the file. I changed mine to ^ and was able to finish my Data Copy job.

Re: Sudden "DelimitedTextBadDataDetected" Error

Anonymous — Mon, 04 Mar 2024 12:05:40 GMT

@v-nikhilan-msft I am getting the same error but did not understand the resolution. Where exactly should I go and set the bad data to null?