Import Excel - Photo Captions

[webowner]

Hi Phil,

It's been a long time, but I hope you remember me... Owner of web business, and Professional Photographer.

I have used your ExifTool system in the past to export thousands of photo captions, and now I have them in a Microsoft Excel file.

Now, I am in desperate need to IMPORT these photo captions back onto my photos using either Adobe Lightroom or Zoner Photo Studio (Have you heard of Zoner?)

How exactly do I do this?

The Excel sheet is broken down into columns using these fields:
00450004.JPG       Bow River looking towards downtown Calgary     00450004.JPG   2008-11-13 14:55

Column 1 - Image name
Column 2 - Photo Caption
Column 3 - Image Name
Column 4 - Date

Please let me know - I really need this to work, and would be super grateful if you can guide me in the right direction.

Thank you so much. Hope to hear from you ASAP.

[Phil Harvey]

I can't help with Lightroom or Zoner Studio, but read FAQ 26 if you want to use ExifTool to write the information from your Excel sheet into your images.

- Phil

[Gerhart]

Today I started to explore the possibilities of ExifTool for my photo business.
I have a similar issue with metadata to be written from an excel sheet.

In my case the excel sheet contains the filename the name of the client and some other data.
I want the client name to be written to the metadata as an keyword.

I work in windows 7 so this wouldn't work on a mac I think.

I started with making a function in excel vba.
Therefore you have to open excel and enter the vba editor
In the right column you right click to create a new function.
I came this far (I used dutch words for the string names):

Function ExifRenameString(Vnaam, TV, Anaam, Groep, Fotonr As String) As String
Dim ExifString, Naam As String
ExifString = "exiftool -keywords+="
If TV = "" Then Naam = Vnaam & " " & Anaam
If TV <> "" Then Naam = Vnaam & " " & TV & " " & Anaam
ExifRenameString = ExifString & Chr$(34) & Naam & Chr$(34) & " -overwrite_original " & Groep & "\" & Fotonr & ".jpg"
End Function

I used the Chr$(34) for " otherwise it wouldn't work.
I added the 'Groep' String because my photo's  are in subdirectories and this field contains the directory names.
- Vnaam contains the Firstname
- TV contains the Middlename
- Anaam contains the Lastname
- Fotonr contains the filename of the photo
You have to activate the function in Excel. (you'll find it under options or search google)

Now, if you open your worksheet.
In an empty column you insert the function (formula --> insert function)
Your new function should be visible in the user defined category.
- Select it and choose the columns you need
- DoubleClick the square on the right bottom corner to the formula copy to every row with data
- Right Click and copy the all the formula's
- Paste the data in a document from wich you can create a batch file (I use notepad on windows)
- Save the document as a batch file (put the name between " with the extention .bat)
- Run the .bat file in the directory with the photos (be sure ExifTool.exe is in the same directory)

There is one glitch. I have tried different characterset options but charaters like é, ü, í etc. are not written correctly to the metadata. So I'm still working on it.

I hope I could be of any help.
Can you share your solution if you find an other one?

Greetings Gerhard

[Phil Harvey]

Thanks for the post Gerhard, but it should be a lot simpler than that.  All you need to do is export a spreadsheet from Excel in CSV format with the proper column headings then use -exiftool -csv=INPUT.csv DIR to write this information to your images.

And FAQ number 10 may shed some light on your character encoding problems.

- Phil

[Gerhart]

Thanks Phil,
Like I said, I'm new to Exiftool and am looking at it with the(old) knowledge I have.

Time to take a new approach.
Gerhard

[Gerhart]

Btw,
Once you created the function in excel you can use it every time, so it will be less work.

I still wander how exiftool will know which keyword to write to witch file.
So I have some studying to do tomorrow.

I have looked at FAQ number 10 earlier today but I couldn't solve the problem yet.

Goodnight.

[Phil Harvey]

I still wander how exiftool will know which keyword to write to witch file.

The CSV file has the tag names as column headers, and the file names as row headers.  Read FAQ 26 as I mentioned earlier to see how this works.

- Phil

[Gerhart]

Phil, today I found this tool.
http://www.photographers-toolbox.com/products/lrtransporter.php

I tested it with lightroom 5.4 and it's easy to use and works good.
You have to donate to edit more then 5 records at once.

[Phil Harvey]

Great.

I thought for a minute that you had found a metadata plug-in that didn't use ExifTool to do the heavy lifting.  I couldn't find any mention of ExifTool on the web site.  But I downloaded it to take a look, and sure enough, there is exiftool inside the plugin directory.

- Phil

[webowner]

Hi Phil,

I sure appreciate your Exiftool to possibly import my thousand of photo captions onto my images.... So, I have installed the exiftool cmd on Win8 and am using the line from your FAQ line 26, as mentioned in my earlier post (from May 2014).

However, the problem with importing my information - is exiftool does not like the first column - indicating it is not a Tag?

I have no idea what fields / column headings I should use -

# import from CSV file
exiftool -csv="c:\Users\Karen\photocaptionsGood-list.csv" "c:\Users\Karen\gallery"


** Can you please explain EXACTLY what my 4 column headings should be?

This is what I have so far:
00450004.JPG   Bow River looking towards downtown Calgary   00450004.JPG   2008-11-13 14:55

AND

** I have the photos separated into folders (i.e. gallery/alberta     gallery/europe/scotland   etc.)

Will the photo captions as indicated above - (without the sub folder) automatically write to the correct pictures, in the folder?

Please explain what I am doing wrong. I really need this to work asap.

Thank you so much. Looking forward to hearing from you. I have attached my photocaption.csv file for your review. Can you please kindly make any adjustments as needed to this file - so I can import all of my captions?

Thank you so much. Avid Professional Photographer here, and owner of web business in Canada....

Specifically, the first row of the CSV file must contain the tag names. The first column must be a special "SourceFile" column, containing the names of the associated image files, with paths specified in the same way as on the command line. The JSON file contains similar entries, but is not structured in row/column format.
For each image file specified on the command line, all tags for the database entry with the corresponding SourceFile name are written (with the exception of the FileName and Directory tags, which are ignored). A special SourceFile name of "*" may be used to match any image file. A warning is issued and nothing is written if a specified file does not match any SourceFile entry in the database.
Tag names may be prefixed by a group name to write to a specific group (using the same format as when -G or -G1 is added to the export command). Tags with empty values are ignored. To delete a tag, set the tag value to "-" and use the -f option when importing.

[Hayo Baan]

Hi Webowner,

From the exiftool manual:

Export information in CSV format, or import information if CSVFILE is specified. When importing, the CSV file must be in exactly the same format as the exported file. The first row of the CSVFILE must be the ExifTool tag names (with optional group names) for each column of the file, and values must be separated by commas. A special ''SourceFile'' column specifies the files associated with each row of information (a SourceFile of ''*'' may be used to match any source file name).

So basically what's missing from your csv is telling ExifTool exactly what each column means. In your case all you should need to do is simply add a first line with the names of the tags the content should be put into:
SourceFile,Description,FileName,...
Note that there are a couple of things to take into account here:

• Information could need to go to multiple tags. Your caption for instance could also need to go to ImageDescription (EXIF), Caption-Abstract (IPTC), Description is the XMP tag.
• What date time do you mean? There are very many tags (in many groups) that could hold a date (e.g., DateTimeOriginal, DateCreated, DateTimeDigitized, CreateDate, etc.). Normally, however, you should not need to set the creation date of the file as that should normally already have been set correctly.

Hope this helps...

Cheers,
Hayo

[webowner]

Hello,

Thank you for the help, however I am still confused.

What or How "exactly" do I identify fields / or rows in excel's csv file for "SourceFile" ?

If you can have a look, please at my csv file attached - can you make changes to it, and let me know?

I am having difficulty identifying each column - what exact format for headings or content do I need?

Thank you for the help. I really need this to work, but not at all sure what fields/headings / content it should be.

Can someone please take a look at my csv file attached, kindly please - and let me know ?

[Phil Harvey]

Hi Karen,

Take a file that already contains the information you want, and run this command on it:

exiftool -s FILE  (where FILE is the name of the file)

The tag names will appear in the output.  These are what you need as the labels in the first row of your CSV file.  You need to figure out where you want the information to go (ie. what tag names to put in the first row).  I can't do this for you.

- Phil

[webowner]

Hi Phil,

I really appreciate your help, and many thanks for getting back to me.

I did exactly what you said - however it returned an Error

"Unknown File Type"

Strange I has it in .csv format as indicated.

So, I cannot display or produce any tags.... and therefore cannot import my photo captions.

Any other ideas? I really want to use your system and get this to work.

Thankyou.

[Phil Harvey]

I wasn't specific enough.  FILE is the name of an image file containing the information.  This is essentially FAQ 2.  Also, I suggest reading FAQ 26.

- Phil