Using Python to Download Survey123 Survey in Excel

[DrewThompson]

We are currently creating survey templates using xlsx forms, submitting data in the field using Survey123 Connect, navigating to the Survey123 web platform to export the results to Excel, then modifying the Excel spreadsheet to fit the format we need. We are also using a python code to download the photos into one folder and another software to relabel the photos.

I would like to simplify this process.

I know that to relabel the photos I need the arcpy module from ArcGIS Pro so I'm in the process of getting that approved by my boss. So that takes care of the photo labeling work.

However, I would like to write some code to download the survey data in an excel document and automatically modify the columns and cells.

If it's possible, how would I go about doing that?

[deleted-user-NvcfpBOWaKwr]

Python 3x, arcpy and all of its necessary modules should have already been installed on the machine when you installed ArcGIS Pro. You can check this by launching ArcGIS Pro, go to settings then to python and you can see the installed packages and also the path to the python environment. 

To download an item from ArcGIS Online you will need to use the ArcGIS API for python. Essentially all you do is create a GIS connection (To ArcGIS Online):

arcgis.gis module — arcgis 1.7.0 documentation 

Then  you will get the service item by using content.get and the item ID:

arcgis.gis module — arcgis 1.7.0 documentation 

Then you will export the item to an excel compatible format like CSV or xls by using .export:

arcgis.gis module — arcgis 1.7.0 documentation 

Next you will download the exported item:

arcgis.gis module — arcgis 1.7.0 documentation 

Then you can use XlsXWriter or CSV module to edit and change things around to how you want it. 

Below is an example on how to export from ArcGIS Online. I didn't add in any code for the editing of the excel.

import arcgis

########################################################################################################################
# Variables
########################################################################################################################

agoLogin = arcgis.GIS(url="URL to your ArcGIS Portal use none if to ArcGIS Online",
                      username="Your User Name",
                      password="Your Password")

itemToDownload = agolLogin.content.get("The Item ID")
exportLoc = r"The path where you want the file to download to"
itemExportName = "The Name of the Exported Item"

########################################################################################################################
# Export the ArcGIS Online Item
########################################################################################################################

farmParcelLayer.export(title="Title / Name of the export. This will show up in ArcGIS Online",
                       export_format="Excel",
                       parameters=None,
                       wait=True)

########################################################################################################################
# Search for and get the Newly Exported Item
########################################################################################################################

searchForExportedItem = agolLogin.content.search(query=itemExportName)
exportedItemID = searchForExportedItem[0].id
getTheExportedItem = agolLogin.content.get(exportedItemID)

########################################################################################################################
# Download the Newly Exported Item
########################################################################################################################

getTheExportedItem.download(save_path=exportLoc)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DrewThompson]

Jeremy,

Thank you for responding so quickly!

I'm working on getting this block of code to work. However, my IDE is struggling with "farmParcelLayer" being an unresolved reference. Do you know how I would go about resolving that?

[deleted-user-NvcfpBOWaKwr]

I'm sorry I thought I renamed all of the variables. it should be

itemToDownload.export(title="Title / Name of the export. This will show up in ArcGIS Online",
                       export_format="Excel",
                       parameters=None,
                       wait=True)‍‍‍‍

[DrewThompson]

Jeremy,

Thank you for all your help so far.

I'm still having trouble with this code, though. It seems to be having an issue exporting the file. 

This is the summed up error that I'm getting:

"Unable to export item.
Item type'file' is not supported for export, The item needs to be of type url"
Item '[Item ID]' is of invalid type 'Form' Item type for export needs to be 'Feature Service', 'Vector Tile Service' or 'Scene Service'

(Error Code: 400)

I cut and pasted your code above and filled in my own answers. I've also attempted to use different versions of the Item ID for this survey (using the entire URL, using the form_title, random attempts with different identifiers, etc). I've also gone through and double-checked that everything is spelled correctly and I'm not experiencing any syntax errors either.

Also, when I simply go through the motions of downloading the file, it downloads as an empty text file (the survey has over 100 lines of data so it shouldn't be empty).

Any ideas how I can go about fixing this?

[deleted-user-NvcfpBOWaKwr]

So it looks like you are inputting the Item ID for the actual survey form. When you create and publish a survey to ArcGIS Online it will create a folder for your survey. Within that folder you will see a "Form" and a "Feature Layer" that should have the exact same name. You want to use the Item ID for the feature layer. 

You probably already know how to find the feature layer's Item ID but just in case, Go to ArcGIS Online and login. Then go to "Content" then on the left hand side under "Folders" double click on the Folder for the survey you would like to export the Excel File from. Within that folder you should see a "Form" and "Feature Layer" that have matching names. Click on the feature layer to open its "Overview" page. The Item ID will be located at the end of the URL in your web browser. Here is an example of the full url:

https://yourpage.arcgis.com/home/item.html?id=642f9c5b85e64788a6b2b5418dd61320 

You want all of the numbers and letters after the equals sign and your Item ID should look something like this:

642f9c5b85e64788a6b2b5418dd61320

Hope This Helps

[DrewThompson]

Thanks, Jeremy.

That fix definitely allowed the code to run.

I'm getting a zip file that downloads now with a webform instead of an excel. I think that since I have to navigate to the online forum every time anyways, I might as well continue clicking the "export" button instead of dealing with all the code.

Again, thank you for walking me through these steps.

Have a great week!

[deleted-user-NvcfpBOWaKwr · Accepted solution]

So I figured out the issue. I had a bonehead moment. I try to test every script I give to people to make sure it works. So after pulling the needed code out of one of my other scripts and editing it to give to you. I forgot to save the completed version that worked and ended up copying over the non-working version. I just retested it and fixed the issue. My apologies about that. If you want to revisit the code again below is the working version.

import arcgis

########################################################################################################################
# Variables
########################################################################################################################

agoLogin = arcgis.GIS(url=None,
                      username="Your ArcGIS Online Login Name",
                      password="Your Paassword")

itemToDownload = agoLogin.content.get("Add the Item ID of the feature layer for the survey")
exportLoc = r"Path to the folder where you want to save the excel file"
itemExportName = "Name of the excel file. Make it unique"

########################################################################################################################
# Export the ArcGIS Online Item
########################################################################################################################

itemToDownload.export(title=itemExportName,
                      export_format="Excel",
                      parameters=None,
                      wait=True)

########################################################################################################################
# Search for and get the Newly Exported Item
########################################################################################################################

searchForExportedItem = agoLogin.content.search(query=itemExportName)
exportedItemID = searchForExportedItem[0].id
getTheExportedItem = agoLogin.content.get(exportedItemID)

########################################################################################################################
# Download the Newly Exported Item
########################################################################################################################

getTheExportedItem.download(save_path=exportLoc,
                            file_name="{}.xlsx".format(itemExportName))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DrewThompson]

That worked!!

Jeremy- thank you so much!

[JordanMiller4]

Is there a way to clean up the excel sheets saved on AGOL?