Select to view content in your preferred language

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

3216
5
03-07-2021 08:19 AM
CharfeldineMahammedi
Emerging Contributor

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

0 Kudos
5 Replies
DavidPike
MVP Frequent Contributor

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

CharfeldineMahammedi
Emerging Contributor

Thank you for your help

0 Kudos
jcarlson
MVP Esteemed Contributor

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

jcarlson_0-1615141171171.png

 

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.

jcarlson_1-1615141325039.png

jcarlson_2-1615141404113.png

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.

jcarlson_3-1615141717150.png

 

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.

- Josh Carlson
Kendall County GIS
0 Kudos
CharfeldineMahammedi
Emerging Contributor

Thank you for your help.

 

0 Kudos
ellipsol
Regular Contributor

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")

 

0 Kudos