extracting date and time into two cells and removing file extension

[fototastic]

Hi Phil,

Hope you can help. I am exporting metadata from a batch of several thousand images to a table / spreadsheet in order to create captions. This has worked really well thus far. I've been using the following command line in Terminal (I'm on a Mac) to export the filename, GPS latitude, GPS longitude, datetimeoriginal.

exiftool -filename -gpslatitude -gpslongitude -DateTimeOriginal -d '%d/%m/%Y %H:%M' -T -r /Volumes/WD\ 2TB\ Drive/images > metadata_output.txt

However, I want to:

a) export the filename without the file extension (.jpg etc)
and also
b) I want to separate the date (in the format DD/MM/YYYY) from the time (in the format HH:MM) so each will be in a separate cell. I can't work out how to do either of these things.

As I'm a newbie at this, and I'm a little stuck!

EXIFTool is brilliant BTW! Thank you Phil

[StarGeek]

a) export the filename without the file extension (.jpg etc)

If you're using version 12.22 or higher, you can use the BaseName tag, otherwise you would use add the Basename tag from the example.config file to your .exiftool_config file or use ${filename;s/\.[^\.]+$/ /} in the option below.

b) I want to separate the date (in the format DD/MM/YYYY) from the time (in the format HH:MM) so each will be in a separate cell. I can't work out how to do either of these things.

That would be quite a bit trickier.  You can't have multiple -d (-dateFormat) options.  One way to do it would be to do it as post processing in the spreadsheet program.  Spreadsheets aren't my area of expertise, but I'm pretty sure there's a way to split a column into two, separating on the space character.

Otherwise, you would have to roll your own format using the -p (-printFormat) option.  See FAQ #12, the section starting "Finally, the -p option".  Your -p option would be along these lines (replacing <tab> with an actual tab)
exiftool -p '#[HEAD]Filename<tab>GPSLatitude<tab>GPSLongitude<tab>Date<tab>Time' -p '$Filename<tab>$GPSLatitude<tab>$GPSLongitude<tab>${DateTimeOriginal;DateFmt("%d/%m/%Y")}<tab>${DateTimeOriginal;DateFmt("%H:%M")}' -r /Volumes/WD\ 2TB\ Drive/images > metadata_output.txt

I wouldn't think you would have to add the -api filter option as indicated in the FAQ, as none of those tags should have the special characters that would mess up the formatting.

[fototastic]

Thanks so much Stargeek!

I wasn't aware of the Basename tag. Tried it, it works!

Thanks so much for the rest of your solution. I didn't get it to work unfortunately as I think its getting slightly beyond my own comprehension!  This really isn't my area of expertise at all......However, I used the following in terminal:

exiftool -basename -gpslatitude -gpslongitude -DateTimeOriginal -d '%d/%m/%Y %H:%M' -T -r /Volumes/WD\ 2TB\ Drive/ images > metadata_output.txt

It still results in the Date and time within the same cell, when I open it in Excel. But, as per your suggestion, I worked out the way to split the column at the space so they are now in 2 separate cells as I need them to be. So that's a huge result 

Thankyou so much - I wish I understood more about how to use EXIFtool properly !!

[Hubert]

In Terminal on a Mac, one horribly down and dirty one-line way is to pipe the output (not the file) from the ExifTool command through sed (stream editor) to perform two replacements and then write the text file.

I'm sure there are more elegant ways of doing this, but it works for me:

Code Select Expand

exiftool -filename -gpslatitude -gpslongitude -DateTimeOriginal -d '%d/%m/%Y  %H:%M' -T -r /Volumes/WD\ 2TB\ Drive/images | sed 's/.jpg//I' | sed 's/\(\/[0-9]\{4\}\)  /\1 /' > metadata_output.txt

This uses sed twice. First, it removes the character string .jpg (case insensitive) from each line of the output. It then takes the output from that replacement and in each line it looks for a pattern of a forward slash followed by any four digits, the year followed by two spaces (present in the original ExifTool date format). It buffers the year pattern only (not the spaces) using the escaped parentheses and then inserts the year pattern (eg /2021) followed by the extra tab character as a separator.

One slightly awkward bit is getting the tab character into the command line. It's the long gap after the \1 replacement (the buffered year pattern) in the code above. In Terminal you need to type ctrl+v followed by a tab. You can also copy and paste a previously typed tab character from a text editor.

I get an output file like this:

P1003357   52 deg 1' 22.61" N   1 deg 21' 40.30" E   22/04/2021   13:17
P1003353   52 deg 1' 24.17" N   1 deg 21' 41.06" E   22/04/2021   13:14
P1003347   52 deg 5' 34.06" N   1 deg 20' 28.57" E   22/04/2021   11:26

EDIT: it would need further tweaking to find any file extension, not just ".jpg".

[StarGeek]

First, it removes the character string .jpg (case insensitive) from each line of the output.

Since fototastic is using a version that has Basename available, this first part wouldn't be needed.

[Hubert]

First, it removes the character string .jpg (case insensitive) from each line of the output.

Since fototastic is using a version that has Basename available, this first part wouldn't be needed.

True.