Automatic update of an hosted layer from emailed CSV file daily

[KenBouchard]

Hi,

I'm looking to get some advice/insights/help on how to automate processes.

I'm receiving, each day, an email with a .csv file that I'm using to update an hosted layer in AGOL.

The manual process is pretty simple as described below

1- I get the email in Microsoft Outlook.

2- Save the .csv to my local machine.

3- I update (overwrite entire layer option) the hosted layer in AGOL with that .csv.

It's an easy task but redundant and would fit well for being automated.

Would it be possible to automate this process?  Any advices?

Ken

[jcarlson]

First, I'd advise against the overwrite method, as layers and dependencies can break that way. It's better to truncate the layer and then load data into it from your source.

Second, I would see if there are any alternate methods of getting the data. Could it be loaded to a shared drive / folder somehow? More details about the source would be helpful. For automating, it becomes much easier if there is a fixed location to look for the file, such as a URL or file path. We have a daily update process that used to come from an emailed file, but we were able to figure out how to scrape the website the data was coming from, and bypassed the email / file stuff entirely.

If email is truly the only option, consider looking into something like Make (formerly Integromat) to at least handle some of it. If the emails come in a consistent format, then you could possibly create a scenario that grabs those particular emails, pulls out the attached file, and submits it to a REST endpoint. I haven't done that myself, but it might be helpful if all else fails. But I would make sure whether or not other delivery options are available.

[JohnCodd]

Hi  @jcarlson  - Can you provide the Esri Links to the approach(es) you have shared with Ken and the community?  What is "truncate" and "load from source"?  Is this procedure completed from ArcPro or from AGOL?  Thank you.

John

[jcarlson]

Truncate removes all rows from a table and sets the objectID back to 0, but does not touch any part of the layer's definition or settings, making it particularly useful for situations where folks need to refresh the entire layer.

Loading from the source just means using the source data, be it a CSV or something else, and using it in a standard append process, either through Pro, AGOL, where there are built-in methods for doing so, or through some other means entirely. Instead of opening the data and overwriting the destination layer, the source layer is the input in an append process.

Neither of these concepts are specific to Esri tools, and can be accomplished in a LOT of different ways. We use the ArcGIS Python API for most of ours. You can see an example of it here: https://github.com/jdcarls2/ilgisa-2022/blob/main/hosted-copy/hosted-copy.ipynb

Being run entirely in Python, it's OS agnostic, so you could do it anywhere.