Compare EXIF, IPTC, XMP metadata stored in image files to the Caption, Description and other metadata stored in a RootsMagic 5 database for image files linked to its Media Gallery and copy between the files and the database.

The principles and procedures outlined here, while based on the way Picasa uses metadata stored in image files, are applicable with appropriate modifications to relating other photo managers to RootsMagic 5.

Compare RM5 and Image File Metadata

Introduction

I use Picasa to manage my collection of photographs and other media and often write captions in Picasa’s picture editor for photos that I upload to Picasaweb online albums. Of course, working in RootsMagic, I get to write captions and descriptions for some of the same photos and for many others that I may not have captioned in Picasa. LessTX and others have asked that RM have some capability of using metadata fields stored in the image files as a way of loading values into RootsMagic or storing them in the image (e.g., Insert Image Metadata into Database and some of the results in this search of the RootsMagic Forums). This sounded attractive on the surface and so I have looked into it further.

Description

One result can be seen in this screenshot of a query in SQLite Expert Personal (which displays the thumbnail images, unlike most other SQLite managers):

Under the left red box is the media caption and description metadata stored in the RootsMagic 5 database in the MultiMediaTable, along with the thumbnail image, image file name and path. Under the right hand box are the values extracted by ExifTool from the Caption-Abstract and Description metadata fields stored in the image file by Picasa. With this convenient comparison of the two sources of the image metadata, I can readily see discrepancies between the two and take any needed corrective action in the corresponding application.

Some Observations and Reservations re Automatic Synchronisation

I’ll describe how I developed this query after discussing some of my observations and reservations about automatically synchronizing the captions and descriptions between the RootsMagic database and the images.

1. Picasa ver 3.8.0 and RootsMagic 5.0.1.0
2. Picasa stores the same value in both the Caption-Abstract and Description tags in the image (but I cannot account for one example above of several that have a Caption-Abstract value but not Description – perhaps it was written by an earlier version). Therefore, there cannot be a 1:1 relationship between the RootsMagic metadata fields and the image’s metadata fields.
3. Neither RootsMagic nor Picasa have a low limit on the length of the string that can be stored in their respective places. However, the RootsMagic caption should be constrained to be quite short because so little space is provided for printing it in the one place it is used, a Scrapbook report. If the Description is also included in the Scrapbook, then both Caption and Description values must be severely limited. Picasa displays captions most generously in its slideshows, less so in the Photo Editor and to 16-60 characters under its variably zoomed thumbnails.
4. Given these significant differences, I cannot see a way of automatically copying from the metadata stored in the image file to the RM fields, without following up to edit them into appropriate sizes. The reverse may be more successful: concatenate the RootsMagic fields and copy the same value to both Caption-Abstract and Description tags in the image file.
5. That said, the use of the image file in a Picasa Album might dictate a quite different caption from that used in a RootsMagic Scrapbook report (just as some users were dismayed to discover that in upgrading from RM4 to RM5 they had lost the ability to have a different caption for the same image when associated with different persons, events, etc. – the usage dictates the caption).
6. Therefore, caption copying must be manually controlled, except for copying to blank fields which, nonetheless, is liable to require manual follow-up.
7. Although these observations are specific to RootsMagic 5-7.6 and Picasa, the RM constraints also apply in the general case and the conclusions may not be significantly altered, except for the possible maintenance of the separation between Caption and Description.
8. I have not explored whether there is any appropriate image metadata tags that may correspond with RM’s media Date and Reference Number.

Installation

• Copy the windows command line shell sqlite3.exe from the sqlite-shell-win32-x86-3nnnnnn.zip file downloaded from the Precompiled Binaries for Windows section of sqlite.org to a folder of your choosing. The batch file below expects it to be in C:\Program Files (x86)\SQLite but that can be revised to suit.
• Install exiftool.exe from ExifTool in a folder along with all of the related batch files and sql scripts. It could be located elsewhere if you revise the batch file accordingly.
• Download RM5comparePicasa.bat (rev 2020-05-31) to the common folder.
• Create a shortcut to the batch file RM5comparePicasa.bat with the Target property [“C:\yourpath\RM5comparePicasa.bat” “C:\yourpath\yourRMdatabase.rmgc”], enclosing the database path in double quotes. Name the shortcut something like “RMcomparePicasa yourdatabase“. Make such a shortcut for each database you want to review.
• Download ImportPicasaDescriptions.sql to the common folder

Procedure

When you click on the shortcut, it launches the batch (or Windows command) file with the path to your database file passed to it in the %1 register. The batch file deletes the old files it created in a previous run and then proceeds to build a short sql script GetImageListFromRM.sql. Then it calls sqlite3 to open your database file and run the script which outputs the results of the query to the ExifTool arguments file RMimagefiles.args, to which it then appends the -execute argument. The args file contains the list of JPG files with full pathnames recorded in the RM5 database Media Gallery. [1]

Next the batch file calls ExifTool to go through the list of image files, find them if it can, and extract the values from the Caption-Abstract and Description tags along with the paths and file names to a new file, ImageDescriptions.txt. There’s a pause to review any possible error reports in the command window, e.g., file not found. [2]

Now the batch file calls sqlite3 again to open or create the local database RM-Media.db3, creates PicasaTable with four columns matching those in the tab-delimited ImageDescription.txt and imports the latter into the table. [3]

That’s it for the batch file, except to generate instructions as to what to do next:
Open RM-Media.db3 with your SQLite manager (SQLite Expert Personal if you want to see the thumbnails), copy and run some SQLite statements the batch generated. [4]

If everything worked as intended, you should see results like this screenshot (adjust column widths to suit, of course).

[Inline comment:

ve3meo Jan 22, 2012

There is a flaw in the above to be corrected if there are multiple image files with the same file name in different paths. It has been correctly done in the RM5copyMetaDataToRM query.]

Copy RM Image Metadata to Image File (Picasa compatible)

Description

This is basically a query in RM-Media.db3 (created and updated by the Compare utility with the RM5 database attached) to extract the list of files for which RM has a caption and the image file does not, along with the concatenation of caption and description values, and getting those into a file in the format required for ExifTool arguments with the appropriate arguments to tell ExifTool to write the value into the Caption-Abstract tag of the image files, and then run ExifTool against the args file. All this is managed by a batch file as was done for the Compare procedure. Here is an example showing how the RM5 Caption + Description end up being displayed as a Picasa caption.

This was a very tricky procedure to finally get going and resulted in considerable hair loss! The arguments for ExifTool must be one per line so carriage return/line feed controls (CR/LF) in the RM5 values must be replaced. Thus the white space above the URL in the RM Description disappears in the Picasa caption, the CR/LFs being replaced by periods.

This same difference applies in using ExifTool to copy the metadata from the image to RM: ExifTool itself substitutes a period for CR/LF embedded in the metadata values. Thus metadata values on either side containing CR/LF cannot be exactly replicated on the other, at least with ExifTool as the intermediary.

Installation

• sqlite3.exe and exiftool.exe are already installed as above
• download RM5copyMetadataToImageFileExifToolArgList.sql to the same folder as RM-Media.db3

[Inline comment:

ve3meo Jan 22, 2012

There is a flaw in the above to be corrected if there are multiple image files with the same file name in different paths. It has been correctly done in the RM5copyMetaDataToRM query.]

/* RM5copyMetadataToImageFileExifToolArgList.sql
*/
SELECT
  '-Caption-Abstract='
  ||
  REPLACE(CAST(RM.Caption AS TEXT),x'0D0A','.')
  || ' ' ||
  REPLACE(CAST(RM.Description AS TEXT),x'0D0A','.')
  || CAST(X'0D0A' AS TEXT) ||
  RM.MediaPath
  ||
  RM.MediaFile
  || CAST(X'0D0A' AS TEXT) ||
  '-execute' || Pic.ROWID
  || CAST(X'0D0A' AS TEXT)
    AS Args
FROM MultiMediaTable AS RM
  INNER JOIN PicasaTable AS Pic
  ON RM.MediaFile COLLATE NOCASE = Pic.MediaFile
WHERE
  (RM.Caption IS NOT NULL OR RM.Description IS NOT NULL)
  AND
  (Pic.Caption LIKE '-' AND Pic.Description LIKE '-')
;

• RM5copyMetadataToImageFileExifToolArgList.bat.bak Download to the same folder: (Remove .bak extension added to bypass some systems’ security)

ECHO OFF
ECHO .ATTACH DATABASE '%1' > RM5copyMetaData.sql
ECHO < RM5copyMetadataToImageFileExifToolArgList.sql >> RM5copyMetaData.sql

• Download RM5copyMetadataToImageFile.bat.bak to the common folder and remove the .bak extension
• Create a shortcut to the batch file RM5copyMetadataToImageFile.bat with the Target property [“C:\yourpath\RM5copyMetadataToImageFile.bat” “C:\yourpath\yourRM5database.rmgc”] and name it something like “RM5copyMetadataToImageFile – yourdatabase“. Make such a shortcut for each database you want to copy captions from.

ECHO OFF
REM RM5copyMetadataToImageFile.bat
REM 
REM Build sql file
ECHO ATTACH DATABASE '%~1' AS RM ; > RM5copyMetaData.sql
TYPE RM5copyMetadataToImageFileExifToolArgList.sql >> RM5copyMetaData.sql
"C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < RM5copyMetaData.sql > RM5copyMetaDataToImageFile.args
ECHO .
ECHO Check RM5copyMetaDataToImageFile.args before proceeding to write to the image files 
PAUSE
exiftool -@ RM5copyMetaDataToImageFile.args > RM5copyMetaDataToImageFile.log
ECHO .
ECHO Finished copying captions to image files
PAUSE
END

Procedure

First run the Compare utility against your database to ensure that the RM-Media.db3 is up-to-date with the current metadata from all of the images used by the database. Then click on the shortcut “RM5copyMetadataToImageFile – yourdatabase“. The Windows Command console will open and, almost immediately, you will asked to

Check RM5copyMetaDataToImageFile.args before proceeding to write to the image files

Open the file (in the folder common to all of the .bat and .sql files you have installed as instructed above) with a non-wrapping text editor and note that each file is addressed by a triplet of three lines and an empty line after as:

-Caption-Abstract=Obituary - Colin xxx North Bay Nugget - Aug 13, 1956
C:MyDocsFamilyTreeHolxxxScrapbookxxxxxxn, Colin - obituary - 1956.jpg
-execute2
 
 -Caption-Abstract=Registration of birth of Helen xxx 13
 C:MyDocsFamilyTreeHolxxxScrapbookxxxxxxth, Helen Elizabeth - Birth Register xxx13.jpg
 -execute3
 ...

There should be nothing else in this args file than the triplets of single lines as above. The number following -execute is the PicasaTable rowid, which may be useful in tracking down a problem if ExifTool encounters a bad image file (I think it will report that number in its error message).

Then, press any key to launch ExifTool to work on the list of files. ExifTool copies each file to a .jpg_original file and re-writes the .jpg file with the new Caption-Abstract value added.

After some time, proportional to the number of files being processed, you will see the message “Finished … Press any key to continue.” Because the batch file is really basic with no error-trapping, you will see this message regardless of errors. It’s possible that no file may have been processed. Inspect the file RM5copyMetaDataToImageFile.log for any error message.

Assuming no errors, if Picasa is set to monitor the folders where these image files are located and was running at the time they were being revised by ExifTool, you will have seen the little pop-up message from Picasa as it updates its thumbnails and its internal storage of captions for searching. The ability to search through captions is one of Picasa’s strong features, along with its facial recognition and multiple views: Albums, People, Folders.

Copy Image Metadata from JPG to RM

This procedure is trivial compared to the opposite direction. The heavy work has been done in the Compare… procedure, which should be carried out before attempting to copy metadata to the RootsMagic database as the Copy… relies on the RM-Media.db3 database created and updated by Compare… The only operation that makes sense to me that would benefit from such a procedure is to copy multiple image files’ Caption-Abstract values to empty RootsMagic captions for those files. Copying onto existing captions is necessarily a manual process and could be done with copy/paste between the applications.

This is essentially a SQL query against both the RootsMagic database and the RM-Media.db3 database. However, it’s complicated by the fact that I cannot figure out a way to make a single UPDATE statement work for multiple captions so a batch procedure is needed to generate a sql script file with multiple UPDATE statements, one per caption to be copied, and run that.

Installation

• requires sqlite3.exe command line shell as above
• Download RM5copyMetadataToRM.sql to the common folder:

-- RM5copyMetadataToRM.sql
-- generates an UPDATE statement for each blank caption in RM for which there is a caption extracted from the image file
-- requires RM-Media.db3 with the RM5 database ATTACHed
SELECT 'UPDATE MultiMediaTable SET Caption=(SELECT Caption FROM PicasaTable WHERE ROWID=' || PicID ||') WHERE MediaID=' || MediaID ||';'
AS Statement
FROM
(
SELECT MediaID, Pic.ROWID AS PicID
FROM MultiMediaTable AS RM
INNER JOIN PicasaTable AS Pic
ON REPLACE(RM.MediaPath || RM.MediaFile, '', '/')
LIKE Pic.MediaPath || '/' || Pic.MediaFile
WHERE Pic.Caption NOT LIKE '-' AND RM.Caption IS NULL
)
;

• RM5copyMetadataToRM.bat.bak Download to the common folder: (Remove .bak extension added due some systems’ security)

ECHO OFF
REM RM5copyMetadataToRM.bat
REM Copies to blank RM5 captions the values from Caption-Abstract metadata stored 
REM in the corresponding image files
REM Uses PicasaTable in RM-Media.db3 created by the related Compare routine
REM and the MultiMediaTable of the target RootsMagic 5 database.
REM Requires the sqlite3.exe command line shell
REM  
REM Build and run sql script file that generates UPDATE statements for each caption to be copied
ECHO ATTACH DATABASE '%~1' AS RM ; > RM5copyMetaData.sql
TYPE RM5copyMetadataToRM.sql >> RM5copyMetaData.sql
"C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < RM5copyMetaData.sql > RM5UpdateCaptions.sql
REM
REM Build and run the sql script file that UPDATEs the target RootsMagic 5 database captions
ECHO ATTACH DATABASE '%~1' AS RM ; > RM5copyMetaData.sql
TYPE RM5UpdateCaptions.sql >> RM5copyMetaData.sql
ECHO Check RM5copyMetaData.sql before running the caption UPDATEs
PAUSE
"C:\Program Files (x86)\SQLite\sqlite3.exe" RM-Media.db3 < RM5copyMetaData.sql
ECHO Blank RM5 Captions Updated
PAUSE
END 

• Create a shortcut to the batch file RM5copyMetadataToRM.bat with the Target property [“C:\yourpath\RM5copyMetadataToRM.bat” “C:\yourpath\yourRM5database.rmgc”] and name it something like “RM5copyMetadataToRM – yourdatabase“. Make such a shortcut for each database to which you want to copy captions.

Procedure

First run the Compare utility against your database to ensure that the RM-Media.db3 is up-to-date with the current metadata from all of the images used by the database. Then click on the shortcut “RM5copyMetadataToRM – yourdatabase“. Follow the on-screen instructions. Use RootsMagic to check the new captions.

Further Considerations

1. The two unidirectional copy caption to blank caption procedures could be combined into one batch and made to standalone without the need for the Compare… procedure to be run in advance.
2. A comparison between RM’s Caption + Description and the image file’s Caption-Abstract by substituting the CR/LF’s in the former with periods might be feasible.
3. If MetaData comparisons and copying/editing were part of a future RM feature or outboard utility, it would be desirable to have a directional choice per image where there are non-blank values in both.
4. What relationship should there be between RM’s Date and Reference Number fields and any EXIF, IPTC, or XMP tag?
5. What changes may be needed for compatibility with media managers other than Picasa?
6. LessTX wish1 LessTX wish2 vyger wish vyger wish2 Athena’s 2008 wish

Inline comments