Export attribute table to excel with photo attachments

[NicholasMartin1]

I am using Collector on a project that relies heavily on photo attachments. Viewing the photographs of collected data points in ArcGIS Online is working well, but I also want to share these photo attachments in other formats.

Specifically, I want to export an attribute table to a spreadsheet with photo attachments viewable on the spreadsheet (or create a report from ArcMap with these photo attachments). I also would like to be able to share feature classes in ArcMap that include photograph attachments. However, I haven’t figured out how to achieve either.

When I export ArcGIS Online feature classes to ArcGIS for desktop, the attachments are viewable in the attachment manager for each point. If I try to export the feature class to share with another party, however, the attachments are lost. Similarly, when I export the attribute table to a spreadsheet the photos aren’t viewable.

So I have two questions:

1. 1) Is it possible to easily share feature classes in ArcGIS that include photo attachments?
2. 2) Is it possible to include photo attachments when I export an attribute table to a spreadsheet?

Any help or guidance is appreciated. Thank you!

[JakeSkinner]

Hi Nicholas,

1)  Instead of exporting the feature class, you will want to copy/paste the feature class to another geodatabase.  Attachments are stored in a table and are related through a relationship class to the associated feature class.  Performing an export will not export the relationship class and related table, but a copy/paste will.

2)  Since the attachments are stored in the related table, you would need to export the attachment table.  However, the attachments are stored as BLOBs, so they won't be viewable in the excel spreadsheet.  One thing you could do is:

     1.  Export the attachments to a directory using a python script:

import os, arcpy
tbl = r"C:\Temp\Python\Test.gdb\Point__ATTACH"
fldBLOB = 'DATA'
fldAttName = 'ATT_NAME'
outFolder = r"C:\Temp\Python\Attachments"

with arcpy.da.SearchCursor(tbl,[fldBLOB,fldAttName]) as cursor:
   for row in cursor:
      binaryRep = row[0]
      fileName = row[1]
      # save to disk
      open(outFolder + os.sep + fileName, 'wb').write(binaryRep.tobytes())

print 'Finished'

     2.  Join the attachment table to the feature class (using the feature class OBJECTID and the attachment table REL_OBJECTID)

     3.  Export the feature class table to another format

     4.  Update the ATT_NAME field to include the path to where the images were exported

You could then have a hyperlink in the spreadsheet to the associated image.

[HunterSimpkins]

I have a very similar question to Nicholas', but I need to be able to attach two photos to each record/point in the spreadsheet.  The data consist of highway signs with a picture of the front and back of each sign, collected in Collector for ArcGIS. I successfully exported the batch attachments from ArcGIS Online and updated the file path in the Attachment table.  I now have two photos that reference one Rel_GlobalID from the point feature class:

I'd like to create a second column that would hold the path of the second image and export this table to excel so the rest of the project team can locate the appropriate photos for each sign (they don't use ArcMap).  I'd rather not have duplicate points/records in the dataset, so I have not explored the one-to-many join options.  I'm a python newbie, so any clues towards scripting this would be much appreciated!  Thanks in advance.

[JakeSkinner]

Hi Hunter,

Here is an example on how you can do this using python.  The below code updates two fields (Image1, Image2) in a feature class called Point.

Note:  I needed to delete the relationship class between the feature class and the attachments table in order to have the code execute successfully.

import arcpy, os

from arcpy import env

env.workspace = r"C:\temp\python\test.gdb"

path = r"C:\temp\python\Attachments"

fc = "Point"

table = "Point__ATTACH"

dict = {}

with arcpy.da.SearchCursor(fc, ["GlobalID"]) as cursor:

   for row in cursor:     

      OID = row[0]

      dict.setdefault(OID, [])     

      with arcpy.da.SearchCursor(table, ["ATT_NAME"], "REL_GLOBALID = '" + str(OID) + "'") as cursor2:        

         for row2 in cursor2:

            dict[row[0]].append(row2[0])

del cursor, cursor2

for key in dict:

   with arcpy.da.UpdateCursor(fc, ["Image1", "Image2"], "GlobalID = '" + str(key) + "'") as cursor:

      for row in cursor:

         row[0] = path + os.sep + dict[key][0]

         row[1] = path + os.sep + dict[key][1]

         cursor.updateRow(row)

del cursor

print "Finished"

[StephanLe_Roux]

Jake,

I am struggling to get this working. This is exactly what I need to do. I want to extract my table to a spreadsheet with a hyperlink pointing to multiple photos for a single record.

I have tried running your script. I am not a programmer but only a user.

First I tried running it in IDLE but it came back with :

Traceback (most recent call last):

  File "C:\temp\extract_photos_Test\2Photos.py", line 45, in <module>

    for row in cursor:

RuntimeError: Objects in this class cannot be updated outside an edit session [WaterSamplingPoints]

The I tried running it in ArcMap 10.2.2 and got the following error :

Traceback (most recent call last):

  File "C:\temp\extract_photos_Test\2Photos.py", line 49, in <module>

    row[1] = path + os.sep + dict[key][1]

IndexError: list index out of range

Failed to execute (Script).

Can you please give some advice ?

Regards

[JakeSkinner]

HI Stephan,

Can you zip and upload a small sample of your data for me to take a look at?

[StephanLe_Roux]

Jake,

Sorry for not replying to your email. I am just struggling with my dataset. It’s too big and I am trying to cut it into smaller chunks.

My feature service have almost 14000 points in it, Each point will have 3 photos attached. So I realized to make this work more efficient I will have to cut my data chunks to about 1000 points and have multiple feature services serving data to a single webmap.  Other problem is we have done a pilot project on the data and we have done about 900 so far. How can I transfer the attachments when I start chunking the data ?

I am in the process of doing just that. I am struggling to get you a sample data set because I have no control over  what records I can pull from AGOL.

I found the “Update Definition” option and changed the max record to 2000 but still I can only see 1000 record when I open the service in ArcMap.

Kind Regards

[JakeSkinner]

I would not recommend chunking the data.  Instead, consider using scale dependencies.  Take a look at the section Optimizing layers, rasters, and layouts from the following link:

Optimizing map content for performance—Help | ArcGIS for Desktop

[StephanLe_Roux]

Jake,

I am a bit confused. I have a AGOL feature service with 13700 points in one layer. 3 photos for each point will be added to the feature service. I was told by ESRI support to chunck my data to more manageable sizes as this feature service might grow to big to handle. They suggested between 1000 - 2000 record/data chunck should be resonable to handle.

I am currenly struggeling to get more than a 1000 records downloaded into a local copy.

My plan.

Contractors will visit these points and do some attribute updates and changes. They will also take 3 photo/Site and upload that to the service. I want to be able to run a scrpit to download records from a feature service with the attachments. Extract the attachments to a folder and create a dbf/spreadsheet with a hyperlink to open the photo. This is what the above mentioned script should be able to do if I am not mistaken ?

Because of the volume of data I was thinking to cut my one big feature service into about 13 smaller feature services (one layer each) or should I have one feature services with 13 layers (Layer chuncked) in it. Use one map to give collector access to edit the data.

I would then be able to run the script against any one of the 13 feature services and extract the attachments and spreadsheet for futher use.

Is there a beter way of doing this or am I on the right track ?

Multiple layers -> One feature service -> one map -> Collector app

Multiple Layers -> Multiple feature services -> one map -> Collector app

Single layer -> single feature service -> single map -> Collector app (for each chunck of data 13 in total)

Any suggestions welcome ?

[ZacAndereck1]

You all really should check out this. Have not had a problem with the exporting of pictures with features using this approach.

Watch the video and be sure to check out the comment they referenced in the video. (posted comment below for simplicty)

Getting Attachments down from ArcGIS Online — Cloudpoint Geographics Inc.

Great article! It helped a lot!

Fantastic article. To follow up on gisucato's question, here's how I was able to export the attachments with a name that will help me tie it back to the feature class (keep in mind that all my attachments are jpeg's). 1. Add a new unique identifier field to your feature class (for the sake of this example I'll refer to the UniqueID field as GIS_ID). Populate that field with a unique ID (use the field calculator to populate it with the ObjectID, incremental id, etc.) 2. Add the same field (GIS_ID) to your attachment table, but don't populate it yet with any info. 3. Perform a table join on the attachment table back to the feature class using the 'REL_GLOBALID' field from the table and the 'GlobalID' field from the feature class. 4. Use the field calculator to populate the GIS_ID field in the attachment table with the values in the GIS_ID field from the feature class. Remove the join. 5. Add another field to the attachment table called 'FileName'. 6. Use the field calculator to calculate the 'FileName' field in the attachment table with the full file name, including the extension (ie, [GIS_ID] + ".jpg"). There are plenty of ways to populate this info if you have different attachment file types but I won't get into that here. 7. Within the ExportAttachments.py file change the search cursor value of 'ATT_NAME' (found on line 7) to match the name of the field in the table which contains the newly created file name (in this case, 'FileName'). Save the .py file. 8. Run the tool. Voila! Your attachments will have a file name that matches the unique ID in your feature class. 9. If you wanted you could then add another field in your feature class and populate it with the full attachment name (via a table join and field calc) to use as a hyperlink field.