Convert Excel to Json

Like (1) Share

Report

Posted on 7 Nov 2022 23:01:55 by garry9318

Hi,

I have the below table in an excel file and want to convert to json with nested array Properties as shown in the example below.

How can I achieve this please through an automated flow?

Excel Data:

OwnerID	PropertyID	Property Address	Rent	Bedroom
L001	P001	12 St LA	300	2
L001	P002	3 St SA	450	3
L002	P003	4 St SA	500	4
L003	P004	20 St LA	400	2

Output Required in Json:

{

"owner1":{

"id":"L001",

"property":[ {

"propertyID":"P001",

"Property address":"12 st LA",

"Rent":"$300",

"Bedroom":"2"

{

"propertyID":"P002",

"Property address":"3 st LA",

"Rent":"$450",

"Bedroom":"3"

}

]

"owner2":{

"id":"L002",

"property":[ {

"propertyID":"P003",

"Property address":"4 st LA",

"Rent":"$500",

"Bedroom":"4"

}

]

"owner3":{

"id":"L003",

"property":[ {

"propertyID":"P004",

"Property address":"20 st LA",

"Rent":"$400",

"Bedroom":"2"

}

]

}

Thank You.

All responses (11)

Answers (0)

garry9318 48 on 08 Nov 2022 at 07:05:08

Like (1)

Report

Re: Convert Excel to Json

Hi @grantjenkins

Thank You so much for your help, finally it worked.
grantjenkins 11,059 Super User 2025 Season 1 on 08 Nov 2022 at 06:02:22

Like (0)

Report

Re: Convert Excel to Json

Also, can you check that the input into your Apply to each is using the output from the Compose and not the Select.
grantjenkins 11,059 Super User 2025 Season 1 on 08 Nov 2022 at 05:58:56

Like (1)

Report

Re: Convert Excel to Json

I don't think that's the issue actually.

It seems as though your Name value isn't coming through. Can you run the flow again and have a look at the output from your List rows present in a table, and the following Select. I wonder if your Name column in Excel is coming through with a different header (maybe not called Name).
grantjenkins 11,059 Super User 2025 Season 1 on 08 Nov 2022 at 05:54:55

Like (0)

Report

Re: Convert Excel to Json

In your first Select can you change the Header from Id to id (lowercase i) to see if that fixes it.
garry9318 48 on 08 Nov 2022 at 05:38:26

Like (1)

Report

Re: Convert Excel to Json

Hi @grantjenkins

Here's the flow inputs:
union(body('Select'),body('Select'))

items('Apply_to_each')?['id']

item()?['PropertyID']
item()?['Address']
item()?['Market rent']
item()?['No of Bedrooms']

concat('"', items('Apply_to_each')?['Name'], '":{"id":"', items('Apply_to_each')?['id'], '","property":', body('Select_Properties'), '},')

json(variables('output'))

Thank You.
grantjenkins 11,059 Super User 2025 Season 1 on 08 Nov 2022 at 05:19:38

Like (0)

Report
Re: Convert Excel to Json

Are you able to send through screenshots of your flow to see what you have put for each of the inputs, etc.?

Below is the output I get:

{ "owner1": { "id": "1", "property": [ { "PropertyID": "P001", "Address": "12 st LA", "No of Bedrooms": "2" }, { "PropertyID": "P002", "Address": "14 Louis st LA", "No of Bedrooms": "2" } ] }, "owner2": { "id": "2", "property": [ { "PropertyID": "P003", "Address": "15 Merry st LA", "No of Bedrooms": "3" }, { "PropertyID": "P004", "Address": "29 John st LA", "No of Bedrooms": "2" } ] }, "owner3": { "id": "3", "property": [ { "PropertyID": "P005", "Address": "8 Grant st LA", "No of Bedrooms": "3" } ] } }
garry9318 48 on 08 Nov 2022 at 05:05:49

Like (0)

Report

Re: Convert Excel to Json

Hi   @grantjenkins
Thanks for your help, I have tried it and it is working, but I am not getting the Json code as required and not even getting the full Code.

Following Excel table:
Name id PropertyID Address Market rent No of Bedrooms
owner1 1 P001 12 st LA $300 2
owner1 1 P002 14 Louis st LA $500 2
owner2 2 P003 15 Merry st LA $550 2
owner2 2 P004 29 John st LA $1093 2

Output got :
{
  "": {
    "id": "2",
    "property": [
      {
        "PropertyID": "P003",
        "Address": "15 Merry st LA",
        "Market rent": "$550",
        "No of Bedrooms ": "2"
      },
      {
        "PropertyID": "P004",
        "Address": "29 John st LA",
        "Market rent": "$1093",
        "No of Bedrooms ": "2"
      }
    ]
  }
}

Required Output:

{
"owner1":{
"id":"L001",
"property":[ {
"propertyID":"P001",
"Property address":"12 st LA",
"Rent":"$300",
"Bedroom":"2"
},

{
"propertyID":"P002",
"Property address":"3 st LA",
"Rent":"$450",
"Bedroom":"3"
}

]
},

"owner2":{
"id":"L002",
"property":[ {
"propertyID":"P003",
"Property address":"4 st LA",
"Rent":"$500",
"Bedroom":"4"
}
]
},

"owner3":{
"id":"L003",
"property":[ {
"propertyID":"P004",
"Property address":"20 st LA",
"Rent":"$400",
"Bedroom":"2"
}
]

}

}

Can you help me on that please.
grantjenkins 11,059 Super User 2025 Season 1 on 08 Nov 2022 at 02:17:44

Like (2)

Report
Re: Convert Excel to Json

This should get you what you want. The only thing you'll need to do is add your additional columns to the Select action.

I've got the following Excel Table for this example:

The full flow is below. I'll go into each of the actions.

List rows present in a table retrieves your Table data.

Select retrieves just the Name and Id properties which we'll use to loop through later.

Compose uses a union expression to remove any duplicate values from Select as we only want one instance of each owner to loop through. The expression here is:
union(body('Select'),body('Select'))

I then create a string variable called output that will hold the final output once we combine our owners, properties, etc. I set the initial value to {

The Apply to each uses the output from our Compose since we want to loop through each unique owner.

Filter array returns rows that match the current owner's Id using the following expression:
items('Apply_to_each')?['Id']

Select Properties takes the output from Filter array and allows us to specify exactly what properties we want to include. You'd need to add your additional fields here. We don't include Name and Id here as we already have them outside the objects. The expressions used here are:
item()?['PropertyID'] item()?['Address'] item()?['Number of Bedrooms']

Compose Build then concatenates all of our data to build up the JSON array you're after. You should be able to just copy the expression below if you've named all your actions the same as what I have.
concat('"', items('Apply_to_each')?['Name'], '":{"id":"', items('Apply_to_each')?['Id'], '","property":', body('Select_Properties'), '},')

I then append that output to the output string variable.

Then outside the Apply to each we do the following:

We append a closing } to the output string variable.

And finally, we convert the output to JSON using a json expression.
json(variables('output'))
SudeepGhatakNZ 14,303 Most Valuable Professional on 07 Nov 2022 at 23:39:26

Like (1)

Report

Re: Convert Excel to Json

@garry9318 ,

You can query your Excel file and then use Select action to form a JSON array.
garry9318 48 on 07 Nov 2022 at 23:33:07

Like (0)

Report

Re: Convert Excel to Json

Hi @SudeepGhatakNZ

I don't think this will help me in that way, I want this in a Microsoft power flow, can you help me in that, please.