Select JSON records as an Excel table

Question

I have an internal map service that serves up GIS records via JSON objects.

For the purpose of this question, instead of using my internal/private data, we can use the JSON data on ESRI's public Sample Server 6.

• https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&f=pjson

---

Question:

I want to select the JSON data and insert it into an Excel table.

In other words, I want to take the JSON data:

enter image description here

And put it into a tab in an Excel spreadsheet as rows:

enter image description here

How can this be done in Excel 2016?

Answer 1

The 1min job using OGR/GDAL command line. I've changed f=pjson to f=json in your url. You will just need to open the CSV.

ogr2ogr -f CSV localgovernment_events.csv "https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&f=json" -lco GEOMETRY=AS_WKT

For points, you may prefer an output with X and Y columns instead of WKT

ogr2ogr -f CSV localgovernment_events.csv "https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&f=json" -lco GEOMETRY=AS_XY

You can also use QGIS by using f=geojson like below. It will load as a layer. Then, you will right click on the layer "Export" -> "Save Features As...", choose to export to CSV format or Excel or OpenDocument (LibreOffice format). Be aware that output format does not always keep geometry in the output file.

enter image description here

Answer 2

I can use Excel Power Query.

The steps are pretty unintuitive, so I'll spell them out for future reference:

1. Excel 2016 >> Data tab >> From Other Sources >> From Web
2. Paste the URL
3. Click Edit
4. Remove the Changed Type step
5. In the Source step's settings, change Open file as from CSV to Json
6. Click the features row
7. Click Into Table
8. Click the List hyperlink in the features row
9. Click To Table
10. Click Ok
11. In the header for Column1, click the arrows icon: enter image description here
12. Unselect geometry, if applicable.
13. Click Ok
14. In the header of Column1.attributes, click the arrows icon: enter image description here
15. Unselect Use original column name as prefix, if applicable.
16. Click Ok
17. Change/correct the data types for the columns. Note: I couldn't figure out how to display the dates correctly.
18. This is what the resulting data/table looks like in the Power Query window: enter image description here
19. Click Close & Load
20. This is what the resulting table in the Excel spreadsheet looks like. enter image description here
21. The query can be refreshed anytime: Data tab >> Refresh All.
22. Additionally, the refresh settings can be set so that the query refreshes when the spreadsheet file is opened.

For more details, see the answers in this post: Select JSON records as Excel table via Power Query