Convert JSON to Excel

[husrathe (Harshwardhan Usrathe)]

Hello All,

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

DOC042319-04232019125916.1.1.json (3.4 KB)

[ronanpeter]

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.

[husrathe (Harshwardhan Usrathe)]

Is there any .xaml i can refer to.

[ronanpeter]

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)

[husrathe (Harshwardhan Usrathe)]

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”.

JsonToExcel.xaml (22.9 KB)

[husrathe (Harshwardhan Usrathe)]

Thanks ronanpeter,

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