import data from excel/csv file into IPTC/XMP caption fields

[Glorious1]

I have 100 JPG images and an Excel file with detailed caption/description information for each. I'm hoping to automate pasting of the metadata into the image files.

I've read some posts talking about using exiftool for automatic metadata manipulations, but none that covered this goal. I managed to add exiftool (the one that comes with XnViewMP) through File > Open With > Configure Programs. But I have no idea what Arguments I need to add in that window.

Does anyone know?

[michel038]

You need to export the Excel file in CSV format (using commas as separators).
In this file, the first line describes the columns, with right exiftool syntax for example :

Code: Select all

SourceFile,IPTC:Caption-Abstract,IPTC:Headline

The first column will contain the full name of each photo files (ex D:\Folder1\my photos\DSC001.JPG)
The second column will contain the captions
The third column will contain titles
These values are separated by commas, and there are no spaces.

ExifTool command :
exiftool -k -csv="c:\folder2\MyCsvFile.csv" "c:\Test\" -out "C:\test\result\"
-csv is the most important command that causes the specified csv file to be read
"C:\Test" is the folder containing the photos to update
-out "C:\test\result\" is the folder where updated photos will be stored
This prevents damage to the originals, and avoids the creation of bak files by ExifTool
Of course, choose your own paths and folders for these operations

From within XnViewMP, add this parameter for an exiftool "open with" command

Code: Select all

-k -csv="c:\folder2\MyCsvFile.csv" "c:\Test\" 

But in order to run the “open with” command, you'll have to select at least one file ... I'm afraid this will interfere with this exiftool command, which doesn't need any designated source file ...
The other solution is to create a Windows shortcut outside XnviewMP, calling exiftool.exe
Then edit the shortcut properties and write :
(... path...)\exiftool.exe -k -csv="c:\folder2\MyCsvFile.csv" "c:\Test\"
in the first "Target" field

[Glorious1]

Thank you very much for that helpful reply, Michel. May I ask two followup questions?

1. I don't really have Headlines/titles. The second bit of metadata I would like to add is actually Categories, which I think in IPTC are called Keywords? What would the appropriate column heading be for that in the csv file?

2. Unfortunately I'm not on Windows, but macOS. For the Windows shortcut, I wonder if the Mac equivalent would be Automator? Or maybe just invoke exiftool on the commandline/Terminal with those arguments should work also?

[jamesmwphd]

Yeah, exiftool is your best bet for this. You can use a simple script to pull in data from your Excel (after saving it as a CSV).

Code: Select all

exiftool -csv=yourfile.csv -overwrite_original -iptc:caption-abstract=Description -xmp:description=Description *.jpg

Just replace "yourfile.csv" with your actual CSV file name.

[michel038]

1. I don't really have Headlines/titles. The second bit of metadata I would like to add is actually Categories, which I think in IPTC are called Keywords? What would the appropriate column heading be for that in the csv file?

Categories in xnviewmp are records in an internal database "the catalog" (saved in xnview.db file, a file it's a good idea to duplicate as a backup).
These categories have the meaning you decide to give them, but classically, they are used as keywords, as you can do multiple queries in the "catalog filter".
When a category is assigned, xnviewmp can generate the corresponding iptc and/or xmp keywords, if the export options (parameters>metadata) have been activated.

Afterwards with xnviewmp, you can force this writing in various ways, (having activated the export options):
-select photos > View> Update files from catalog (you can use "View > View files in subfolders" to save time)
-select a category in the catalog filter, select all the images obtained, uncheck/check slowly the category. De-select photos. Repeat for each existing category.

Exiftool reading syntax for these metadata is : Iptc:Keywords and Xmp-dc:Subject.
For writing use, as these metadata are "list" type, i dont know how to write them with a csv file ...
If there is only one category for each photo, use Iptc:Keywords (or Xmp-dc:Subject) as header in the csv file
Is there more than one category per photo in your set ?

On MacOs and linux, use single quotes instead of double, useful when you path/filename contains spaces.
exiftool -csv='yourfile.csv'
You can also add -ext jpg to process jpg files only

Code: Select all

exiftool -k -ext jpg -csv='yourfile.csv' 'c:/Test/ouputfolder/'

Or maybe just invoke exiftool on the commandline/Terminal with those arguments should work also?

Yes !

[Glorious1]

Thank you Michel. Yes, I have only one Category per photo. I can't get it to work.

In Terminal, I cd into the directory that contains my csv ("exiftool.csv") and the destination directory ("exifslides/").

The csv looks like this:

Code: Select all

SourceFile,IPTC:Caption-Abstract,IPTC:Keywords,XMP-dc:Description,XMP-dc:Subject
'/Users/jim/Documents/family_history/slides/Slide 01.jpg',"My caption",1960-1965,"My caption",1960-1965

I enter the command. No matter what I try, the result is always the same:

Code: Select all

jim@JimsMBPro ~/Documents/family_history: /Applications/XnViewMP.app/Contents/Resources/AddOn/exiftool -k -csv="exiftool.csv" "exifslides/"

    1 directories scanned
    0 image files read
-- press any key --

I know exiftool is finding the csv file, because if I name it wrongly in the command, I get an error indicating it is not found. I also am sure the csv has the correct path and filename of the files. Changing double to single quotes in the command arguments doesn't fix it. I can't figure out what the problem is. Asking for maximum verbosity (-v5) adds no information.

[michel038]

Sorry, I didn't explain the use of quotation marks very well...
Perhaps try this

csv file without quotes :

Code: Select all

SourceFile,IPTC:Caption-Abstract,IPTC:Keywords,XMP-dc:Description,XMP-dc:Subject
/Users/jim/Documents/family_history/slides/Slide 01.jpg,My caption,1960-1965,My caption,1960-1965

and the command (I'm not sure about single quotes, maybe double quotes will work ? .. )

Code: Select all

/Applications/XnViewMP.app/Contents/Resources/AddOn/exiftool -k -csv='exiftool.csv' 'exifslides/'

[Glorious1]

csv file without quotes :

Code: Select all

SourceFile,IPTC:Caption-Abstract,IPTC:Keywords,XMP-dc:Description,XMP-dc:Subject
/Users/jim/Documents/family_history/slides/Slide 01.jpg,My caption,1960-1965,My caption,1960-1965

Thank you. A problem with that is that many of the actual captions contain commas, so they wouldn't work without quotes. I saw that exiftool has a way to use a different delimiter. I'll see if I can export from Excel with a unique delimiter.

[Glorious1]

On the exiftool forum I discovered the error of my ways. I thought that the SourceFile column in the csv tells exiftool what files to process and where they are. No - you need to tell exiftool on the commandline where to look for source files, then it checks it against the SourceFile list. Not very intuitive, since that information is already in the SourceFile column.

I intended 'exifslides' to be the destination for the output files, but because I didn't use -out (-o), exiftool thought that's where the sourcefiles were. So now this works fine:

Code: Select all

exiftool -v2 -csv='exiftool.csv' 'test/' -out 'exifslides/'

-v2 gives a good level of verbosity
-csv= of course designates the csv file
'test/' is where exiftool should actually look for source files
-out in this case designates a directory for output files (so it doesn't rename the source files).

Thanks for your help Michel.

[michel038]

Thank you for the feedback
My post 2024/09/16 contained errors, I understood through your response, and I corrected it.
We need to specify the folder in which the photos to be updated are stored.

I read the exiftool documentation ...
https://exiftool.org/exiftool_pod.html#csv-CSVFILE

A problem with that is that many of the actual captions contain commas

If your captions contain commas, you can use another delimiter in the csv file
"The -csvDelim option may be used to change the input/output field delimiter if something other than a comma is required."
ex -csvDelim ; or -csvDelim "\t" (Tab used as a delimiter) .
"Use "\t", "\n", "\r" and "\\" to represent TAB, LF, CR and '\' respectively"

I can also answer to my own questions

For writing use, as these metadata are "list" type, i dont know how to write them with a csv file ...

"List-type tags are stored as simple strings in a CSV file, but the -sep option may be used to split them back into separate items when importing."
"Special feature: -csv+=CSVFILE may be used to add items to existing lists. This affects only list-type tags. "