It’s a funny coincidence, that I saw your post as I was creating an Applescript to handle this last week. I figured if I got anywhere I’d post, if nobody beat me to it. Well, it looks like that’s the case, so here goes.

I lived in Japan back in the 00’s and took 38,000+ photos. At the time, I didn’t think about any sort of image storage beyond the folder they were stored, and an Excel file in the same place that described everything. So I wrote what feels like tons of data into an Excel spreadsheet that grew over the years. In a nutshell it had a record for each photo, with a description, a bunch of keywords, locations, date, and other metadata. I’ve since put all my photos in Aperture, and want that information tied closer to the images.

Since there wasn’t anything built-in to Aperture, I wrote this Applescript. I won’t say it’s perfect, but it’s a start and has been working processing my photos since Friday. It’s slow, but it’d take a lot less time than doing it manually. Plus I get to keep my sanity!

(*
Import Metadata from Excel into Aperture

Instructions:
1. Open both Aperture and your Excel spreadsheet.
2. Select a group of photos in Aperture.
3. Run this script.
a. collects filedates and filename from Aperture
b. finds the first row storing that information in Excel
c. pulls any information in that Excel row
d. adds that data to the image in Aperture
e. returns to Aperture and marks it as Imported and a datestamp
f. Continue…

Note: adjust cell numbers and rowCount as needed
*)

on run
set importCount to 0
set rowCount to 10000
tell application “Aperture”
set selectedImages to (get selection)
if selectedImages is {} then
error “Please select an image.”
else
repeat with i from 1 to count of selectedImages
tell library 1
tell item i of selectedImages
–Collect the filedate
set filedate to value of EXIF tag “ImageDate”
set fileYear to year of filedate
set fileMonth to month of filedate as integer as string
if length of fileMonth is 1 then
set fileMonth to “0” & fileMonth
end if
set fileDay to day of filedate as integer as string
if length of fileDay is 1 then
set fileDay to “0” & fileDay
end if
set filedate to fileYear & “-” & fileMonth & “-” & fileDay as string

–Collect the next day’s filedate, since the spreadsheet’s date and ImageDate may be offset by 1 day
set filedate1 to (value of EXIF tag “ImageDate”) + 1 * days
set fileYear to year of filedate1
set fileMonth to month of filedate1 as integer as string
if length of fileMonth is 1 then
set fileMonth to “0” & fileMonth
end if
set fileDay to day of filedate1 as integer as string
if length of fileDay is 1 then
set fileDay to “0” & fileDay
end if
set filedate1 to fileYear & “-” & fileMonth & “-” & fileDay as string

–display dialog filedate
–display dialog filedate1

–Collect the filename
set filename to (get value of other tag “FileName”) as string
–display dialog filename

–Reset the keywords
set rollId to “”
set description1 to “”
set keyword01 to “”
set keyword02 to “”
set keyword03 to “”
set OutOfOrder to “”
set panorama to “”
set others to “”
set rowId to “”
set alreadyAdded to “No”

–Go into Numbers to pull the data
tell application “Microsoft Excel”
tell sheet 1 of workbook 1
repeat with i from 2 to rowCount
–display dialog filedate & “:” & value of cell 2 of row i

if filedate = value of cell 2 of row i or filedate1 = value of cell 2 of row i then
set imageFile to value of cell 8 of row i
–display dialog imageFile

if filename is imageFile then
if value of cell 51 of row i is “Y” then
set alreadyAdded to “Yes”
else
set rollId to value of cell 4 of row i
set description1 to value of cell 18 of row i
set keyword01 to value of cell 19 of row i
set keyword02 to value of cell 20 of row i
set keyword03 to value of cell 21 of row i
set OutOfOrder to value of cell 10 of row i
set panorama to value of cell 11 of row i
set others to value of cell 14 of row i
set rowId to i
end if
exit repeat
end if
end if
end repeat
end tell
end tell

–Found the image in the spreadsheet
if alreadyAdded is “No” and description1 is not “” then
–display dialog filename & ” ” & filedate & ” ” & description1 & ” ” & rollId
make new IPTC tag with properties {name:”Caption/Abstract”, value:description1} –ex. value:”bike ride home from school”
make new keyword with properties {name:rollId, parents:{“Roll ID”}}

if keyword01 is not 0 then
make new keyword with properties {name:keyword01, parents:{“Japan Spreadsheet”}} –ex. name:”sakura”
end if
if keyword02 is not 0 then
make new keyword with properties {name:keyword02, parents:{“Japan Spreadsheet”}}
end if
if keyword03 is not 0 then
make new keyword with properties {name:keyword03, parents:{“Japan Spreadsheet”}}
end if
if OutOfOrder is “*” then
make new keyword with properties {name:”Out of Order”, parents:{“Japan Spreadsheet”}}
end if
if panorama is “*” then
make new keyword with properties {name:”Panorama”, parents:{“Japan Spreadsheet”}}
end if
if others is “*” then
make new keyword with properties {name:”by Other”, parents:{“Japan Spreadsheet”}}
end if
make new keyword with properties {name:”Japan Spreadsheet”}

–Go back to Excel to set the Imported flag to “Y”
tell application “Microsoft Excel”
tell sheet 1 of workbook 1
if rowId is not “” then
set value of cell 51 of row rowId to “Y”
set value of cell 52 of row rowId to ((current date) as string)
end if
end tell
end tell
set importCount to importCount + 1
end if
end tell
end tell
end repeat
end if
end tell
display dialog “Importing Metadata from Excel is complete. Metadata was added to ” & importCount & ” images and videos.”
end run

*Cells*
Cell 2 (Column B): Date in yyyy-mm-dd format ex. 2004-07-29
Cell 4 (Column D): Roll/Camera ID; I assigned a separate number to each day depending on which camera used. Different cameras had different numbers, my 35mm started with “8001” and so on, digital “9001”, my cell “K001”, etc.
Cell 8 (Column H): Full Filename ex. IMG_0444.JPG or MVI_0591.AVI
Cell 10 (Column J), Cell 11 (Column K), Cell 14 (Column N): Flags set based on certain photo criteria
Cell 18 (Column R): Description, used for the Caption in Aperture
Cell 19 (Column S), Cell 20 (Column T), Cell 21 (Column U): Keywords for the photos
Cell 51 (Column AY): Metadata Imported indicator. default is “N”, script sets this to “Y” when successful
Cell 52 (Column AZ): Date/time metadata assigned to the photo

The other cells have id fields, more keywords and other things. I just use the file date and file name as the key between the spreadsheet and selected image. If you have a different order of columns, you could just switch out the numbers. Maybe someday I’ll dynamically find the column number based on the description before doing any of the processing in the script. Also, count the rows in the spreadsheet instead of using a fixed number.

This is really good. Perhaps some extra documentation would be useful. What precise columns are there in the Excel spreadsheet, and in what specific order? What essential field(s) in Excel and Aperture (which resemble the date and filename) are matched?

Tell us how well this worked out for you. It seems like a fantastic tool for transferring Excel metadata to Aperture. Good work!  drift boss