Overwrite Hosted Table with new data

[ChrisMalam]

Hi,

I am having a challenging time trying to write a code using the ArcGIS Python API in Notebooks to update or overwrite an existing hosted table with new data obtained via a Survey123 form. The ultimate goal will be to set this to update automatically at regular intervals.

I have tried to resolve using similar previous posts (e.g. Overwrite Hosted Table), but to no avail.

My initial steps were to run my analysis code and add the output table as a CSV to my organisational AGOL account, which I then manually published as a Hosted Table. However, when I run the below steps, the output messages appears to be fine, but when I go to check the hosted table data in my 'Content', it has not updated. Code is in the image, below:

As additional context:

• Given my analysis is all scripted, the hosted table name, properties and schema are remain the same.
• Code runs ALL Survey123 data each time, rather than just those new records submitted since last run (there's are <1000 records).

Any assistance would be much appreciated as I am still relatively new to Notebooks and python language. Happy to include additional information if needed.

Thanks!!

[KevinMacLeodCAI]

Hopefully this will get smoother but for now there seem to be various issues with replacing an item. However, an alternative strategy we have had success with is looping through and deleting all rows and then writing all new records to the item with cursors.

[jcarlson]

If you've got an input table, a target feature layer, no changes to the schema, and you're working with the ArcGIS Python API, what you need is a spatially enabled dataframe. It does appear that you're working with a dataframe already in your screenshot, is that correct? Just call .spatial on the dataframe to access the additional capabilities added in the ArcGIS Python API.

Try something like this:

new_data = agg2[(agg2.Site == 'Callumbrae')]

rabbit = gis.content.get('your-itemid')

rabbit.tables[0].manager.truncate()

rabit.tables[0].edit_features(adds=new_data.spatial.to_featureset())

[ChrisMalam]

Hi Josh,

Thanks for the suggestion - when I tried running on my spatial dataframe I get the following error on the "rabbit.tables[0].manager.truncate()" command:

"IndexError: list index is out of range"

[jcarlson]

Hm. Is it a feature layer with some geometry, and not a table? In that case, it would be rabbit.layers[0] etc etc.

[Kara_Shindle]

I've also used the following successfully - my sources vary from CSV to Excel to Google Sheets, Smartsheets, etc.

commRecTable = gis.content.get('{insert item ID here')
commRec_collection = FeatureLayerCollection.fromitem(commRecTable)
commRec_collection.manager.overwrite(commRecCSV)

  This one was created by using a spatially enabled data frame and manipulating my date fields, etc. using Pandas, then exporting it to a CSV.  I then overwrite and upload my new CSV every hour.  I do this for about 6 different items. 

[DaveAlmond]

This worked like a charm and is so much simpler than most of other material out there on this topic. Thanks Kara! 

[JoshObrecht1]

I have tried this with a hosted table, however I am receiving the below error when running overwrite. Any thoughts?

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

[FredMitchell]

Hi Kara, thanks for sharing this code! I've been trying to overwrite a hosted table in AGOL but I keep getting the following error: 

AttributeError: 'NoneType' object has no attribute 'type'

 Have you ever encountered this. I've been reading other posts by @JakeSkinner and trying to get this to work but no luck yet! Any thoughts anyone might have would be greatly appreciated. The hosted table is not spatial so I'm wondering if that has something to do with it. It's just 3 columns and a few rows of data that I'm extracted and using for a dashboard. Would love to be able to automate this!

[JakeSkinner]

@FredMitchell can you share the service to an AGOL group and invite my account (jskinner_rats)?  I can take a look to see if anything jumps out at me.