azure data flow: reading json content in a csv file column

Question

I have a csv file where one of the columns contains a json string like in the attachment. However, when loading the data in azure data flow, column content is split by "," ignoring double quotes to keep the field content together.

Other software like excel, do recognize the field content correctly

How should I deal with this in azure data flow? tasks example json.txt

Answer 1

Hi javier cohenar ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

From the description of your query, it seems while you are trying to preview the csv data which has a json column in mapping dataflow, it is splitting the data by comma of the json content as well even though it is enclosed within double quotes. Please let me know if my understanding about your query is incorrect.

To expect the dataflow to consider the double quotes as quote character, you should have all the column data within double quotes and not just the one having json content.

To resolve this issue, you can try using a custom delimiter for the CSV file that is not used in the JSON string. For example, you can use an ampersand '%' character as the delimiter instead of a comma.

Here are the steps to use a custom delimiter in Azure Data Factory:

1. Open the CSV file in a text editor, such as Notepad.
2. Replace all commas in the JSON string column with a different character that is not used in the JSON string, such as an ampersand '%' character.

1. Now, replace ""%"" with "",""

1. Create the dataset and update the column delimiter to %
2. In data preview tab, Refresh from source and see if it works.

Hope it helps. Kindly accept the answer if it's helpful. Thankyou