Convert JSON to Excel

Hello All,

I have tried few solutions to Covert JSON to Excel. But nothing works out.

2019-05-28_21h31_32

DOC042319-04232019125916.1.1.json (3.4 KB)

You can do this within excel using power query.

Open Excel (v2016 or later)
Go to Data>NewQuery>Other>FromWeb

Paste in the file path to your JSON file.

You can then use the tools in PowerQuery to access the data you need and set up your table.

If your JSON data file is dynamic, or changing regularly, you can save the PowerQuery query linked to the file, and a simple ExecuteMacro activity to refresh all queries will then update the query everytime you run your workflow.

Is there any .xaml i can refer to.

This just accesses the values directly from your JSON file and outputs to a datatable. You want to deserialize the file first. Hopefully it meets your requirements. However, you can just change the item names you are looking to access where you need, should be easy enough to adjust.

Main.xaml (12.3 KB)

Thanks,

I have to read many Jsons from a folder and write respective data in Excel row by row. I have updated code accordingly by referring your code. But i am getting Object Reference error because as you now we are reading Title as “_TexasTitle:_TexasTitle” which will get change as per selected Json like “_CTTitle:_CTTitle” or “_NYTitle: NYTitle”.

JsonObject%20Refrence
JsonTitle_51
JsonToExcel.xaml (22.9 KB)

Thanks ronanpeter,

I did some String operation and solve the issue.
Marking Solution to you post.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.