Sudden "DelimitedTextBadDataDetected" Error

[karans]

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,'

[MarcusJBrubaker]

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. 

[arpost]

@v-nikhilan-msft, any update on this or a workaround?

[v-nikhilan-msft]

Hi @arpost  @AlinaFlorence  @chatjpt  @sanjay1135 
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

[v-nikhilan-msft]

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.

[arpost · Accepted solution]

@v-nikhilan-msft 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.

[Bangalore_data]

@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?

[v-nikhilan-msft]

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

[AlinaFlorence]

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.

[chatjpt]

Also having similar issue!

[arpost]

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. @v-nikhilan-msft, I can't provide a sample file given the nature of the data.

[v-nikhilan-msft]

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.

[v-nikhilan-msft]

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.

[v-nikhilan-msft]

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.

[v-nikhilan-msft]

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.

[sanjay1135]

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

[v-nikhilan-msft]

Hi @sanjay1135 ,
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.