topic Re: Using excel to access published tables in ArcGIS Enterprise Questions

Using excel to access published tables

James_Armstrong — Thu, 29 Jul 2021 00:57:10 GMT

Im not sure what (if any) would be the best way to go:

Data coming from PostSQL database
Publishing the feature datasets to Portal (10.9) from database.
We have some tables and related tables in database, these can be published as well
Data in tables changes and needs to be updated periodically

We would like to be able to connect to the table data using excel so non-GIS staff can edit as needed.

Should we try to set and use excel to connect directly to PostSQL. (ODBC connection?)
IS there a way to use excel to connect to the published data (Portal)? SO far we have not found one.

Any suggestions, advice, or direction will be most appreciated.

James Armstrong

Re: Using excel to access published tables

MichaelGinzburg — Thu, 29 Jul 2021 05:25:46 GMT

Portal distributes data by REST services.

You can try to use webservice() Excel function.

https://www.microsoft.com/en-us/microsoft-365/blog/2013/03/21/use-webservice-functions-to-automatically-update-excel-2013-spreadsheets-with-online-data/

Re: Using excel to access published tables

DerekLaw — Thu, 29 Jul 2021 14:11:32 GMT

Hi @James_Armstrong,

> Im not sure what (if any) would be the best way to go:

Data coming from PostSQL database
Publishing the feature datasets to Portal (10.9) from database.
We have some tables and related tables in database, these can be published as well
Data in tables changes and needs to be updated periodically

For the above workflow, you could create query layers in ArcGIS Pro, Query layers.

> IS there a way to use excel to connect to the published data (Portal)?

ArcGIS for Office enables MS Excel users to connect to an ArcGIS portal and view its data, but it does not support the ability to edit data.

Hope this helps,

Re: Using excel to access published tables

James_Armstrong — Thu, 29 Jul 2021 18:36:22 GMT

Derek, I kinda knew that already, but just wanted to be sure I hadn't missed anything. I like the idea of the Query View. I believe this would work well and I also believe I could connect to the actual database in PostSQL using Excel. If this is true, then perhaps the specific staff members could update the tables and these updates would be reflected in the query view.

Thanks for the nudge

James