JSON to CSV transformation

Question

I would like to below given json data to given csv format using data factory if possible

json:

{
    "employee":"123",
    "bankdetail":[
        {
            "banknumber" : "bank1"
        },
        {
            "banknumber" : "bank2"
        }
    ],
    "dependent" : [
        {
            "depname" : "dep1"
        },
        {
            "depname" : "dep2"
        },
        {
            "depname" : "dep3"
        }
    ]
}

CSV:

Answer 1

Hi Upendra Singh ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As I understand your query, you are trying to convert the json data into csv format with the help of mapping dataflow in ADF pipeline. Please let me know if that is not the requirement.

This can be achieved by creating two branches (one having only employee and bank, other having only employee and dependent) and then flatten the data of bank[] array and dependent[] array in each of the branches, do a full-outer join based on surrogate key and then use select transformation to deselect the unnecessary columns .

1. Add the Source transformation to point to the input json dataset. Select JSON Document form as 'Array of documents'
2. Add Flatten transformation and select bankdetail[] to unroll by and in source columns select 'employee' as 'employee' and 'bankdetail.banknumber' as 'bank'
3. Create a new branch out of the source transformation and add another flatten transformation and select 'dependent[]' to unroll by and in source columns select 'employee' as 'employee' and 'dependent.depname' as 'dependent'
4. Add Surrogate key transformation to create unique ID associated with each of the rows in both the branches and give the column name as 'Id1' and 'Id2' respectively
5. Add Join transformation and select 'Full outer' join to join using Id1==Id2
6. Use Select transformation to deselect all the columns except employee, bank and dependent

Sharing the implementation videos below:

Part1:

Part2:

Hope it helps. Please let us know if you have any additional queries. Kindly accept the answer by clicking on Accept answer button. Thankyou

Answer 2

@Upendra Singh

Thank you for posting query in Microsoft Q&A Platform.

1. In the Azure Data Factory pipeline, select the copy activity that you added to transform the JSON data to CSV format.

2. In the copy activity, select the "Mapping" tab.
3. In the "Source" section, select the JSON dataset that you added as the source in the pipeline.

4. In the "Sink" section, select the CSV dataset that you added as the sink in the pipeline.
5. In the "Mapping" section, you can map the JSON data fields to the CSV data fields by dragging and dropping the fields from the "Source" section to the corresponding fields in the "Sink" section.

1. Once you have mapped all the fields, select "Publish all" to save the mapping.

refer this video for detailed information.https://www.youtube.com/watch?v=VbLddzh1_2E

alternatively, you can also use.

Flatten transformation: Connect the source output to the Flatten transformation to flatten the JSON file. You might need to add multiple Flatten transformations between the source and sink to get the required output.

please go through this links :https://datanrg.blogspot.com/2020/03/transforming-json-to-csv-with-help-of.html

https://stackoverflow.com/questions/68669250/use-azure-data-factory-to-parse-json-table-to-csv-format

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 3

You can use this online tool to convert JSON to CSV: https://formatjsononline.com/json-to-excel.