topic Re: Dataflow Gen2 Copy data csv to snowflake in Data Engineering

Dataflow Gen2 Copy data csv to snowflake

shivaazure — Tue, 12 Nov 2024 22:42:56 GMT

I have a dataframe loading data into CSV ( few columns contain JSON structure data),

Now trying to load the CSV to Snowflake tables using Copy data activity in Fabric dataflow activity.

Getting error while loading data into SNowflake for the columns which contain nested JSON data

Error:

ErrorCode=DelimitedTextBadDataDetected,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Bad data is found at line 82 in source R1.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=CsvHelper.BadDataException,Message=You can ignore bad data by setting BadDataFound to null.
IReader state:
ColumnCount: 140
CurrentIndex: 8
HeaderRecord:
["Id","Request Id","Start Time","End Time","Refresh Type","Service Exception Json","Status","Extended Status","Refresh Attempts","Semantic Id","Semantic M Name"]
IParser state:
ByteCount: 0
CharCount: 41610
Row: 82
RawRow: 82
Count: 140
RawRecord:
Hidden because ExceptionMessagesContainRawData is false.
,Source=CsvHelper,'

My Doubt is why does the copy activity considering a string data into JSON format and parsing it. can it be copy as string in snowflake table.

Re: Dataflow Gen2 Copy data csv to snowflake

Anonymous — Wed, 13 Nov 2024 06:26:48 GMT

Hi @shivaazure ,

The error message says that bad data was found when parsing line 82 of the CSV file.

I have some suggestions:

1. Open the CSV file and check the data format of line 82 to make sure that line 82 contains all expected columns and there are no extra delimiters or special characters.

2. Set the BadDataFound option in the Advance editor of Dataflow Gen2. The code is as follows:

let Source = Csv.Document(File.Contents("path/to/your/file.csv"), [Delimiter=",", Columns=140, Encoding=65001, QuoteStyle=QuoteStyle.Csv, BadDataFound = null]), PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in PromotedHeaders

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Dataflow Gen2 Copy data csv to snowflake

Anonymous — Tue, 19 Nov 2024 06:14:48 GMT

Hi @shivaazure ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

Re: Dataflow Gen2 Copy data csv to snowflake

Anonymous — Thu, 28 Nov 2024 09:02:26 GMT

Hi @shivaazure ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team