Survey 123 data to Excel file scheduled with Python Script

2127
6
Jump to solution
03-29-2021 03:50 PM
ClintBoaz1
New Contributor III

I am trying to write a script that will automate exporting the attribute data in the survey 123 hosted feature layer from AGOL to an excel file.  As I understand it I need to do this from within the ArcGIS Pro environment as I want the columns to be named with the name value from the survey 123 connect XLS form as opposed to the label.  I would also like this script to be able to be run on a schedule as it is written without needing to update information before every run.  Every time a new record is collected however the name of the .gdb file that is extracted from the zipfile changes and new records are not captured in the excel table.  Additionally, the script still requires me to manually delete the excel/csv file each time to rerun the script...  It won't overwrite despite the overwriteoutput = True statement.  Is there a better workflow to download attribute data to an excel file with Python from within the ArcGIS Pro environment?  Any help appreciated, Thanks

Script.JPG

1 Solution
6 Replies
MarthaRodgers
New Contributor III

When I see the words automate and Survey123, I automatically think "Integromat".

If it were me, that's what I would use for this scenario.  You could easily watch a survey and then write out the attributes to an Office365 Excel sheet or Google Docs.  You can even schedule this to run on certain times/days.

Screen Shot 2021-03-29 at 4.04.27 PM.png

Here is a blog about it.

https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-integromat/ba-p/89...

-Martha

 

ClintBoaz1
New Contributor III

Hey Doug,

Thanks for the reply.  If I run this tool on the feature class in my downloaded database I will still have the problem of my database name changing every time a new record is submitted and the script will point to the old geodatabase not capturing new records.  Is there a way to run this tool and point to a AGOL hosted feature layer as the the input?  

0 Kudos
MicZatorsky_AEC
Occasional Contributor III

Yes, Table to Excel will work on a hosted Feature Layer

ClintBoaz1
New Contributor III

Hey Doug and Mic,

Thanks for the help.  I ended up pointing the Table to Excel tool to the hosted feature layer in AGOL and it spit out the table formatted how I wanted it with the names instead of the labels.  I don't work in AGOL very much so I didn't know how to point the tool to the hosted feature layer which is why I started down the other path but this seems much more straight forward. Thanks again! 

DougBrowning
MVP Esteemed Contributor

If this tool is in Pro you can use a Hosted service as a input.  Have not tried it.

Have you tried to export this way?  Change type from GDB to excel.  Not sure what the name options are there.

# Setup inputs
configFile = r"in"
backupDir = r"out"

# Make a connection to ArcPro
gis = GIS('pro',verify_cert=False)
#gis = GIS('pro')

# read in the config file for list of HFS for Survey123
BackupAllAGOHFSConfigFile = open(configFile, 'r')
HFSList = BackupAllAGOHFSConfigFile.read().splitlines()
BackupAllAGOHFSConfigFile.close()


for HFS in HFSList:
    HFSname = HFS.split(",")[0]
    itemId = HFS.split(",")[1]          # this now comes from the main AGO page with all the settings.

    # Start the export to GDB job
    print ("Export job started for " + HFSname)
    fsLink = gis.content.get(itemId)
    result = fsLink.export("tempOut"+ HFSname, "File Geodatabase")

    # Save to file system
    dt = time.strftime("%Y%m%d_%H%M%S")
    out_file = os.path.join(backupDir,"{}_{}.zip".format(HFSname,dt))
    print ("Saving final downloaded FGDB to {}...".format(out_file))
    result.download(backupDir, out_file)

    # Remove the extracted FGDB from AGOL (cleanup)
    print ("Removing the export file from AGOL")
    deleteResult = result.delete()
    print ("Delete result is " + str(deleteResult))