Extracting photo Metadata into a spreadsheet

iOS
#1

Can this be done in AppleScript? I am no programmer but tried anyway with Workflow, IFTTT and Automator with no luck. After listening to @Sal on Episode 3, I was impressed by AppleScript’s power.

I’ve taken almost 500 photos documenting a project’s work-in-progress over the past 6 weeks. I’d like to create a Numbers (or Excel) table by automatically extracting the metadata from each picture. This would allow me to document what work was done, when it was done and enable me to calculate time on project. Also, having one of the fields be a thumbnail would be a bonus though not required. The photos are in the Apples Photos app further filled in a project album.

Help would be appreciated or at least point me in the right direction so I can learn how to do this - the solution can be IOS or MacOS. I dread to think of the manual alternative.

#2

Hi, Shelton,

I don’t know how to automate getting these pictures out of Photos (apart from grabbing them all and dragging them out of Photos), but once they are out and in a folder structure, please check out ExifTool at https://www.sno.phy.queensu.ca/~phil/exiftool/ to grab the metadata from pictures. I’d then dump the metadata into a csv file, which can then be opened by Excel or Numbers.

Good luck and post the script once it’s running.
Veit

#3

Since you asked about a thumbnail: Same approach, but use ImageMagick at https://www.imagemagick.org/script/command-line-tools.php to create thumbs for each image.

#4

Thank you for your input @veit. I’ll look into these in a couple of days when I’m back in the office.

#5

Greetings. First things first. Photos on iOS has limitations on accessing or viewing the name of an image file, embedded caption data, or other metadata. It is really designed for consumers and not for automation.

On macOS however, Photos is scriptable via AppleScript/JavaScript and you could easily extract metadata to a Numbers spreadsheet. For example, here is a simple script that extracts filename and description from the selected images and adds that to individual rows in the current Numbers spreadsheet.

For more information, visit: https://iworkautomation.com

Cheers,

Sal

tell application "Photos"
	set theseItems to get selection
	set imageData to {}
	repeat with i from 1 to the count of theseItems
		set thisItem to item i of theseItems
		set aFileName to filename of thisItem
		set aDescription to (description of thisItem)
		if aDescription is missing value then
			set aDescription to ""
		end if
		set the end of the imageData to {aFileName, aDescription}
	end repeat
end tell

tell application "Numbers"
	tell document 1
		tell active sheet
			tell first table
				repeat with i from 1 to the count of imageData
					set thisData to item i of imageData
					repeat with q from 1 to the count of thisData
						set cellData to item q of thisData
						set the value of cell q of row i to cellData
					end repeat
				end repeat
			end tell
		end tell
	end tell
end tell
2 Likes
#6

Thank you @SAL! I’ve been away for awhile and just saw this. It looks exactly what I’ve was looking for and I can’t wait to give it a try.

#7

This is a program which is extracting the name and date of picture to Excell list using AppleScript.
7798 pictures were done for only 44 sec !!!

tell application "Photos"
	activate
	set num to 1
	set imageSel to (get selection)
	if (imageSel is {}) then
		error "Please select at least one image."
	else
		repeat with index from 1 to count of imageSel
			set next_image to item index of imageSel
			set a to (the filename of next_image)
			set b to ((the date of next_image) as date)
			tell application "Microsoft Excel"
				set value of cell num of column 1 of active sheet to a
				set value of range num of column 2 of active sheet to b
				set num to num + 1
			end tell
		end repeat
	end if
	(*  return....   *)
end tell

(*

I wanted to make this program to work, but to be as short, simple and understandable as possible. I skipped all unnecessary operations, like opening, saving files, selections…, what is easy to do (maybe even easier) manually before starting the program. Some notes:

  1. Open and select photos from application “Photos”

  2. Open a Workbook in Excell’

  3. Run this program.

  4. The oldiest date of some picture to be recognized is 01-01-1900.

  5. This program writes list and shows a result n+1 , but if you want to see some data of your last picture in your selection, put the command return between last two commands, like for instance :

    return "Adjusted the titles of " & (the length of imageSel) & " photos. The last date is " & ((the date of next_image) as date) & " ; name = " & name & " ; index = " & index & " ; meno sliki = " & (the filename of next_image) & " ; num = " & num
    *)

#8

I tried this to copy and run, but for some reason QUOTATION MARKS shows ERROR. So, they should be erased and put new one. (SOOOOOORY) (but not my fault)

#9

Thank you Rosemary for formatting my code and now everything is fine. Quotation marks are not problem anymore. I wanted to mention that terms cell and range are equivalent and they are put on purpose to show that.