update excel from json

[rich0087]

I have a json from excel 1 and I want to update a column in excel 2.

Can somebody point me the right direction how to parse the json and update the rows in excel 2?

[Pstork1]

You will use the Parse JSON action to gain access to the Rows and columns and then Add a Row for each Row in the JSON.  Can you share a sample of a bit of the JSON involved and I can show you how it works.

[rich0087]

@Pstork1  (thanks for you help on this one)

I've included images of the flow below with the output for the json. The common key between the two excel worksheets is Claim. I was thinking I would Initialize a Variable. and then parse it and then i got stuck because I didn't know how that would take the data and update the status column. 

Json output of log claim

{

  "Claim": [

    {

      "Claim": "1300-90-0406",

      "Status": "Closed",

      "State": "CA",

      "Incident Date": "32876"

    },

    {

      "Claim": "1300-00-0342",

      "Status": "Closed",

      "State": "CA",

      "Incident Date": "36755"

    },

    {

      "Claim": "8118",

      "Status": "Closed",

      "State": "CA",

      "Incident Date": "37320"

    },

[Pstork1]

You look like you are on the right track. But there are couple things you are doing that you don't need to do.

1) You can insert the Output from the original file directly into the Parse JSON.  You don't need to put it into a variable first.

2) Once you complete the Parse JSON you should have direct access to the properties of the items in the array.  If you try to access one, do something like put it into a compose., it will automatically create an Apply to each loop for the items in the array.  Then all you need to do is Add a row to the second excel file inside that Loop and supply the values.  You shouldn't need to check for blank values unless you want to avoid processing those rows.

3) You can also do your logging inside the Loop.

Once you exit the loop you'll be all done.

[rich0087]

@Pstork1   Your way is much simpler than what I was attempting, however it's still getting stuck. The error I'm receiving is:

"The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['Claim']' is of type 'Null'. The result must be a valid array." 

I've included my flow below. I'm parsing the body of the excel rows. The next step I did was compose and it created the apply to each as you stated. I added the body as the output and the input was the column status (as thats is the specific data I want to add to excel 2). The key value between the two excel's is claim. 

I feel as if i'm missing one important piece and  I played around with the inputs, but nothing seemed to work. 

Appreciate any guidance. 

[Pstork1 · Accepted solution]

You shouldn't need the second Apply to Each loop.  Once you've created the first loop on Status you should have access to all the properties at the same level as Status.  So under the compose just add your update Row and fill in the values.

[rich0087]

@Pstork1  That is what I was assuming, however it automatically creates the second apply to all when I input my 'key value' into the update a row field. 

[Pstork1]

Can you show me the full JSON schema you are using?  You may have to enter the key value using some manual entry of JSON.

[rich0087]

@Pstork1  Below is the full JSON schema. Could I be receiving this error because of the ItemInternalID

Below is the schema I included into parse JSON

{

    "type": "object",

    "properties": {

        "Claim": {

            "type": "array",

            "items": {

                "type": "object",

                "properties": {

                    "Claim": {

                        "type": "string"

                    },

                    "Status": {

                        "type": "string"

                    },

                    "State": {

                        "type": "string"

                    },

                    "Incident Date": {

                        "type": "string"

                    }

                },

                "required": [

                    "Claim",

                    "Status",

                    "State",

                    "Incident Date"

                ]

            }

        }

    }

}

[Pstork1]

What are you using as the key field?  If its InternalItemID then you need to add that to the schema.

[rich0087]

@Pstork1  so i think i actaully got it to work by changing the schema to type: array as opposed to object that the sample inputted. 

Thanks for all your help with this and giving me this much simpler option.