How I add EXIF Information to Photos, using EXIFTOOL, so they can be searched on in WidowsComrails Logo

How I add EXIF Information to Photos, using EXIFTOOL, so they can be searched on in Widows

Overview

Windows 7, and later versions, have a great features that allows you to search files based on the meta-data associated with the file. Unfortunately when it comes to photographs, and other types of images getting the meta-data into the image can be difficult process. Windows itself provides the ability to enter the date for a single image,

If you are like me, you already have a lot of the information stored in either a text file, spreadsheet or database file, and want a method to bulk load the existing data in to the images. The best tool I have found to do this job is EXIFTOOL, by Phil Havey (you can find it at http://www.sno.phy.queensu.ca/~phil/exiftool/), but it requires you to reformat your meta-data into a format it can read before it can but bulk loaded into the images. Being a programmer, my solution was to write a script that would take my data-file, which started life as an Excel spreadsheet, and create a file full of the EXIFTOOL commands that would do the job for me.

How it Works

The Source File

My meta-data source file is an MS-EXCEL spreadsheet. It not particularly complex, Each row in the sheet contains the information for a single image, with five columns describing the basic information I need. The columns, in order are:

Catalogue number
- this is the images file name (JPG extension is assumed)
Date
- any known date information
Details
- a general description of the photos contents
Location
- where it was taken, if known
Author
- the name of the person that took the photograph
Tags
- optional multiple extra subject tags separated by "; " (semicolon followed by a space)

The Image Files

The image files are normally standard JPEG files, but the same process works for TIFF or any other image file that support EXIF or IPTC meta-data. The image type if hard coded in the script, but it easily changed.

The Script

The script has two major parts to its operation. The first parts reads in a command separated variable file, that has been save from the MS-EXCEL spreadsheet and creates an file called ExifToolCommandList.txt which contains commands for EXIFTOOL. The second part of script takes the ExifToolCommandList.txt file and passes it to EXIFTOOL for processing. It is this step that puts the actual meta-data into the image files.

What you end up with

The Image Files

After running the script you end up with a new image file that contains your catalogued meta-data, and the original image file renamed.

EXIFTOOL EXIF and IPTC fields updated

The meta-data is added to the image file using the following EXIFTOOL field tag names (see the EXIFTOOL documentation for an expansion of the technical details if you wantto know what these fields expand into): XMP-acdsee:Caption gets the date + Location + Details + Catalogue number UserComment gets the date + Location + Details MWG:Creator field gets Author MWG:Copyright is cleared MWG:Description field gets the date + Location + Details + Catalogue number MWG:City fields get the location XMP:Credit=Scanned by Chris Drymalik XMP:Subject field gets the optional multiple extra subject tags The list of fields has been selected so that windows and most common image programs will be able to read and process the meta-data appropriately.

Naming and Data Entry Limitations

Some minor naming limitations need to be adhered to enable the script to operate correctly. These are limitations caused by the scripting parser.

  1. The name of the index file cannot contain any spaces. If it does contain spaces in the name you should replaced them with dash (-) or other characters. Blanks spaces will result in the script not finding the index file and not adding the meta-data to the image files.
  2. Blanks cannot be used in the names of the image files. Leading spaces are also a problem if used to pad the entries in the .CSV file.
  3. Any line in the index file that begins with a semi-colon will be treated as a comment line and ignored.
  4. The optional multiple extra subject tags need a semi-colon (;) followed by a space character as the tag separator. This is a requirement of the standard and seems to be expected by windows.

How To Modify Things

Simple Modifications to the code can easily be made. You just need an text editor and it bit of care. Windows Notepad or any basic text editor will do the job for you (unfortunately word processors, such as MS-Word, will mange the code and make it unusable).

The simplest way to run notepad is to type 'notepad' into the search box and then double click on 'notepad.exe' when it is found. You now can use the file menu to open the '.bat' file start making changes. Once you have finished making changes use 'save as' to give it a new name, making sure to keep the '.bat' file extension

How To Modify The Code

If your not a programmer you might think the code looks rather confusing, but in reality the code itself is not really as complex as it looks. It consists of the quite a lot of comments interspersed with a small number of program instructions. In the following couple of paragraphs I will explain most of the common changes you might want to make.

Setting the Input File Name

The name of the Input file is set in the line:

SET _Inputfile=$NameOfPhotoListingIndexFile.csv

You should change the $NameOfPhotoListingIndexFile part of the line to be the name you have given your file.

Changing the format of the Caption/Description used

The Captions and Descriptions that get stored as meta-data are set in the lines:

echo -XMP-acdsee:Caption=%%B %%D %%C ^(%%~A^)
echo -UserComment=%%B %%D %%C - National Railway Museum, Port Adelaide, Collection - Miscellaneous Negatives Collection ^(CRS 7-1074^)
echo -MWG:Creator=%%E - National Railway Museum, Port Adelaide
echo -MWG:Copyright=
echo -MWG:Description=%%B %%D %%C ^(%%~A^)
echo -MWG:City=%%D
echo -XMP:Credit=Scanned by Chris Drymalik
echo -XMP:Subject=%%F

Each of these lines sets a different element of the meta-data to a particular value. The format of the line actual represent a command that will be executed by the EXIFTOOL program. Echo cause the line to be written to the command file, and the rest of the line is the actual command. The %%A, %%B etc. are simply place holders to the columns in you MS-Excel spread sheet. Column one use place holder %%A, column two %%B, column three %%C etc., with the last column being %%E.

Thus the line

echo -MWG:Description=%%B %%D %%C ^(%%~A^)

sets the meta-data description (the meaing of MWG:Description is explained in the EXIFTOOL documentation should you want to know what meta-data fields it expands into) to be the following: The contents of column two (%%B - date), followed by a space, then the contents of column four (%%D - location), followed by a space, then the contents of column three (%%C - description), followed by a space, then the contents of column one (%%A - the file name) inside brackets.

Some of the other lines contain ordinary text that can be replaced. Such as:

echo -XMP:Credit=Scanned by Chris Drymalik

The "Scanned by Chris Drymalik" is ordinary text that can be replaced by any other text. The only thing you should be careful of it that some special symbols need to written in funny ways - such as the left bracket being written as ~( and right bracket ~). The % symbols is also reserved for special use in a similar manner. I recommend that you keep you text simple and only use letter and numbers and the occasional brackets.

Setting the file name type

The line:

echo .\%%~A.jpg

Sets the file name and extension. If you are adding meta-data to TIFF files, just change the .jpg to .tif. Other image formats that support EXIF and XML meta-data can be modified by this program in a similar manner - check the EXIFTOOL online documentation for a list of the image data types it supports.

How To Run You Modified Program

Open your photo index file in MS-EXCEL, and use the 'save as' menu to save a new copy of the file with the name that is named in the program '.bat' file. Make sure you change the 'Save as type' to 'CSV (Comma Delimited) (*.csv)' as this is the format the program needs to be able to read the input file correctly.

Make sure you have you *.csv file, images and program code in the same directory - I recommend you always make changes to a copy of the images, not the originals, just in case things go wrong. Also make sure you have a copy of the EXIFTOOL program in this directory as well.

To run the program double click on the '.bat' file it will proceed to do it thing. It runs in two passes, the first pass creates a command file and then pauses waiting for you to press the Enter key. At this stage you can use an alternative window to inspect the ExifToolCommandList.txt file that has been created. This contains all the commands that will be passed to EXIFTOOL to add the Meta-Data to the images.

To commence adding the meta-data to the images, press the Enter key in the script window and the images will be modified. If you have a large number of images it might take a while to process things.

Any errors will be presented as they occur int he script window. You can pause the script window scrolling by pressings CNTL-S (i.e. hold down the CNTL and S keys at the same time). To resume paused processing press CNTL-S again.

The Script

The script is displayed below, or can be downloaded from The Computer Code page. I recommend that you download it rather than try and copy it from this page as the download version retains all the correct indenting and formatting, plus includes this page for reference as a ms-word document .

@ECHO OFF
goto  :TopOfCode
This routine adds EXIF an IPTC information to JPGs and other image files by creating a command file that can be used by the EXIFTOOL program. The input information
comes from a CSV file that is exported from MS-EXCEL.
No special usage parameters are provided
Input:
Format of CSV File:
The input file should be a CSV file exported from MS-EXCEL using the 'save as' command with a 'Save as type' of 'CSV (Comman delimited)(*.csv)'.
named as appropriate.
Note: lines that have a semi-colon (;) at the start are ignored (default action of the 'FOR /F' command)
Columns are:
Column 1 (A) : identifier for image - i.e the name of the file that is going to have EXIF/IPTC information added to it.
Column 2 (B): date picture taken (optional)
Column 3 (C): description of image
Column 4 (D): location of image
Column 5 (E): author of image
Column 6 (F): tags (separated by semi-colons)
Output:
Command file that can be used by the EXIFTOOL program. EXIFTOOL call syntax is 'EXIFTOOL -@ ExifToolCommandList.txt'
:TopOfCode
setlocal enableDelayedExpansion
SET _Inputfile=$NameOfPhotoListingIndexFile.csv
del ExifToolCommandList.txt
rem Get the file - first column is variable A, next is B etc. and build up the command file
echo Starting to build EXIFTOOL command file
rem The extra read is so that I can turn nulls ',,'  into ', space  ,' so that they are read correctly
for /f "delims=" %%L in (%_InputFile%) do (
set line=%%L
set line=!line:,,=, ,!
set line=!line:,,=, ,!
set line=!line:,,=, ,!
set line=!line:,,=, ,!
set line=!line:,,=, ,!
for /f "tokens=1-6 delims=," %%A in (^"!line!^") do (
echo .\%%~A.jpg
echo -XMP-acdsee:Caption=%%B %%D %%C ^(%%~A^)
echo -UserComment=%%B %%D %%C - National Railway Museum, Port Adelaide, Collection - Miscellaneous Negatives Collection ^(CRS 7-1074^)
echo -MWG:Creator=%%E - National Railway Museum, Port Adelaide
echo -MWG:Copyright=
echo -MWG:Description=%%B %%D %%C ^(%%~A^)
echo -MWG:City=%%D
echo -XMP:Credit=Scanned by Chris Drymalik
echo -XMP:Subject=%%F
echo -execute
)
)>> ExifToolCommandList.txt
rem echo========SOF
rem TYPE ExifToolCommandList.txt
rem echo========EOF
echo Command file built - check output then press ENTER to proceed with adding information to picture
pause
EXIFTOOL -@ ExifToolCommandList.txt
echo EXIFTOOL processing comp[lete - press ENTER to finish
pause
:End