topic Re: Automatecally update data in ArcGis when I update the excel sheet? in ArcGIS Online Questions

Automatecally update data in ArcGis when I update the excel sheet?

CharfeldineMahammedi — Sun, 07 Mar 2021 16:19:24 GMT

Hello, I am wondering if there is any option to automatecally update data in ArcGis when I update the excel sheet?

Your help is appreciated. Thanks

Re: Automatecally update data in ArcGis when I update the excel sheet?

DavidPike — Sun, 07 Mar 2021 18:46:40 GMT

If you can link the feature class to the excel table/spreadsheet by a common field (e.g. the spreadsheet values referencing a matching ObjectID in the feature class), a Join might be a good option. Add Join (Data Management)—ArcGIS Pro | Documentation

edit - not an AGOL solution

Re: Automatecally update data in ArcGis when I update the excel sheet?

jcarlson — Sun, 07 Mar 2021 18:31:52 GMT

I'm assuming you mean in a hosted web layer? If that's the case, there's no way to do it "automatically", but there are ways to make it easier.

Published Standalone File

If your layer is published from a standalone excel file, as in the image above, you can choose to Append to the layer by re-uploading the same file. You can also overwrite the entire file, but if your source data has a unique identifier field, the Append option can be used to maintain existing features.

Slightly Easier Alternative: Cloud Drive

If you have the option, consider adding your data from a cloud drive, such as Google Drive or Microsoft OneDrive. The adding process works the same as when adding from a standalone layer. The key difference here is that the update process just looks for that same file in your cloud drive, and you don't have to maintain an offline copy.

This method is great if you're collaborating with other users on the data, or want to take advantage of the built-in versioning and sharing options of the cloud drive of your choice.

The "Automatically" Part

Finally, whichever option you choose, you can use the Python API to schedule a script which runs the append or overwrite function at regular intervals, thus making the hosted layer as close to a "live" copy of the Excel sheet as is feasible.

Re: Automatecally update data in ArcGis when I update the excel sheet?

CharfeldineMahammedi — Sat, 13 Mar 2021 11:29:25 GMT

Thank you for your help

Re: Automatecally update data in ArcGis when I update the excel sheet?

CharfeldineMahammedi — Sat, 13 Mar 2021 11:32:23 GMT

Thank you for your help.

Re: Automatecally update data in ArcGis when I update the excel sheet?

ellipsol — Fri, 08 Dec 2023 22:42:40 GMT

You can delete existing features from a hosted feature layer and append new features with Python. The example below creates a json from a shapefile which is loaded via edit_features with the adds parameter. The Excel file can probably be subbed for the shapefile. Finally this script can be scheduled as a task to run at the needed frequency.

# import libraries import os import sys import arcgis import arcpy from arcgis.gis import GIS from base import get_logger arcpy.env.overwriteOutput = True # Set up logger file logger = get_logger("z_arl_data_v4", "a") logger.info("Preparing JSON for AGOL") # Set variables ws = "E:/batch/arl/content/" loc = ws pth = os.chdir(loc) output_feature_class = "ARL_data.shp" localJSON = ws + "./arl_content.json" try: if os.path.isfile(localJSON): os.remove(localJSON) except FileNotFoundError: print("Wrong file or file path") # Truncate the existing feature layer def TruncateWebLayer(gis, target): try: lyr = arcgis.features.FeatureLayer(target, gis) lyr.manager.truncate() print("Successfully truncated layer: " + str(target)) except Exception: e = sys.exc_info()[1] print(e.args[0]) # LAPD log in, create a profile so you don't need to show un/pw mygis = GIS("https://site.maps.arcgis.com/", profile="python_playground_prof") # publishedWebLayer is the URL of a single feature layer within a collection in # an AGOL portal publishedWebLayer = "https://services6.arcgis.com/alphanumericpath/arcgis/rest/services/ARL_data/FeatureServer/0" # a feature class on the local system with the same schema as the # portal layer updateFeatures = os.path.join("E:/batch/arl/content/" + output_feature_class) # remove all features from the already published feature layer feature_layer_item = mygis.content.search("arl whole file")[0] flayers = feature_layer_item.layers flayer = flayers[0] flayer.delete_features(where="area > 0 OR area =0") logger.info("Deleting existing features from AGOL") # reference the empty layer as FeatureLayer object from the ArcGIS Python API fl = arcgis.features.FeatureLayer(publishedWebLayer, mygis) # create a JSON object from the local features jSON = arcpy.FeaturesToJSON_conversion(updateFeatures, localJSON) # create a FeatureSet object from the JSON fs = arcgis.features.FeatureSet.from_json(open(localJSON).read()) # logstuff("Creating a featureset from the JSON") # add/append the local features to the hosted feature layer fl.edit_features(adds=fs) # logstuff("Appending latest data to the hosted feature layer") logger.info(arcpy.GetMessages(1)) logger.info("Finishing steps for load to AGOL")