topic Re: Importing CSV File in Pipelines

Importing CSV File

Torin78 — Sun, 07 Jul 2024 13:32:09 GMT

Hi, i am trying to import a csv file such:

345,Fernandez,"{id:4r34,add:calle,ape:feraa}"

I´ve imported the file with SQL Server Management Studio and it Worked perfect, putting this {id:4r34,add:calle,ape:feraa} in a field.

The quotation Charecter is " and field Separatos is ,

Furthermore in ADF does not work fine, it split in this {id:4r34 in a field, this in another field add:calle and so on...

Do you know how can i import properly this file with ADF?

Thank you

Re: Importing CSV File

Anonymous — Mon, 08 Jul 2024 04:13:29 GMT

Hi Torin78 ,

i think you can use a Derived Column transformation to create a new column that parses the JSON string.You can use the parse function to convert the JSON string into a structured format.

If your JSON strings are nested, use the Flatten transformation to break down the JSON into individual columns.

Add a Sink transformation to write the transformed data to your desired destination. You can also use this JSON code and have a look at this topic: azure data factory - ADF Copy activity to handle csv files with json fields - Stack Overflow

{ "type": "DerivedColumn", "name": "ParseJSON", "derivedColumns": [ { "name": "ParsedJSON", "expression": "parse({YourJSONColumn})" } ] }

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Importing CSV File

Torin78 — Mon, 08 Jul 2024 06:07:55 GMT

Hi Yilingo Zhou i dont want to parse the json i want to store the whole json in a filed, but the pipeline break the field when it find a ",".

Re: Importing CSV File

Anonymous — Mon, 08 Jul 2024 08:52:36 GMT

Hi Torin78 ,

Here are some steps you can follow:

1. Define the Source Dataset:
In ADF, create a dataset for your CSV file.
Ensure that the Column Delimiter is set to , and the Quote Character is set to ".
2. Use Data Flow:
Create a Data Flow in ADF.
Add a Source transformation and connect it to your CSV dataset.
In the Source settings, ensure that the Quote Character is set to ".

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Importing CSV File

Anonymous — Mon, 08 Jul 2024 10:48:34 GMT

Yes i think that i am doing properly.

Importing with Management Studio it works fine.