Forum Discussion
Sudden "DelimitedTextBadDataDetected" Error
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,'
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.
16 Replies
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.
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.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.
arpostPost Prodigy
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.
Also having similar issue!
- arpost
Anonymous, any update on this or a workaround?
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.
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.
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.
