Would ExifTool solve my problem?

[Paul728]

Hi, I have a large collection of photos (over 10,000) of biological samples for my research in iPhoto 11 (I also have the original photo files as jpgs in folders) and a Filemaker database containing a listing of the photo titles and their geographic coordinates in degree/min/sec format. I would like to add the coordinates to the photos so that I can use iPhoto's Places feature to view the origins of each sample. Ideally, I'd love to add the other info contained in the database, such as depth (most are marine organisms) and taxonomy (Phylum, Genus, Species, etc.) - but perhaps iPhoto isn't the appropriate tool for that. There doesn't seem to be a way to add this to iPhoto other than manually one photo at a time, which will take far too long.

I've been on the Apple iPhoto discussions trying to find a solution. Folks there suggested ExifTool or HoudahGeo, I wrote to the folks at Houdah and they confirmed that their software won't do what I need. They suggested exporting the database as a CSV file and using ExifTool and some shell scripting to merge the data. I looked at your site to see if it would help and am not sure. It seems to have a long learning curve and before I invest the time, I'd appreciate advice on whether it is the right tool for me.

Would ExifTool allow me to add the database gps info to either iPhoto or to the original files prior to importation into iPhoto?

The other alternative would be to move the photos into the Filemaker db but then I'd lose the easy photo manipulation tools of iPhoto.

Any suggestions for how to proceed will be greatly appreciated.

[Phil Harvey]

Absolutely you can do this with ExifTool.  The only trick is the interchange of information between your database and ExifTool.  The exiftool application gives you some options that allow input of information as XML or command-line arguments, but worst case you can always write a simple Perl script to parse any type of information then use the ExifTool API to write the information to images.  If you search this forum you will find examples of Perl scripts that do similar things (here is one example,  and another).

- Phil

Edit:  I found a couple more good examples:

• update-write iptc or xmp based on csv file


• Batch GPS into Exif from text file

[Paul728]

Hi Phil,

Thanks for the feedback. I don't know Perl but I'll look over the examples you pointed out, see if it makes any sense to me, and try playing with a limited set of data to see if I can do it. I assume I should start by exporting a subset of the data as a csv file, make sure I have a folder with the corresponding photos labeled with the identical names and see what happens.

One question, do I need to worry about the file extension in the name (e.g. photo1.jpb)? The db just has it as "photo1".

[Phil Harvey]

If all the file extensions are the same then it will be trivial to add them in the script.

- Phil

[Alan Clifford]

I have a bash script that takes a tab delimited file

ahc_0266.jpg    Cheetah
ahc_0286.jpg    Gerenuk
ahc_0304.jpg    View from room at Samburu Game Lodge

and stuffs the titles into the jpegs.

The business seems to be done with two lines
RUBRIC=`awk -F "\t" 'ENVIRON["BASEIMAGE"] == $1 {print $2; exit} ' "${RUBRICFILE}"`
and
exiftool -overwrite_original_in_place -xmp:title="${RUBRIC}" -iptc:objectname="${RUBRIC}" "${FILESPEC}"

with all the rest of the script checking for existing titles, whether it's a jpeg and stuff.

I don't understand the awk line any more but send me a couple of photos (reduced size please) and a tab delimited file of the data to be inserted to alan@clifford.ac and I'll have a go at modifying.  But I don't promise anything.

Into what fields in the jpeg metadata were you think of putting the non-location data?

Alan

[Paul728]

Hi Alan,

I need to do some studying about how the jpg metadata fields and iPhoto database are structured since I've never dealt with this stuff before. I'm not a very experienced programmer. The last time I did any serious programming was in grad school over 25 years ago and a little bit of AppleScript in more recent years.

The photos are named with an eight digit alphanumeric code (e.g. 4A6D 5101). The db contains the collection date, depth, latitude, longitude, genus and species. Also some additional fields, but these are the essential ones.

When imported into iPhoto the file name ends up as the title. The date, photo resolution, file size, and file type are imported. No data on the camera, exposure, shutter speed, etc are imported so they must not be present in the photo files (not a problem as I don't really need that).

Ideally, I'd like to end up with photos that can be imported into iPhoto with the following result.
I'd like the title in iPhoto to read as follows:       4A6D 5101 Genus Species
the geo coordinates in the "location" field
and any other info (depth, ...) into the "description" field.

If you're willing to give it a go, I'll send you a set of 3-6 photos and the corresponding data as a csv.

Thanks for the input.

Paul

[Alan Clifford]

If you're willing to give it a go, I'll send you a set of 3-6 photos and the corresponding data as a csv.

Paul

Send me a selection.  I have iPhoto so I should be able to see if I can get the data into the right fields.

Alan

[Phil Harvey]

Just for fun I created a version of the exiftool app that with CSV support:  click here to download

I haven't tested it thoroughly, but it seemed to work on a few small tests.

Eventually I may include a feature like this in the official version, but I'm sure it would need a lot of tweaking first (including, but not limited to, more diagnostic warnings when something goes wrong).  It works like this:

       -csv[=CSVFILE]
            Output information in the format of a CSV file, or import informa-
            tion if CSVFILE is specified.  The first row of the CSVFILE must
            be a list of tag names for each column of the file.  The Source-
            File tag is used to specify the file for writing the associated
            tags.  If SourceFile doesn't exist, the Directory and FileName
            tags are used instead.

                # generate CSV file from all files in a directory
                exiftool -csv dir > out.csv

                # update metadata for all files in a directory from CSV file
                exiftool -csv=a.csv dir


This version requires that the "SourceFile" column of the CSV file contains the exact file name (and must match exactly the file name from the command line to work), so you would need to generate a new "SourceFile" column in your database with the extensions added for this to work.

The first line in the CSV file must contain the tag-name headings.

Let me know if this is at all useful, and/or how it could be improved.

- Phil

Edit: Changed incorrect "FileSource" names to "SourceFile"

[Paul728]

Thanks Phil,

I'm going to try to play with it over the weekend. Hoping I'm not too deeply in over my head.

I also took up Alan on his offer to play with the files.

Paul

[Phil Harvey]

Update: I have done some more testing and found some bugs in my code.  I have uploaded a new version of the modified exiftool with the bugs fixed: click here to download

- Phil

[Amihai]

Hi. sorry my bad english.

1. Where I can download windows .exe with CSV support?

2. Can anybode help to solve my task? :

"calculate time/date difference and store result to unused EXIF/IPTC field" (for generate custom captions in the future).

Examples:

A. Trip.
start_date = 2011.03.06
exif.user_comment = "(day " + days (datedate_time_original - start_date) + "of 10)"

Later, it let generate in the ACDsee caption "2011/03. Mountain trip" + {exif.user_comment} and place it on photo.

B. Child age
birth_date = 2010.05.04
datediff = datedate_time_original - birth_date
if (iptc.keywords like "*John*")
then exif.user_comment = "age " + years (datediff) + "Y " + int( days (datediff/30) ) + "M"

Later, after setting captions for collection of photo:
caption = caption + "(" + exif.user_comment + ")"

P.S. I'm not familiar with perl and don'h have perl installed
dir -> export csv -> import excel -> process tags-> save .csv -> import csv = long way

[Phil Harvey]

1. Where I can download windows .exe with CSV support?

It won't be available as a Windows .exe until it is released officially.

"calculate time/date difference and store result to unused EXIF/IPTC field" (for generate custom captions in the future).

You could do this with a user-defined Composite tag, but it would require quite a bit of Perl scripting.

- Phil

[Phil Harvey]

FYI:  I worked on the CSV feature most of the weekend.  It isn't 100% finished yet, but it is now at the point where I can say with reasonable certainty that this feature will be included in the next ExifTool release (version 8.51).

- Phil

Amihai: I have split your post into a separate topic: Calculate time difference

[Paul728]

Phil, a big thank you for providing this tool.

Also, a big shout of appreciation for Alan Clifford who offered assistance via this forum. He provided a script that used exiftool to modify my photo files. He worked with me to tweak the script until it did what I wanted.

Have already applied it successfully to a subset of my photo collection and am quite pleased with the results.

[Phil Harvey]

Excellent.

Alan: Thanks from me too.

- Phil