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
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
Thank you for your help
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.
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.
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.
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.
Thank you for your help.
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")