Survey123 Tricks of the Trade: Download survey data (with attachments)

[IsmaelChivite]

Do you want to download survey data, including photo attachments? This blog describes, step by step, how you you can do it.

Before we start

Before we start, let me point out that there are handy Export tools in the Survey123 website. They let you export your survey data as a CSV, Excel, KML, Shapefile or File Geodatabase. You can choose to export all your survey records, or just a subset.

You can also export your survey data from the item details page of your survey's layer item.

The only caveat with these export methods is that you can't download your photo attachments. Technically, you could get your images through the file geodatabase format, but what if you want to export your data and have all your photos in their original format in a folder?   That's what this blog post is about.

It is going to take a Python script and ArcGIS Pro

Downloading your photos and documents in their original format requires a Python script and ArcGIS Pro. I will describe how to do it step-by-step. Even if you do not have experience with Python, you will be able to do this.

The Python script was developed by @ZacharySutherby  Zach loves Python and he is always looking for new ideas to automate common Survey123 tasks. The script is part of the Survey123 developer documentation.

Download data and attachments

• First, open a web browser and navigate to the Export survey data with attachments Python script
• Click on the Try it live link. This will open a new browser tab

• Click on the Download button and place the .ipynb file into a well-known location on your computer

• Next, open a New project in ArcGIS Pro
• In ArcGIS Pro, sign in to your ArcGIS Online or ArcGIS Enterprise organization, if not already
• Open the View ribbon and make sure the Catalog Pane is visible

• Open the Analysis ribbon, expand the Python menu and add a new Python Notebook.

• From the Catalog pane, right click on the Notebooks category and click on Add Notebook

• Use the Add Python Notebook dialog to browse for and select the .ipynb file you downloaded before

• Double-click on the export_survey_data_with_attachments.ipynb notebook

At this point, the notebook will open. With a few minor changes it will be ready to run.

• Scroll down until you see the second code cell, as shown in the screenshot below

• Since you are already signed in, you can ignore the first three parameters (portalURL, username, password)
• The survey_item_id uniquely identifies the survey for which you want to download data. Here is how you get the survey_item_id value:
  • In a web browser, sign in to survey123.arcgis.com
  • From the survey gallery, click on your survey
  • Get the survey ID as specified below

• Now, take your survey_item_id and replace it in the Python code
• Change the save_path parameter so it targets an existing directory in your own computer

Earlier, I asked you to disregard the portalURL, username and password parameters. If you ignored me and set those up, then you can skip the next instruction.  If you left the portalURL, username and password parameters unchanged, then follow the next step

• In the gis = GIS (portalURL, username, password) line, replace the contents within the brackets with "pro". That is, leave it as: gis = GIS ("pro").  This will essentially reuse the current login information within ArcGIS Pro to run this script and access your survey's data

•  You are now ready to run the script!
• Open the Cell menu and click Run all

The script takes about 20 seconds to initialize. Be patient.

• While the script runs, open the target folder and observe how data is downloaded.
  • You will see a spreadsheet created. It contains the records from your survey.
  • You will then see a second spreadsheet describing what attachments correspond to each record. Note that you could have multiple files (attachments) associated with one record.
  • Finally, a folder will be created for your attachments.The script will download photos, signatures as well as audio files any other type of document attached in this folder.

The script has a few other parameters that you can tweak. It is all superbly documented so I do not anticipate you will run into any issues with them.

More scripts

This is one of many scripts available through the Survey123 developer documentation.  I encourage you to explore and play with other scripts. You will find samples to programmatically create reports, move surveys across organizations and more.

Many thanks to @ZacharySutherby and the Survey123 documentation team for their great work with this. If you have ideas for new scripts, post your comment below.

[DougBrowning]

If interested I took it a level farther.  I calculate a image name in the form then use that to rename the image as I export them so they have more meaningful names.  I also create a directory that is the name of the Primary key field as I export so it builds out a meaningful directory structure.

See code here  https://community.esri.com/t5/arcgis-survey123-questions/match-survey123-photo-attachments-to-featur...

Then in this last project I got more advanced by adding a comment field for each photo in the form.  The script then uses exif.exe to add those comments right into the photo Exif info.  

Then for the final step I create simple html pages that have thumbnails for all the photos with the comments from the Exif on the page below the photos.  The thumbnails all link to the full size photos.

Then in our DB we have a link to the web dir / Primary Key / index.html.  These links then launch the little gallery.  Cool thing is this all works even in a Excel export.

See both samples below.  Little more advanced but it came out slick.

Script that renames and adds Exif comments.  Note you need the Exif.exe tool and the Python wrapper called exiftool for this to work.

#       Trying to add in comments to Exif
#       Added Boating section
#       Note for 2022 changing to PhotoEvaluationID in FPW so then this should be the only script needed
#       Use comments below to run each of the 3
#-------------------------------------------------------------------------------

import os, arcpy
import exiftool

# vars--------------------
inDB = r"EarlySeason.gdb"

# Photos form
photoForm = "PhotosRepeat"
# actual attachements table
attachName = "PhotosRepeat__ATTACH"

# For Boating
##photoForm = "BoatingPhotosRepeat"
### actual attachements table
##attachName = "BoatingPhotosRepeat__ATTACH"

# for FPW form
##photoForm = "FloodpronePhotos"
### actual attachements table
##attachName = "FloodpronePhotos__ATTACH"

outDir = r"C:\temp\CanyonPhotos"

# should be standard
fldBLOB = 'DATA'
fldAttName = 'ATT_NAME'

#dateLoadedInDb = "2020-09-01"

# vars--------------------

print "Starting"

# join attach table to Repeat table
arcpy.MakeTableView_management(inDB + "\\" + attachName, "attachView")
arcpy.AddJoin_management("attachView", "REL_GLOBALID", inDB + "\\" + photoForm, "globalid")
# funky due to the join
fieldList = [attachName + "." + fldBLOB, attachName + "." + fldAttName, photoForm + ".PhotoEvaluationID", photoForm + ".PhotoID", photoForm + ".PhotoComments", photoForm + ".State"]
with arcpy.da.SearchCursor("attachView", fieldList) as cursor:
   for row in cursor:
      binaryRep = row[0]

      # save to disk
      # build output path of base + EvaluationID
      if row[2] == None:
        print "Warning Null values found in EvaluationID"
        print "   ATT_NAME is " + row[1]
      else:
        # for now it is base + EvaluationID
        # now added in state
        baseOut = outDir + "\\" + row[5] + "\\" + row[2]
        # check to see if dir already there - if not create it
        if not os.path.exists(baseOut):
            os.makedirs(baseOut)

        fileName = row[3] + ".jpg"

        # in case some / got in there.  this should not happen now!
        fileName = fileName.replace("/","")

        # save out the file
        open(baseOut + os.sep + fileName, 'wb').write(binaryRep.tobytes())

        # now set the Exif Windows Comment fields to the photo comment
        #skip if comment is blank
        if row[4]:
            jpgFile = baseOut + os.sep + fileName
            with exiftool.ExifTool() as et:
                #jpgComment = et.get_tag("XPComment", jpgFile)
                #print jpgComment
                #newComment = '-XPComment=' + "test"
                # take out special chars
                newComment = '-XPComment=' + ''.join(i for i in row[4] if ord(i)<128)
                #add flag so taht does not crea
                et.execute(bytes(newComment), bytes(jpgFile), bytes("-overwrite_original_in_place"), bytes("-preserve"))
                #et.execute(bytes(newComment, 'utf-8'), bytes(jpgFile,"utf-8"))

del cursor
print "All Done"

Take out the comments and create HTML page

#-------------------------------------------------------------------------------
# Name:        Create HTML for all images dirs with Exif Comments.py
# Purpose:     Searches a dir structure for any JPGs or jpgs.  If founc makes a index.html for each dir
#               Used for the AIM Data Poral for easy image viewing
#               Note using HTML5 which may cause issues on really old browsers
#               For Lotic so far
#
#   Dependencies:
#           Need to download ExifTool exe from Phil Harvey. Reanme to remove the -K stuff so it is just exiftool.exe.
#               Put it somewhere then add that place to the windows Enviroment Path var
#           Need to install the PyExifTool which is a python wrapper for ExifTool.  Can use Pyton easy-install exiftool?? Or can just copy from below
#           See 
#-------------------------------------------------------------------------------
import os
import exiftool

inDir = r"C:\temp\OR\PC-TR-32733_2021-07-12"

print "Starting HTML"

for dirpath, dirnames, filenames in os.walk(inDir):

    fileList = os.listdir(dirpath)

    # get a list of jpgs in the folder
    jpgList = []
    for file in fileList:
        if file.endswith("JPG") or file.endswith("jpg") or file.endswith("jpeg") or file.endswith("JPEG"):
            jpgList.append(file)


    # only run on dirs with photos
    if len(jpgList) > 0:



        htmlList = []

        # build the html
        htmlList.append("<!doctype html>\n")
        htmlList.append("<html>\n")
        htmlList.append("<head> <style> img { border: 5px solid #FFFFFF; } </style> </head>\n")
        htmlList.append('<title>' + inDir.split("\\")[-1] + '</title>')
        htmlList.append("<body>\n\n")

        for jpg in jpgList:
            htmlList.append('  <div style="float:left">\n')
            htmlList.append('    <a href="' + jpg + '"><img src="' + jpg +'" width="450" style="max-width: 100%;">\n')
            htmlList.append('    <figcaption style="margin-right:2em;">' + jpg + '</figcaption></a>\n')
            with exiftool.ExifTool() as et:
                jpgComment = et.get_tag("XPComment", dirpath + "\\" + jpg)
            #only write if not blank and take out special chars
            if jpgComment != None:
                # took out special on the way in so should be ok here
                #jpgComment = ' '.join(i for i in jpgComment if ord(i)<128)
                htmlList.append('<p style="width: 450px; word-wrap: break-all;">' + str(jpgComment) + '</p>')
            htmlList.append('  </div>\n')

        htmlList.append("\n</body>\n")
        htmlList.append("</html>\n")

        # now write it all at once
        htmlFile = open(dirpath + "\\index.html","w")
        htmlFile.writelines(htmlList)
        htmlFile.close()


print "Done"

Sample HTML page

hope that helps someone

[IsmaelChivite]

Very nice example @DougBrowning  Great to see how you take data from the form and use it to populate the comments EXIF! The HTML linking is very cool too!

[Travis_Bugg]

Thanks for the detailed and informative post @IsmaelChivite and for the additional developments @DougBrowning ! This is going to assist in our post-field data processing quite a bit. 

Would it be possible to filter the records that are exported from the survey using an attribute or range of attributes from within the survey? It is very simple to apply a filter from within the Survey123 Data page. Can the same functionality be applied to this python code? Where would the query be inserted? 

Thanks!

[DougBrowning]

You can add a where clause on this line  MakeTableView_management.  

Also you could select the records then run this as a Toolbox and it will respect the selections.

[Alena]

Thanks! The directions and script worked perfectly! Does it only work for the creator of the survey or can a user that has access be able to download? 

[DougBrowning]

They would need content creator rights to use the Export button if that is what you mean.  It is a pain for us for sure.

[Alena]

Yes, that is a pain. I'd rather all the users that have access to the survey be able to download what they wanted.

[PaulCone2]

It used to be on AGOL you could query the REST endpoint for a specific inspection and get the photo attachments back in a list, then walk through the list and download them.  The URL looked like this...

https://services.arcgis.com/[AGOLinstancestring]/ArcGIS/rest/services/[servicename]/FeatureServer/inspectionlayerID/inspectionID/attachments

So in practice that looked like

https://services.arcgis.com/quVN97tn06YNGj9s/ArcGIS/rest/services/ATC_building_inspections/FeatureSe...

I've moved my survey to Enterprise and now when I query I get no attachments.  I know that Survey123 knows where the photos are.  What is the way to query them now with Python?

[PaulCone2]

@JamesTedrick any ideas on this?

[BrandonA_CDPH]

@IsmaelChivite , thank you so much for this post.  I am looking forward to using this notebook to streamline some download workflows.

I'm not sure if you or @ZacharySutherby are better to help with this. Is there a way to filter the survey features before downloading. I have a survey that is constantly updated, but the user only needs a subset of the data at once. The query would be something like [Field] = XYZ* (using * as wildcard).

Thanks in advance for any assistance. 

[ZacharySutherby]

Hello @BrandonA_CDPH, 

On the second line of the fourth code block is where we are exporting the data as an Excel spreadsheet. The code is as follows: 

item_excel = rel_fs.export(title=survey_by_id.title, export_format='Excel')

The export method in the Python API supports a "parameters" argument which we aren't using in the sample provided. I believe in this "parameters" argument you can set up a where clause to only export records where [Field] = XYZ. 

Please see this documentation for more information on how to configure the parameters, but setting the "parameters" argument to something like: 

parameters= "{"layers" :[ {"id" : 0, "where" : "fieldname = XYZ*"}]}"

Should allow you to export a subset of records.

You may need to convert it to a JSON object, please see this link for more information on converting the syntax to a JSON object

[BrandonA_CDPH]

@ZacharySutherby  - thank you for the help and direction. I'm pretty new at coding, so I will have to see if I can handle coding the parameter using JSON. Learning something new is always good.

[ZacharySutherby]

Hello @PaulCone2, 

In ArcGIS Enterprise you can access attachments via the URL the same as ArcGIS Online just using your ArcGIS Server web context URL. 

[BenjamenWetherill]

This is a good tutorial, but the thing I am surprised about is that the basic export functionality does not provide any field to tell you if an attachment exists.  I don't need to automatically download attachments, but I need to know which records to look at for attachments.

[IsmaelChivite]

@BenjamenWetherill  You can automatically calculate the number of images submitted to an image question as shown below. If you do this, you will be able to tell which records have attachments.

[GrantZ]

Great script! Is it possible to edit this to include a date range filter, so that you are only downloading a subset of the data? I have some partners who are interested in only downloading the survey records that are new since the last time the data was downloaded.

[DougBrowning]

You can add a where clause here

arcpy.MakeTableView_management(inDB + "\\" + attachName, "attachView")

or here 

with arcpy.da.SearchCursor("attachView", fieldList) as cursor:

and I think that would work.

[GrantZ]

@DougBrowningthank you for the info! Do you have any suggestions on implementing this? I'm not super sharp on Python, unfortunately.

[DougBrowning]

Just add the where clause here

with arcpy.da.SearchCursor("attachView", fieldList) as cursor:

becomes this I think.  How to query by date is always a syntax pain.  You can test it in Pro or Map.

whereClause = "YourDateField > '12/05/2022'"

with arcpy.da.SearchCursor("attachView", fieldList, whereClause) as cursor:

See here https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-... 

[LMedeirosUI]

@DougBrowning @ZacharySutherby 

Full disclosure: I'm pretty new to Survey123 and ArcGIS. And not using it with any map info.

I have a survey that I used to collect repeated measurements from the same individuals over time (sampling events). Each sampling event is submitted as a record, and each individual is implanted with a PIT tag so we can track individual changes. I also take pictures using the image question - both using the tablet AND uploading additional images from an ultrasound at a later time. These images are named using the question name, PIT tag for that particular repeat, and a date/time stamp.

I was assuming the images would export just as the rest of the data did. I was wrong. So now I'm trying to find a way to export the data along with the images for each individual so I can look at changes in the morphology over time and associate it with changes in other parameters.

I was directed to this blog post, but when I suggested it to the GIS specialist I work with, they did not think it would work because I have no map data. But it seems like images are being exported just fine. (Half the battle!) But will the images stay associated with their object ID (i.e., the "row" in the larger data table)? I was wondering if someone could post a picture of the Excel files that are exported? My ideal situation would be adding additional columns that contain the pictures to the data that is exported from Survey123. That way I can sort based on sampling event or PIT tag (or whatever) and still have the pictures associated with that repeat. It sounds like I will have a spreadsheet with the data and then another with a list of which images are associated with which objectID, but are they linked? Is there a way to link them and pull them in?

That is, I'm not just interested in downloading the pictures, even if they are named. I need a way to link them back up to a particular record. I'm pretty early on in the data collection process, so if I should instead use objectID or another parameter to name the picture, I can.

To start this is sort of an undertaking and I want to make sure it's worth it as I'm a bit over my head here. Thank you in advance for your help!

[BrandonA_CDPH]

I have used the method in this blog to rename photos and it works great! it takes a little work in Connect, but I think it will do what you're describing.  - Survey123 Tricks of the Trade: Set the file name of photos and signatures (esri.com)

My particular form has a sample Identifier for each entry. For each photo, I have the name set to "Sample ID - #" so that each photo has a unique name (S45-1, S45-2, etc). I didn't have the unique name for each photo at first and it did not work right (the blog points this out, but I missed it at first), so be careful of that.

The general workflow is as follows - this has to be done when designing the survey in Connect:

1) Enter (or calculate) the desired filename.

2) set the filename to the calculated field using the bind::esri:parameters column and the fileName=X parameter

3) when the survey is filled in, the name of the photo is changed.

Hope this helps.

[LMedeirosUI]

@BrandonA_CDPH I think I may have described my issue wrong. I have already named all of my photos using the method in that post. My issue is that images do not export from the survey123 portal unless running individual reports, which I cannot manage to arrange in a way that groups my data in a useful (to me) manner and summary reports do not export images.

I think I need a combo of exporting the files using the API and something similar to what Doug has done so that I generate a table where the pictures are just in columns as if they were text/integer data. Then I can sort the table by the unique id for the individual fish (or whatever parameter).

[DougBrowning]

Can you leave them in the service?  We download to permeant storage but if you do not need that just leave them there and use Ops Dashboard for photo review.  That is what we do during the season and it is super slick.  See the selectors at the top.  All intergrade with the service so its all live time.  No downloading anything.  We take and review 60,000 photos a year with this.

Sounds like you are focusing on the old school way using Excel but there are much more modern tools and workflows to use now.  If you update the tech you also need to update the workflow.

Hope that helps

[LMedeirosUI]

@DougBrowning I don't think that's an option because the survey has no map data. We aren't using things as intended. But maybe I'm wrong (hopefully) because otherwise this would work.

Follow up, though. When going over options, I see people mention the attachments table. Where is that? I don't see it listed in any feature service on AGOL, but I know the images are there because if I go to an individual record in survey123.arcgis.com, the "form view" sidebar shows the images for that individual record. Is that table something I should be able to see, but maybe my permissions aren't set up properly? I'm the owner of the survey and am a publisher. Or could be a hidden table...

[LiamHarrington-Missin]

Hi @LMedeirosUI,

I'm not sure I can help you with your export challenge, but I'm sure someone out there can help.  What I did want to check in on is your comment "the survey has no map data".  If you created it in S123, it will have created it as a point feature service by default, even if you don't use the location and all your records are located at 0 Lat, 0 Lon.  

That being said, you don't need the data to be georeferenced to leverage the power of dashboards.  You can just point the dashboard at any feature service, whether it is a non-spatial table or a point layer with the spatial all at 0 lat, 0 lon.  You don't have to display a map to create a dashboard.

I don't know whether dashboards will give you a viable solution which will avoid the need to export and use Excel but it may make your workflow even slicker.  Additionally, although the learning curve is much larger, Experience Builder is like Dashboards on Steroids. 

[LMedeirosUI]

@LiamHarrington-Missin Experience Builder is definitely the closest I've come to getting what I need! And was the easiest to navigate (for me). I will still play around with export options (both within Experience Builder and ArcGIS Pro) just because it would offer a way to organize data and remove it from the online world, but for now at least I can visualize what I need to. Thank you!

[JamiDennis]

@DougBrowning 

I am getting the following error when I try to run this script 

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
In  [13]:
Line 2:     item_excel = rel_fs.export(title=survey_by_id.title, export_format='Excel')

File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\__init__.py, in export:
Line 12855: data_path = "content/users/%s/export" % self._gis.users.me.username

AttributeError: 'NoneType' object has no attribute 'username'
--------------------------------------------------------------------------

Is it because I am not the form creator? I do have creator rights but I did not create the Survey123 form that I want to download attachments for.  

[DougBrowning]

Are you hitting a service?  If so you need to be logged into Pro.  Then it will grab your credentials there at the top of the script.

[JamiDennis]

thanks for the reply, @DougBrowning  yes. I am logged into the portal with the survey through Pro, just as outlined in the article above by @IsmaelChivite 

[snowflake]

@DougBrowning; @IsmaelChivite  I implemented the topmost code from the blog to download attachments. It works wonderful.
Is there any way to add a filter to download attachments associated with specific locations, people submitting the form or date range (or all)?
I see an example code for the improved code that's in the comments, but is there a way to do it with the code that's in the blog?

[MUNICIPIODEPALMIRAVALLE]

BUEN DIA

estoy recibiendo el siguiente error cuando corro el script 

soy nuevo agradezco su colaboracion 

[ObservatorioComunicación]

Excelente aporte. A mi me ha funcionado perfectamente. Tengo una duda: ¿Cómo identifico que el script ya terminó de ejecutarse?
Gracias.

[JenChaffeur]

Hi! Thank you for the helpful tutorial. I am working with a survey that we generated that has almost 1,800 attachments from over 90 respondents. Is there a way to download the attachments and have them save in a folder that is named by a specific feature in the survey, such as a feature called 'NAME'? We have 15+ repeating questions that utilize attachments too and wondering if we can create a file structure that makes sense with the massive amount of data we have. Thank you in advance! @IsmaelChivite  @DougBrowning

[DougBrowning]

I script for this has been posted a few times but I cant find it.  The basic is here.  Really just join the attach table to the form and pull any fields you want.  Then create dir, add photos to dir and rename as you do.

Export to a GDB from Item details page.

import os, arcpy

# vars--------------------
inDB = r"exportGDB.gdb"
outDir = r"C:\tmp\photos"

# For LocationVerification
locForm = "LocationVerification"
# actual attachements table
locattachName = "LocationVerification__ATTACH"

# should be standard
fldBLOB = 'DATA'
fldAttName = 'ATT_NAME'

# vars--------------------

print "Starting"
totalPhotos = 0



print "Running Loc ver"
# Location Verification also
arcpy.MakeTableView_management(inDB + "\\" + locattachName, "attachView")
arcpy.AddJoin_management("attachView", "REL_GLOBALID", inDB + "\\" + locForm, "globalid")
# funky due to the join
fieldList = [locattachName + "." + fldBLOB, locattachName + "." + fldAttName, locForm + ".EvaluationID"]

with arcpy.da.SearchCursor("attachView", fieldList) as cursor:
   for row in cursor:
      binaryRep = row[0]

      newComment = ''
      if row[2] == None or row[2] == '':
        print "Warning Null or blank in EvaluationID  Skipping photo.  Must fix!!"
        print "   ATT_NAME is " + row[1]
      else:
        baseOut = outDir + "\\" + row[2][:2] + "\\" + row[2]
        if not os.path.exists(baseOut):
            os.makedirs(baseOut)

        # Need to figure out which Photo it is from the name
        whichPhoto = row[1].split("-")[0]
        fileName = row[2] + "_" + whichPhoto + ".jpg"

        # save out the file
        open(baseOut + os.sep + fileName, 'wb').write(binaryRep.tobytes())
        totalPhotos += 1

print "Total Photos " + str(totalPhotos)
del cursor
print "All Done"

[MelanieWawryk]

We ended up using Kobo Toolbox https://www.kobotoolbox.org to create a survey for work permitting that required several documents to be uploaded. We then use FME to convert the survey into our enterprise geodatabase with fields with hyperlinks to each of the different types of documents downloaded with FME to our document server. There wasn't enough control over multiple uploaded documents within Survey123 mainly when the uploaded files are not all the same type of document. 

[MRomijn]

Hi, I have created a survey to capture records to onboard individuals to a project. The project is across 20 different locations and the project site location is one of the fields in the database. There are almost 4000 entries. Each entry has 3 to 4 images/files linked to it, which include a photo of the person, photo of their identification and bank letter. I have coded the images to save with each participants identification number, their surname and the "name" of the image/file. About 5% of the attachments did not name correctly.

I have exported all the entries using this link: Survey123 Tricks of the Trade: Download survey data (with attachments)  https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-download-survey-da... 

However it places all the attachments into one folder. With over 4000 entries it becomes difficult to sort the data and place it into the relevant project site's folder. Does anyone know of a way that you can export the attachments either simultaneously into different folders (per project site), or to export the attachments in batches per project site.  

[ekangeles]

Thank you for this information. I was able to download all the attachment. I am wondering if there is a code that arrange all the attachement into separate folders. 

[DougBrowning]

Yes my code above creates one folder for each ID field you give it.  

[PaulCone2]

I'm attempting to run this script, but it just stalls and I'm trying to figure out why.  I added some print statements to see how far it is getting.  It appears to be stuck on

item_excel = rel_fs.export(title=survey_by_id.title, export_format='Excel')

I am running Pro 3.1.3, and can run other notebooks in other projects.

Any ideas?

Paul

[DougBrowning]

Are you logged into Pro and have this in your script to grab the credentials?

from arcgis.gis import GIS
gis = GIS('pro')

I download to GDB but the rest of my script is this

fsLink = gis.content.get(itemId)
result = fsLink.export("export" + HFSname + dt, "File Geodatabase")

# Save to file system

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

hope that helps

[PaulCone2]

Yes, I have the rest of your script as well.  End users aren't GIS savvy, so they want the Excel.

[PaulCone2]

@DougBrowning I guess I was being impatient!  Thank you!

[JMcNulty]

Attempting to work through this now - could someone post a complete example of the original solution combined with Doug Browning's sections? Having trouble piecing the two together. 

[PaulCone2]

@JMcNultynot sure if this will help, but here is my current code.  I added some print statements to figure out where it was in the script.  It turns out that since my Survey123 feature service includes all the building footprints so I can create a relationship of building to inspection, the script was downloading ALL of them, and taking hours and hours, so I though it was just stuck.  Once I figured that out, I just added another IF THEN to avoid that layer.  This is running in a Notebook in Pro.

print("Downloading survey attachments to folder")
layers = rel_fs.layers + rel_fs.tables
print("for i in layers")
for i in layers:
    print("i.properties.name = " + i.properties.name)
    if i.properties.hasAttachments == True and i.properties.name != 'building_footprints':
        print("i.properties.hasAttachments == True")
        print("Setting feature_layer_folder = os.path.join(save_path, '{}_attachments'.format(re.sub(r'[^A-Za-z0-9]+', '', i.properties.name)))")
        feature_layer_folder = os.path.join(save_path, '{}_attachments'.format(re.sub(r'[^A-Za-z0-9]+', '', i.properties.name)))
        print("Making feature_layer_folder")
        os.mkdir(feature_layer_folder)
        if bool(store_csv_w_attachments):
            path = os.path.join(feature_layer_folder, "{}_attachments.csv".format(i.properties.name))
        elif not bool(store_csv_w_attachments):
            path = os.path.join(save_path, "{}_attachments.csv".format(i.properties.name))
        print("Setting csv_fields = ['Parent objectId', 'Attachment path']")
        csv_fields = ['Parent objectId', 'Attachment path']
        print("with open(path, 'w', newline='') as csvfile:")
        with open(path, 'w', newline='') as csvfile:
            csvwriter = csv.writer(csvfile)
            csvwriter.writerow(csv_fields)
            feature_object_ids = i.query(where="1=1", return_ids_only=True, order_by_fields='objectid ASC')
            print("for j in range(len(feature_object_ids['objectIds'])):  ", len(feature_object_ids['objectIds']))
            for j in range(len(feature_object_ids['objectIds'])):
                current_oid = feature_object_ids['objectIds'][j]
                current_oid_attachments = i.attachments.get_list(oid=current_oid)
                if len(current_oid_attachments) > 0:
                    print("len(current_oid_attachments) > 0:")
                    print("for k in range(len(current_oid_attachments)):")
                    for k in range(len(current_oid_attachments)):
                        attachment_id = current_oid_attachments[k]['id']
                        current_attachment_path = i.attachments.download(oid=current_oid, attachment_id=attachment_id, save_path=feature_layer_folder)
                        print("csvwriter.writerow")
                        csvwriter.writerow([current_oid, os.path.join('{}_attachments'.format(re.sub(r'[^A-Za-z0-9]+', '', i.properties.name)), os.path.split(current_attachment_path[0])[1])])

[JMcNulty]

@PaulCone2 

Thanks for the response. I have no issue with the first solution was more curious if there was a way to tie Doug Browning’s portion for creating a directory/HTML page to the original code, or if I should just download a file geodatabase directly from the survey results to do so. Were you able to output that part in an excel format?

[PaulCone2]

@JMcNulty our end users don't speak GIS so I am just downloading Excel and photos.  But next on my list is to duplicate the feature service and then create an archived map as well.

[DougBrowning]

@PaulCone2   Take a look at ArcGIS for Excel.  You can open a service right in Excel directly.  Live time no download.

Then you gave me the idea that the photo can be in a popup in the map on the side.  I think it would work but I cannot test since all my photo forms are in a repeat table.

Maybe someone can test this out.

Answered my own question and its a yes!  Hopefully I can figure out a repeat too.  Really no reason to download anymore.

[PaulCone2]

@DougBrowning that is a great idea.  As soon as I can get my org to reenable ArcGIS for Excel, I'll try that!  (It got disabled in a recent Office 365 security lockdown.)

[DougBrowning]

I had luck getting it added as a Managed Addin.

[PaulCone2]

@DougBrowning ArcGIS for Excel is back again thanks to our Office 365 team.  Yes, works great for downloading into sheets, but then where/how to get to photos, to put in pop-up?  My structure is

building footprints

which have inspections attached

which have photos attached