topic Re: JSON Formatting in ArcGIS for Excel in ArcGIS for Office Questions

JSON Formatting in ArcGIS for Excel

AndrewFaulkner — Tue, 04 Jan 2022 01:32:42 GMT

Hi, I am trying to create line features with the ArcGIS for Excel extension, from a JSON file I have exported from ArcGIS Pro. With reference to the screenshots attached, can anyone advise how I need to reformat the JSON (using power query editor or other means) in order for ArcGIS for Excel to recognise the JSON geometry?

Many Thanks,

Andrew

Re: JSON Formatting in ArcGIS for Excel

Anonymous User — Thu, 06 Jan 2022 14:18:02 GMT

Hello!

It maybe that the geometry isn't getting formatted properly as it imports into Excel. We have a connector now for Power Automate that will properly format the JSON so that you do not have to perform any gymnastics in Power Query.

https://www.esri.com/arcgis-blog/products/power-bi/announcements/arcgis-connectors-in-power-automate/

If you have the time, I'd love to discuss your workflows to see if theres anything else we can do to support you. Feel free to DM me your contact info so we can connect.

Cheers,

Andrew

Re: JSON Formatting in ArcGIS for Excel

AshmithaNihar — Fri, 14 Jul 2023 07:59:26 GMT

Can You help me with the same. I have to import a json file and view the geometry in excel. I'm stuck quiet from the beginning. Please help me

Re: JSON Formatting in ArcGIS for Excel

Yuchen111 — Wed, 13 Mar 2024 07:55:14 GMT

Hi!

Thanks for introducing power automate to us.

Could you please just show us maybe a screenshot of a properly formatted geometry in excel? Since currently I'm only focusing on solving my problem via excel.

So far I believe I share the same question as Andrew does. If I didn't guess wrong, I think what we want is an example of a correctly & properly formatted column (or a couple of columns) in Excel that can be recgonised by Arcgis for Excel to show features like lines and polygons in the map.

We don't have trouble dealing with points simply because a pair of lng-lat coordinates are valid enough to visualise its location. But in case of lines and polygons, it's not enough anymore, which leads us to try geojson file instead.

Thank you!

Yuchen

Re: JSON Formatting in ArcGIS for Excel

Gaurav-Singh — Wed, 20 Mar 2024 16:56:56 GMT

Attached EsriJSON sample datasets (points, polygons, and polylines) that works with ArcGIS for Excel. Hope it helps.

You may use the ArcGIS Pro tool 'Feature to JSON' to convert features to Esri JSON format - https://pro.arcgis.com/en/pro-app/3.1/tool-reference/conversion/features-to-json.htm

Re: JSON Formatting in ArcGIS for Excel

Yuchen111 — Thu, 06 Jun 2024 02:26:45 GMT

Thank you SOOO much! This solves a long time puzzle for me and helps me a lot!

Re: JSON Formatting in ArcGIS for Excel

MJB62 — Wed, 12 Jun 2024 13:14:31 GMT

Not able to find the connector for Power Automate to format the JSON in Power Query. I'm new to Power Query, and very little experience with JSON. Spent a couple of days searching the ESRI website and other web searches and have no idea how to bring the JSON data into Excel. Would ne grateful for any advise.