Export Survey123 survey data with attachments using ArcGIS API for Python

Summary

In the ArcGIS Survey123 website, all survey records or a subset of the survey record data can be exported as a CSV, Microsoft Excel, KML, shapefile, or in a file geodatabase format. However, if the survey data contain image attachments, the survey data must be exported as a file geodatabase and the images extracted manually. Fortunately, this process can be automated using the ArcGIS API for Python Survey123 module. Follow the workflow described in this article to export the Survey123 survey data and attachments using a stand-alone Python file (.py). The Python file also runs in Jupyter Notebook.

Procedure

1. Create a file with the .py extension.
2. Specify the script below in the Python window.
   1. Import the necessary modules.

import arcgis
from arcgis.gis import GIS
import os, re, csv

2. Define the required variables.

portalURL = "<Portal for ArcGIS or ArcGIS Online URL>"
username = "<username>"
password = "<password>"
survey_item_id = "<ArcGIS Survey123 form item ID>"
save_path = r"<survey results and attachments save folder directory>"
keep_org_item = <define True if the exported item is to be added to Content for the ArcGIS organization, or False if otherwise>
store_csv_w_attachments = <define False if the .csv file that maps each attachment to its parent object ID should be stored in the root folder (with the exported Excel workbook), or True, in each individual layer folder>

3. Connect the Portal for ArcGIS or ArcGIS Online organization to the survey.

gis = GIS(portalURL, username, password)
survey_by_id = gis.content.get(survey_item_id)

4. Define the file format for the survey's feature service export. In this example, the survey data is set to export as a Microsoft Excel (.xlsx) file.

rel_fs = survey_by_id.related_items('Survey2Service','forward')[0]
item_excel = rel_fs.export(title=survey_by_id.title, export_format='Excel')
item_excel.download(save_path=save_path)
if not bool(keep_org_item):
    item_excel.delete(force=True)

5. Iterate through all the available layers. Export the data as Microsoft Excel files and include the attachments.

# Define the features to be exported. 
layers = rel_fs.layers + rel_fs.tables

for i in layers:
    # Identify whether the features has attachments.
    if i.properties.hasAttachments == True:
        # Set the export path folder when the features has attachments.
        feature_layer_folder = os.path.join(save_path, '{}_attachments'.format(re.sub(r'[^A-Za-z0-9]+', '', i.properties.name)))
        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))
        csv_fields = ['Parent objectId', 'Attachment path']
        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')
            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)

                # Create a new folder for the attachments to be exported and new CSV file using the layer name with the suffix '_attachments' appended to it. The new CSV file contains the parent object ID and the relative folder path for each attachment in the layer.
                if len(current_oid_attachments) > 0:
                    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)
                        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])])

6. Apply the print() function to show a message when the survey data and attachments are exported.

print("<message>")

The code block below demonstrates the full working script.

import arcgis
from arcgis.gis import GIS
import os, re, csv

portalURL = "https://www.arcgis.com"
username = "Username123"
password = "Password123"
survey_item_id = "e7ed3bfae65c4cbe8fee28ef38472bd1"
save_path = r"C:\Users\ISC-DT27-JASON\Documents\Article work\Article 28397"
keep_org_item = False
store_csv_w_attachments = False

gis = GIS(portalURL, username, password)
survey_by_id = gis.content.get(survey_item_id)

rel_fs = survey_by_id.related_items('Survey2Service','forward')[0]
item_excel = rel_fs.export(title=survey_by_id.title, export_format='Excel')
item_excel.download(save_path=save_path)
if not bool(keep_org_item):
    item_excel.delete(force=True)

layers = rel_fs.layers + rel_fs.tables
for i in layers:
    if i.properties.hasAttachments == True:
        feature_layer_folder = os.path.join(save_path, '{}_attachments'.format(re.sub(r'[^A-Za-z0-9]+', '', i.properties.name)))
        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))
        csv_fields = ['Parent objectId', 'Attachment path']
        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')
            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:
                    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)
                        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])])

print("Completed")