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, like me, you already have a lot of the information stored in either a text file, spreadsheet or some form of database file, you need a method to bulk load your 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 https://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 be 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.
Read on if you want to find out how I do it, and see a sample program. I've been doing this for a quite a while to many thousands of images, so I think I have overcome most of the more unusual ways Windows stores metadata (and special thanks for Phil Havey for his amazing program that does the actual writing of the data into the images files).
My meta-data source file is an MS-EXCEL spreadsheet, but if you have it in some other format you will need some method to export it as a comma separated variable file (CSV). The file layout is not particularly complex, each row in the sheet contains the information for a single image, with six columns describing the basic information needed.
The columns, in order are:
Note: Don't use commas in your text data because it will stuff up the layout of the CSV file.
The image files are normally standard JPEG files, but the same process works for TIFF, PNG 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 has two major parts to its operation. The first parts reads in a comma separated variable file, that has been save from the MS-EXCEL spreadsheet (using the file saveas option) and creates an file called ExifToolCommandList.txt which contains commands for import into 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.
After running the script you end up with a new image file that contains your catalogued meta-data, and the original image file renamed.
Not all programs seem to be consistent in their expectation of meta-data locations so the scripts list of fields is fairly extensive, and has been selected so that windows and most common image programs I've come across will be able to read and process the meta-data appropriately.
Some minor naming limitations need to be adhered to enable the script to operate correctly. These are limitations caused by the scripting parser.
Simple Modifications to the code can easily be made. You just need a text editor and a 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 can notepad's file menu to open the '.bat' file and make changes. Once you have finished making changes use 'save as' to give it a new name, making sure to keep the '.bat' file extension
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.
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.
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 -Title=%%B %%D %%C ^(%%~A^) 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:
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.
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.
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 in the script window. On Windows 10 you can pause the script window scrolling by pressings Break (the Break key is usually on the keyboard above arrow keys). To resume paused processing press ENTER.
@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:,,=, ,! 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 -Title=%%B %%D %%C ^(%%~A^) 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 rem replace the EXIFTool statement above with following if you are using the perl script version of EXIFTOOL rem perl C:\windows\EXIFTOOL.pl -@ ExifToolCommandList.txt -m echo EXIFTOOL processing comp[lete - press ENTER to finish pause :End
