Bridge Keywords to Excel Spreadsheet

[brie]

I know absolutely nothing about coding, so learning to use exiftool has been an adventure! I'm trying to export keywords I've applied to camera trap photos in Bridge to an Excel spreadsheet. Currently, I've had some success in listing the keywords along with their filename by creating a csv and importing it into excel so multiple keywords could be separated into different columns.

exiftool -Filename -Keywords -m -sort -csv  100EK113 > test4.csv
When I import the csv file produced by this code, I get a spreadsheet that looks like the excerpt I've attached. Notice how the keyword lists align left so that the columns are not keyword-specific. Because there are over 280k photos and a significant percentage of them are tagged with multiple keywords, it would be rather impractical to try organizing the keywords manually so that each has its own column.

In Bridge, the keywords were hierarchical, and I was wondering if there was a way to preserve this when placing the data in a spreadsheet (for example, photos with a Grevy's Zebra would be tagged with the series of keywords Wildlife>Zebra>Grevys)...Could I make specific data columns for tags of a certain hierarchical level (like a Wildlife column that shows whether the image is tagged Zebra or Giraffe)? Or, at the very least, create placeholders where a keyword is not applied so each keyword would have its own column in the spreadsheet. Is a csv the best way to do this, or would I have more success with a different method?

I'm sorry if this has already been answered or if I'm overestimating the capabilities of exiftool, but I appreciate any guidance I could get! This forum has already been very helpful in allowing me to figure out how to use exiftool at all. Please let me know if any clarification would be helpful.

Thanks,
Brie

[StarGeek]

The problem is that Exiftool only processes one file at a time and will only know the keywords that are in the current file.  For example, from your screenshot, when it hits 02050313.jpg, Exiftool will only know about the Night, Review, Timeframe, and Wildlife keywords.  It can't create a column for Day, Large Herbivore, Series, Grevys, or Humans.  It technically could be possible to do something if you have a list of all the keywords ahead of time, but that could be complicated.

If you did have a full list ahead of time, I think it would be easier to deal with in the spreadsheet.  Keep the keywords in a single cell rather than split them and then then you could do a lookup to match columns to the data.

[StarGeek]

I forgot to address the hierarchy aspect of your question.  Bridge probably saves the hierarchy in the XMP:HierarchicalSubject tag.  The format of that tag lists the each part of the hierarchy separated by the pipe | symbol.  Using your example, the tag with Grevys would be Wildlife|Zebra|Grevys. 

But this runs into the same problem, Exiftool processes only one file at a time and won't know about other HierarchicalSubjects.

After further thought on this, I believe I could make you a user defined tag that you could edit to add your full list of keywords and would separate the keywords into columns.

[brie]

Thank you so much! I'm not quite sure I understand user-defined tags, but I'd appreciate being able to keep my keywords how I marked them.

In response to your earlier post, if still relevant, I do actually have a full keyword list saved (it's pretty hefty when all the subspecies are factored in), could you explain how I would use the lookup function on such a large data set (or direct me towards a resource for dummies)?

I really appreciate the help.

[StarGeek]

Just to verify I'm understanding you, you would want a column for each keyword?  Something like this?

Filename	Large Herbivore	Zebra	Series	Wildlife	Timeframe	Review	Night	Humans	Day
File1	Large Herbivore			Wildlife
File2		Zebra
File3	Large Herbivore			Wildlife
File4		Zebra		Wildlife
File5		Zebra

[StarGeek]

I have a few more tweaks to make, but here's some example output.  This is based upon the following keywords: "Review, Series, Humans, Wildlife, Large Herbivore, Grevys, Zebra, Timeframe, Day, Night" and uses a dash to indicate which keywords are not in the file.  Red herring, since it's not on the list, is ignored.

Code Select Expand

c:\>exiftool -config C:\Programs\UnixUtils\BriesKeywords_Config.txt -keywords -BriesKeywords X:\!temp\Test3.jpg
Keywords                        : Night, Review, Timeframe, Wildlife, Red herring
Bries Keywords                  : Review, -, -, Wildlife, -, -, -, Timeframe, -, Night

c:\>exiftool -config C:\Programs\UnixUtils\BriesKeywords_Config.txt -csv -BriesKeywords X:\!temp\Test3.jpg
SourceFile,BriesKeywords
X:/!temp/Test3.jpg,"Review, -, -, Wildlife, -, -, -, Timeframe, -, Night"

[brie]

Yes, that example table is perfect. Do I need to list all of my keywords in a .txt for exiftool to read? Also, I forgot to mention that I'm working on a mac - sorry if that changes anything.

[brie]

Okay, I've done a little looking and from what I've gathered I should make a config file, which I'm still not exactly sure how to format (but I'm looking over the guide and forums). Will I be able to use this method on an entire folder of photos to make a csv file?

[StarGeek]

Try the config file attached to this post.  Your command has to start like this: exiftool -config /path/to/BriesKeywords_Config.txt  After that you can put the rest of your command.  In the place of -keywords you will use -BriesKeywords.

You'll have to edit the file a bit before you actually use it.  Open up BriesKeywords_Config.txt in a text editor like Notepad, not in a word processor.  Look for the area between the dash lines.  Right now it looks like this:

Code Select Expand

#--------------------------------
# Non-matching cells will be replaced by this character, change it to what you like
my $EmptyCellChar="-"; 

# Place your keyword list between each of the quotes
# Sort them in the order you want the columns to be
my $TempString =
"
Review
Series
Humans
Wildlife
Large Herbivore
Grevys
Zebra
Timeframe
Day
Night
";
#--------------------------------

You will want to place your keywords between the two quotes that are on the lines by themselves, one keyword per line.  Also, you can change what is printed when a keyword isn't matched by changing the dash in the line my $EmptyCellChar="-";.

Right now, it is case sensitive, so if your keyword is "zebra", "zEBra" or other variation, it won't match.  That can be fixed if it needs to be.  If you want to change the name from BriesKeywords to something else, look for the line that says BriesKeywords => { and change it there. 

Let me know if there are any problems.

[brie]

I tried saving the file on the desktop, the directory I'm working in, and in ExifTool's config_files folder, and in every location I still got the "config file not found" error (even after restarting Terminal). Where should it go? 

[StarGeek]

It can go anywhere, you just have to supply the full path to it.  For example, on the desktop on Windows 10, it would be exiftool -config C:\Users\*USER*\Desktop\BriesKeywords_Config.txt  where you would replace *USER* with the actual user name.   Actually, if you put the file on the desktop, you can probably use this at the start of the command:
exiftool -config %userprofile%\Desktop\BriesKeywords_Config.txt 

[brie]

Oh, of course.  I was so excited to test it out that I got ahead of myself. But it worked! Thank you SO much; the reduction in manual data entry will have a big impact on wildlife research. I hope I have your permission to include this code in the procedure outline for the project.

One final question: if all of my images are stored within subfolders, would I be able to run the parent folder through exiftool and have it read the images in the subfolders, or will I need to run the process manually on every folder?

[Phil Harvey]

If you give exiftool a directory name, then the -r option can be used to recursively process all subdirectories.  In this case you might want to also add -ext jpg to process only JPEG files.

- Phil

[StarGeek]

I hope I have your permission to include this code in the procedure outline for the project.

Consider it public domain.  Do with it as you will.

[brie]

Wonderful, thank you both so much for your responsiveness and assistance. I spent months tagging all of our photos and feared that I had wasted my time when I struggled to export the keywords. It can be so daunting for us beginners...I couldn't have done it without you!

[stacyje]

Hi all,

I am an ecologist so very new to this type of stuff so I apologize in advance for my lack of understanding. I am using the config file attached above but I keep receiving this error message (see attached image).  I have tried to adjust the data and cannot seem to get it right. Any help would be really appreciated!!

[StarGeek]

Are the files you want to use on G:?  Because CD G: isn't enough to get you to that drive.  You can try CD /D G:  or just type G:.  The first one you can use the full path including subdirectories if you want, the second will just change to that drive.

Then, to use the config file, you need to input the full path to the file.  If it is on the top level of G:, then you almost had it right with the third attempt, you just need to remove the slash after config.

Also, if you can locate the file in Windows, depending upon your version of Windows, you can copy the path to the clipboard by holding down the shift button, right clicking on the file, and then selecting "Copy as path".  You can then paste it into the command line.  Dragging the file onto the command line may also paste the filename.

[stacyje]

Thank you!

I was able to get one step closer!

Yes, all my files are located on the G drive. I was able to direct to the G drive thanks to your help but I am receiving an error message that the file with my images cannot be found. I have attached an image of the command window and my folder that I am directing to. 

Within this parent folder are tons of subfolders. I used the -r as suggested above to have the command read all parent and subfolders. Is this correct?

Thank you so much for your help.

[StarGeek]

Your directory has spaces in it, but you're not including those spaces in your path name.  In order for windows to realize that the path has spaces in it, you enclose it in double quotes:
"Namunyak CT Images June to Sep 2016 Unclassified"

Also, you can copy the full path to the clipboard with the SHIFT+Right Click trick I mentioned earlier.  That would allow you to paste the full path properly without having to worry about typos.

Also, remember, for this particular config file to work, you have to edit the config file to include a list of all the keywords you're looking for as mentioned in this post.  This config was created for a very particular situation and may not be applicable to your use.

[stacyje]

Thank you!

I am actually using the same data set Brie (the above user) was using. She classified camera trap images for one conservation site and I went through and classified images for the second conservation site. I am trying to use her protocol, however for a newbie and someone who is completely lost I was having a bit of a hard time with it then I found her thread on this site.

I am one step closer but of course one more, hopefully the last question....

That seemed to do the trick but now I am getting another error message....sorry I do apologize for really not knowing at all what I am doing. I really appreciate all of your help.

[StarGeek]

I am actually using the same data set Brie (the above user) was using.

Nevermind then

Error: File format error means that the image is probably corrupt.  They don't look like any file format that exiftool understands.  Odds are that you won't be able to view those images.