Creating CSV for spread sheets such as Excel

[Pingo]

From a directory containing jpegs I want to create a table containing the entire EXIF information of one picture per line.
The table should contain a head line containing the respective tag names of each column.
I think a CSV file output from the ExifTool would be an appropriate solution to import into spreadsheets.
Unfortunately I have not been successful in creating such an output, particularly with the head line.
Does anybody have a solution ready or can help otherwise? (I am using WIN7)

Pingo

[Phil Harvey]

It's a nice idea in theory, but not possible in practice because the possible number of tags is far too large, and in fact it isn't even bounded.  (ExifTool has over 8000 tags it recognizes, but it also extracts unrecognized tags from various types of meta information.)

But you can do this easily with a pre-determined set of tags, using either the -T option for tab delimited output, or the -p option for any other format (ie. comma delimited).

- Phil

[Pingo]

It's a nice idea in theory, but not possible in practice because the possible number of tags is far too large, and in fact it isn't even bounded.  (ExifTool has over 8000 tags it recognizes, but it also extracts unrecognized tags from various types of meta information.)

But you can do this easily with a pre-determined set of tags, using either the -T option for tab delimited output, or the -p option for any other format (ie. comma delimited).

- Phil

Hi, Phil, tks for your fast answer. Of course, I will only need a limited number of tags (the foto-relevant ones). I also tried the -T option, and the import to Excel works fine. But how do I create a headline for the table with the respective names of the tags selected?

Pingo

[Phil Harvey]

There are a number of ways to generate the header line manually.  Here is a common way:

Code Select Expand

echo "FileName Artist UserComment" > t.out
exiftool -filename -artist -usercomment -T DIR >> t.out


Or, you could use the undocumented -echo option if you are a real Exiftool hacker:

Code Select Expand

exiftool -echo "FileName Artist UserComment" -filename -artist -usercomment -T DIR > t.out


(but it may be tricky to get tabs between the tag names on the command line with these techniques)

Or, if you are using the -p option you can use a format file like this:

Code Select Expand

#[head]FileName Artist UserComment
$filename $artist $usercomment


Here the tabs are easier to generate since you can use any plain text word processor.

- Phil

[Pingo]

Hi Phil,

The first solution works fine:

Here comes the content of my test batch file, may be it is useful for others.


echo FileName<tab key>ImageQuality<tab key>WhiteBalance > t.out
exiftool -filename -imagequality -whitebalance -k -T DIR  >> t.out


<tab key> means the tab key must be pushed.

The t.out file can than easily be imported into Excel spread sheet and a nice table is created.

Thanks again

Pingo

[Pingo]

Now I discovered the following problem when importing the CSV file into spreadsheets:

Some of the shutterspeeds (not all) are interpreted and shown as dates ( e.g 3 May) in the spreadsheets.

Is there a simple way to add the unit to the shutter speed (i.e. " s"), so that the tag content is output as text?

I think this would solve the problem.

Pingo

[Phil Harvey]

Hi Pingo,

I think some shutter speeds are being interpreted as dates because they are fractions.  To extract the computer-readable value for any given tag, add a "#" to the tag name.  ie) Using -ShutterSpeed# instead of -ShutterSpeed prints the value as a floating point number of seconds (no " s" as you suggested, but I don't think you will need this).

- Phil

[Pingo]

Thanks Phil, for the solution proposed. I am always amazed to see what is possible with your excellent work.

The proposed solution provides exposure time as a decimal figure. Still I would like to ask if there is a similar possibility to put out tag content in form of  t e x t  for the following two reasons:

- it is quite uncomfortable to interpret an exposure time of 0.003125 instead of 1/320.
- I am using Excel normally with the German area scheme (colon for the decimal point), which means that decimals with a point are interpreted as a date (applies e.g. to apperture). My work around is to switch Excel temporarily to the US scheme.

If there is no such identifier for text, how can I define user tags producing text?


Pingo

[Phil Harvey]

Hi Pingo,

Since this didn't do what you want, maybe it is time to go to the -p option, which will give you full control over formatting.  You can add quotes around a tag value or add " s" to the end or whatever you want.

The format file (my.fmt) will look something like this:

Code Select Expand

#[HEAD]FileName<tab>ImageQuality<tab>WhiteBalance<tab>ShutterSpeed
$filename<tab>$imagequality<tab>$whitebalance<tab>"$shutterspeed s"


And the command will be:

exiftool -k -p c:\my.fmt DIR  > t.out

- Phil

[Pingo]

Thanks, Phil,

the format file works fine and completely solves my Excel problem, but the new solution does not extract the immages taken by Nikon and Canon cameras any more, only for my Panasonic, whereas the former solutions did it for the other cameras also.

Sorry to bother you again!

Ingo

[Phil Harvey]

Hi Pingo,

I think this section of the -p documentation may help explain what is happening:

If a specified tag does not exist, a minor warning is issued and
            the line with the missing tag is not printed.  However, the -f
            option may be used to set the value of missing tags to '-', or the
            -m option may be used to ignore minor warnings and leave the miss-
            ing values empty.

- Phil

[Pingo]

Hi, Phil.

I can now create a nice Excel table by simply dragging the CSV file created with your help onto the first Excel field of an empty worksheet.

The content of the format file  (my01.fmt) I put into the ExifTool directory is as follows:

Code Select Expand

#[HEAD]FileName<tab>Directory<tab>CereateDate<tab>FileSize<tab>CameraModelName<tab>ImageQuality<tab>WhiteBalance<tab>ImageStabilization<tab>ShootingMode<tab>ColorEffect<tab>ContrastMode<tab>Contrast<tab>Sharpening<tab>Saturation<tab>NoiseReduction<tab>FocusMode<tab>AFAreamode<tab>ISO<tab>ExposureProgram<tab>ExposureTime<tab>FNumber<tab>FocalLength<tab>FocalLengthin35mm<tab>Flash
$filename<tab>$directory<tab>$createdate<tab>$filesize<tab>$model<tab>$imagequality<tab> $whitebalance<tab>$imagestabilization<tab>$shootingmode <tab>$coloreffect<tab>$contrastmode<tab>$contrast<tab>$sharpness<tab>$saturation<tab>$noisereduction<tab>$focusmode<tab>$AFAreaMode<tab>$iso<tab>$exposureprogram<tab>$exposuretime s<tab>F $FNumber<tab>$FocalLength<tab>$FocalLengthIn35mmFormat<tab>$Flash

(<tab> to be replaced by Tab key pressing)

The ExifTool command line is as follows:

exiftool -k -m -p my01.fmt DIR  > Photo.out

Thanks, Phil, for all your help.

Pingo

PH Edit: Changed .BAT file to a code block