Create a csv file from output of parse json

[PowerUser0]

Hi, I am getting this kind of output from Parse Json action, now I need to create a CSV table and upload that in one drive. below is the output.

{
"data": [
{
"created_time": "2022-02-13T16:39:36+0000",
"id": "151606954886765",
"field_data": [
{
"name": "languages",
"values": [
"english"
]
},
{
"name": "first_name",
"values": [
"Roopa"
]
},
{
"name": "last_name",
"values": [
"Shree"
]
},
{
"name": "email",
"values": [
"roobash@gmail.com"
]
},
{
"name": "company_name",
"values": [
"L"
]
},
{
"name": "date_of_birth",
"values": [
"1/04/1999"
]
},
{
"name": "gender",
"values": [
"female"
]
}
]
},
{
"created_time": "2022-02-11T18:32:25+0000",
"id": "5506756899180",
"field_data": [
{
"name": "languages",
"values": [
"hindi"
]
},
{
"name": "first_name",
"values": [
"Rahul"
]
},
{
"name": "last_name",
"values": [
"Bajpai"
]
},
{
"name": "email",
"values": [
"bajpai@gmail.com"
]
},
{
"name": "company_name",
"values": [
"Tech"
]
},
{
"name": "date_of_birth",
"values": [
"0/17/1997"
]
},
{
"name": "gender",
"values": [
"male"
]
}
]
}
],
"paging": {
"cursors": {
"before": "QVFIUm5TTXdZARkxuSkhlQVlluRUtrWGRObE9MenRnN2JoSzRzS0VJb00tMTA0QWxZAQ0xOM21jQnFDZA1lQbXNPbU13",
"after": "QVFIUjJNMVhvS2xGbFA5N1ZA3NWNmVzFjMGJqaWswOW9ZAY0hvMkxmQzdRT29IVXA2RW1YYlFsV19DYmFfaEZAvQThxSTln"
}
}
}

Now, I just need data which is inside Field_data array, I need to create a CSV file with Name as the headers of CSV file and values should be the values for those header , so as you can see I have two id's that means it should be two entries in CSV file

I hope this will give more idea,

@Pstork1 @MichaelAnnis @ekarim2020 @abm @tom_riha @Expiscornovus Any help from you guys is much appriciated 

[abm]

Hi @PowerUser0 

There are different ways you could do.  Following are the summary of things you may need to do.

1. Declare an array

2. Parse the JSON

3. Collect Id, Name & Values

4. Construct a JSON Object for the above step 3 value and append to an array (Step 1)

5. Finally create the CSV table using the array 

Thanks

[MichaelAnnis]

Here is the documentation for making your own DataTable via Set Variable:

https://docs.microsoft.com/en-us/power-automate/desktop-flows/variable-data-types#advanced-data-type...

Scroll down to advanced datatypes if it doesn’t do it for you.  Syntax is as follows:

%{^['ColumnName1', 'ColumnName2']['Product1', '10 USD'], ['Product2', '20 USD']}%

so, just remember these rules:

• start datatable with %{ and end with }%
• ^[ starts a title row (optional)
• [ starts an item row
• enclose all text values in ‘
• , separate every cell in the table, so they are needed both between cell values and between rows, such as ],[

This is the “what do we need to get it to?”  I will post another answer on the “how?” once I can take a longer look.

[abm]

Hi @PowerUser0 

Like I mentioned earlier there are different ways you could resolve this. 

Next Initialise an array and add a Parse JSON step.

Above JSON Schema is as follows:

{

    "type": "object",

    "properties": {

        "data": {

            "type": "array",

            "items": {

                "type": "object",

                "properties": {

                    "created_time": {

                        "type": "string"

                    },

                    "id": {

                        "type": "string"

                    },

                    "field_data": {

                        "type": "array",

                        "items": {

                            "type": "object",

                            "properties": {

                                "name": {

                                    "type": "string"

                                },

                                "values": {

                                    "type": "array",

                                    "items": {

                                        "type": "string"

                                    }

                                }

                            },

                            "required": [

                                "name",

                                "values"

                            ]

                        }

                    }

                },

                "required": [

                    "created_time",

                    "id",

                    "field_data"

                ]

            }

        },

        "paging": {

            "type": "object",

            "properties": {

                "cursors": {

                    "type": "object",

                    "properties": {

                        "before": {

                            "type": "string"

                        },

                        "after": {

                            "type": "string"

                        }

                    }

                }

            }

        }

    }

}

Above under the Compose Value action step I have used the below expression. Assuming those array will have always one value only.

items('Apply_to_each_2')?['values'][0]

Finally outside the loop Add the below

See my test result:

Id,Name,Value
151606954886765,languages,english
151606954886765,first_name,Roopa
151606954886765,last_name,Shree
151606954886765,email,roobash@gmail.com
151606954886765,company_name,L
151606954886765,date_of_birth,1/04/1999
151606954886765,gender,female
5506756899180,languages,hindi
5506756899180,first_name,Rahul
5506756899180,last_name,Bajpai
5506756899180,email,bajpai@gmail.com
5506756899180,company_name,Tech
5506756899180,date_of_birth,0/17/1997
5506756899180,gender,male

Thanks

[PowerUser0]

Hi @abm ,

Thanks a lot for your help, I really appreciate your time and effort, you helped to get the csv data but I need the data in below format

Again thank you so much for your help!

@MichaelAnnis @Mister_Shaik 

[abm]

Hi @PowerUser0 

Here are the modified details. So from my previous screenshot keep all the steps up to Parse JSON. Delete all the below steps and add the below details.

The above compose 3 action step details are below.

Id can be mapped from the Parse JSON Dynamic values.

For language expression I used is 

items('Apply_to_each_3')?['field_data'][0]['values'][0]

First Name

items('Apply_to_each_3')?['field_data'][1]['values'][0]

Last Name

items('Apply_to_each_3')?['field_data'][2]['values'][0]

Email

items('Apply_to_each_3')?['field_data'][3]['values'][0]

Company Name

items('Apply_to_each_3')?['field_data'][4]['values'][0]

Date of Birth

items('Apply_to_each_3')?['field_data'][5]['values'][0]

Gender

items('Apply_to_each_3')?['field_data'][6]['values'][0]

 Next step for Append to Array map the output from Compose 3 step.

Finally outside the loop use the CSV Table step.

Here is my final output

Note: Here I assume you have all the elements and values are present in JSON. Here I am directly referencing the index array values. If any elements are missing then this flow will fail.

Hope it is useful and let me know how it goes.

Thanks

[PowerUser0]

Hi @abm ,

Thanks for the help , we are very near to the solutions but my only concern is 

items('Apply_to_each_3')?['field_data'][6]['values'][0] , here the array number is hard coded so lets say there are 10-12 field in future so in that case our flow will only fetch details till ['field_data'][6], can we make some changes to this?

[abm]

Hi @PowerUser0 

Thanks for your reply.  Didn't mention about dynamic values earlier. There are lots to re-write for this. You could try finding the length of the field_data. Construct a Do While loop and append the values into a string and construct the JSON array. 

Thanks

[PowerUser0]

Hi @abm ,

This is entirely new to me, can you give me a small heads up how to do that

[PowerUser0]

Hi @abm , 

Any updates on how to make this dynamic?

[PowerUser0]

Hi @abm 

I am still stuck here 

[abm · Accepted solution]

Hi @PowerUser0 

To make this dynamic you need to know the names. This is what I am thinking.

You have 7 values in that JSON name index

name[0] > Firstname

name[1] > email

name[2] > lastname

......

name[6]

Define variables for all

Int > FirstNameIndex
Int > EmailIndex
Int > LastnameIndex

Construct a loop to find which index it is.

foreach( index++ iterate up to 7)
{

if(name[index]) = "Firstname"
{
FirstNameIndex = index
}

if(name[index]) = "Email"
{
EmailIndex= index
}

}

After executing the for loop you will have all the names index location. 

Let me know how it goes.

Thanks

[PowerUser0]

Hi @abm ,

The logic which you have explain worked for me, hence I am able to dynamically create the CSV now, you have really helped me a lot on this issue, we really need more people here just like you, thanks for connecting one to one and devoting your precious time , Once again thank you so much

[PowerUser0]

Resolved