3

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.


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?

2 Answers 2

4
+50

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

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.