Author Topic: Merge IPTCMETA DATA from Spreadsheet into images  (Read 35530 times)

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Merge IPTCMETA DATA from Spreadsheet into images
« on: October 18, 2011, 08:45:33 AM »
Hi, This is my first question at this forum, and I am asking because I have been unable to find a solution elsewhere.
 
I have 20,000 photos without embedded META keywords. Each image has a unique file name (example 201110_12345.jpg
 
Keywords for each image do exist on an .XLS spreadsheet in IPTC format (one column has a DESCRIPTION, another a TITLE, and others contain KEYWORDS.

My task is to merge the keywords with the images …from the spreadsheet into the image.
 
Programs exist to export META DATA from images, but I would like to do the reverse.

Many thanks in advance for your ideas.

I am not running Photo Mechanic, but would be happy to use it on my Macbook Pro … OS X 10.6 …. If it can solve my problem.

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #1 on: October 18, 2011, 10:16:18 AM »
Hi, This is my first question at this forum, and I am asking because I have been unable to find a solution elsewhere.
 
I have 20,000 photos without embedded META keywords. Each image has a unique file name (example 201110_12345.jpg
 
Keywords for each image do exist on an .XLS spreadsheet in IPTC format (one column has a DESCRIPTION, another a TITLE, and others contain KEYWORDS.

My task is to merge the keywords with the images …from the spreadsheet into the image.
 
Programs exist to export META DATA from images, but I would like to do the reverse.

Many thanks in advance for your ideas.

I am not running Photo Mechanic, but would be happy to use it on my Macbook Pro … OS X 10.6 …. If it can solve my problem.


Photo Mechanic can do what you're asking for.  You would use Code Replacement, {variables} and the IPTC Stationery Pad to accomplish the task.  Get a demo version of PM, post a few rows of your spreadsheet (tab-separated) and I'll walk you through the process.

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTC META DATA from Spreadsheet into images
« Reply #2 on: October 19, 2011, 07:43:10 AM »
Hi Kirk, Many thanks for your response. Here is an example of myspreadsheet structure.

FILENAME:
200902_0001.jpg
200902_0002.jpg
200902_0003.jpg

CAPTION:
House
Child playing with toy
Zulu warrior

MOST IMPORTANT KEYWORDS:
Architecture building buildings
Child children toy toys
Man adult Zulu warriors

MEDIUM STRENGTH KEYWORDS:
residence home homes
play fun games happy
weapon weapons

WEAK KEYWORDS:
tin shack metal rust poverty
plastic car
spear knife native

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTC META DATA from Spreadsheet into images
« Reply #3 on: October 19, 2011, 07:47:52 AM »
Hi Kirk, Many thanks for your response. Here is an example of myspreadsheet structure.

FILENAME:
200902_0001.jpg
200902_0002.jpg
200902_0003.jpg

CAPTION:
House
Child playing with toy
Zulu warrior

MOST IMPORTANT KEYWORDS:
Architecture building buildings
Child children toy toys
Man adult Zulu warriors

MEDIUM STRENGTH KEYWORDS:
residence home homes
play fun games happy
weapon weapons

WEAK KEYWORDS:
tin shack metal rust poverty
plastic car
spear knife native


OK.  What I actually need is a few rows pasted into your reply.  Just save as tab-separated from your spreadsheet, open the file in a text editor, copy a few rows and then paste them into your reply to this message.

Thanks,

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTC META DATA from Spreadsheet into images
« Reply #4 on: October 19, 2011, 08:05:23 AM »
Hope this is correct:

Filename   Caption128   key 50   key 300   key 856         
200902_0001.jpg   House   Architecture building buildings    residence home homes    tin shack metal rust poverty          
200902_0002.jpg   Child playing with toy   Child children toy toys    play fun games happy   plastic car           
200902_0003.jpg   Zulu warrior    Man adult Zulu warriors   weapon weapons    spear knife native           
          

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTC META DATA from Spreadsheet into images
« Reply #5 on: October 19, 2011, 08:41:45 AM »
Hope this is correct:

Filename   Caption128   key 50   key 300   key 856         
200902_0001.jpg   House   Architecture building buildings    residence home homes    tin shack metal rust poverty          
200902_0002.jpg   Child playing with toy   Child children toy toys    play fun games happy   plastic car           
200902_0003.jpg   Zulu warrior    Man adult Zulu warriors   weapon weapons    spear knife native           
          

Yes.  Ok, so can you put commas between your keywords?  If you don't then "Architecture building buildings" will be a single keyword.

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #6 on: October 19, 2011, 09:54:45 AM »
How about this mess. Is it something you can work with?

Filename;Caption128;key,50;key,300;key,856;;;,
200902_0001.jpg;House;Architecture,building,buildings,;residence,home,homes,;tin,shack,metal,rust,poverty;,;;
200902_0002.jpg;Child,playing,with,toy;Child,children,toy,toys,;play,fun,games,happy;plastic,car,;,;;
200902_0003.jpg;Zulu,warrior,;Man,adult,Zulu,warriors;weapon,weapons,;spear,knife,native,;,;;

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #7 on: October 19, 2011, 10:04:47 AM »
How about this mess. Is it something you can work with?

Filename;Caption128;key,50;key,300;key,856;;;,
200902_0001.jpg;House;Architecture,building,buildings,;residence,home,homes,;tin,shack,metal,rust,poverty;,;;
200902_0002.jpg;Child,playing,with,toy;Child,children,toy,toys,;play,fun,games,happy;plastic,car,;,;;
200902_0003.jpg;Zulu,warrior,;Man,adult,Zulu,warriors;weapon,weapons,;spear,knife,native,;,;;


No, it would have to be like this:

200902_0001.jpg    House    Architecture,building,buildings    residence,home,homes    tin,shack,metal,rust,poverty
200902_0002.jpg    Child playing with toy    Child,children,toy,toys    play,fun,games,happy    plastic,car
200902_0003.jpg    Zulu warrior    Man,adult,Zulu,warriors    weapon,weapons    spear,knife,native

Only your 'keywords' columns need commas or semi-colons between each word.  Tabs need to be between each column.

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #8 on: October 19, 2011, 10:17:13 AM »
Puzzles are nice. If I REPLACE semi-colons with SPACE I get this:

Filename Caption128 key,50 key,300 key,856   ,
200902_0001.jpg House Architecture,building,buildings, residence,home,homes, tin,shack,metal,rust,poverty , 
200902_0002.jpg Child,playing,with,toy Child,children,toy,toys, play,fun,games,happy plastic,car, , 
200902_0003.jpg Zulu,warrior, Man,adult,Zulu,warriors weapon,weapons, spear,knife,native, , 

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #9 on: October 19, 2011, 10:36:58 AM »
Puzzles are nice. If I REPLACE semi-colons with SPACE I get this:

Filename Caption128 key,50 key,300 key,856   ,
200902_0001.jpg House Architecture,building,buildings, residence,home,homes, tin,shack,metal,rust,poverty , 
200902_0002.jpg Child,playing,with,toy Child,children,toy,toys, play,fun,games,happy plastic,car, , 
200902_0003.jpg Zulu,warrior, Man,adult,Zulu,warriors weapon,weapons, spear,knife,native, , 


You need tab characters instead of spaces between the columns.  Are you doing your find and replace in your spreadsheet?  That's where you need to do it.  Then when you save as tab-separated text, you'll get your tabs between your columns.

I know this seems pedantic, but once you have tabs between your columns and commas between your keywords, the rest is fairly easy and quite automated.

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #10 on: October 20, 2011, 06:45:11 AM »
How about this .... tabs and commas?

Filename   Caption128   key,50   key,300   key,856         ,
200902_0001.jpg   House   Architecture,building,buildings,   residence,home,homes,   tin,shack,metal,rust,poverty   ,      
200902_0002.jpg   Child,playing,with,toy   Child,children,toy,toys,   play,fun,games,happy   plastic,car,   ,      
200902_0003.jpg   Zulu,warrior,   Man,adult,Zulu,warriors   weapon,weapons,   spear,knife,native,   ,

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #11 on: October 20, 2011, 07:39:20 AM »
How about this .... tabs and commas?

Filename   Caption128   key,50   key,300   key,856         ,
200902_0001.jpg   House   Architecture,building,buildings,   residence,home,homes,   tin,shack,metal,rust,poverty   ,      
200902_0002.jpg   Child,playing,with,toy   Child,children,toy,toys,   play,fun,games,happy   plastic,car,   ,      
200902_0003.jpg   Zulu,warrior,   Man,adult,Zulu,warriors   weapon,weapons,   spear,knife,native,   ,


Great.  While it would be better to not have the trailing and orphan commas, they probably won't mess things up.

Now, a question: do you want all of your keywords columns to go into the keywords field of your images' IPTC data, or just one of the columns?

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #12 on: October 20, 2011, 08:03:36 AM »
Thanks for bearing with me Kirk. All keyword columns are necessary, starting with the column called Key50.

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25025
    • View Profile
    • Camera Bits, Inc.
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #13 on: October 20, 2011, 08:25:47 AM »
Thanks for bearing with me Kirk. All keyword columns are necessary, starting with the column called Key50.

OK, so here is what you're going to do:

Open up Photo Mechanic and click on the Edit menu.  Down near the bottom is the Settings sub-menu.  Move your mouse cursor onto it and a sub-menu will appear.  Choose 'Set Code Replacements...'

In the dialog that appears, click on the 'Add...' button and pick the text file that you prepared.  Now at the bottom of the Code Replacement dialog is a field titled 'Delimiter character:' which by default is backslash ( \ ).  If that is difficult for you to type then you can enter something else like the equals sign ( = ).  For my examples I'll use backslash but if you use something else just substitute your preferred delimiter character wherever you see backslash being used.

Click the OK button in the Code Replacement dialog.

Back in the main window, open up a folder containing some of your images (but not all 20,000 at once, please!)  Once you see your images appear, select one.  Then from the Image menu, choose 'IPTC Stationery Pad...'

In the IPTC Stationery Pad dialog, click in the Caption field.  Enter:

\{filename}#1\

If you already have captions in your photos and you don't want them wiped out, click on the '+' checkbox to the left of the Caption field.  This will cause the new data to be appended to the existing data.

Next, click in the Keywords field.  Enter:

\{filename}#2\ \{filename}#3\ \{filename}#4\

If you already have keywords in your photos and you don't want them wiped out, click on the '+' checkbox to the left of the Keywords field.  This will cause the new data to be appended to the existing data.

If there is any other static information that you want to enter and have it applied, like Caption Writer (you) or Photographer enter it now in the appropriate fields.

If everything looks good, click on the 'Apply Stationery to Selected' button.  Your single image should now have a caption and keywords (as long as its filename has an entry in your Code Replacement data).

You can verify this by moving your mouse over the photo and then clicking the 'i' button when it appears over your photo.  This brings up the IPTC Info dialog and shows the IPTC and XMP metadata contained in your photo.  If everything looks good then close the IPTC Info dialog.  Select the rest of your images with Command-A and then Command-click on your first image to deselect it since it is complete and we don't want to double apply the data.

Once you have your selection setup, choose 'Apply IPTC Stationery Pad to Photos' command on the Image menu.  Depending on how many images you have, this may take a few seconds or several minutes.  When complete, your images should be all captioned and keyworded.  You can spot check your metadata by clicking on the 'i' button which appears when you put your mouse cursor over an image.

Please let me know if you run into any trouble or don't understand any of my instructions.

-Kirk

Offline Old timer

  • Member
  • **
  • Posts: 55
    • View Profile
Re: Merge IPTCMETA DATA from Spreadsheet into images
« Reply #14 on: October 20, 2011, 10:18:07 AM »
This is an incredible way to learn ... and fun ... and it works. Your instructions are clear and understood. Thank you Kirk.

Here are two areas that may require some thought:

1) Commas in Caption

Because my original spreadsheet entries did not contain COMMAS I added them later. During this process COMMAS were added to all fields including the caption. (My fault and part of the replace SPACE with COMMA theme).

The caption looks like this: Child,playing,with,toy

2) I threw in a few images without a corresponding spreadsheet entry (including 200902_0005.jpg). It now contains this:

=200902_0005.jpg#2= =200902_0005.jpg#3= =200902_0005.jpg#4=

Could be a problem for some photographers as it seems to wipe out the original data (as requested) and replace it with Gibberish. Is there a way to instruct the software to IGNORE IMAGES THAT DO NOT HAVE A SPREADSHEET IMAGE NUMBER?

... With 2GB of Ram ... maximum images per folder?

It’s been fun .. and maybe we are not finished yet .....