photo_metadata_to_csv.rb

#!/usr/bin/env ruby
# frozen_string_literal: true

# Lists photos in a folder, reads metadata via ExifTool (supports HEIC), writes CSV.
# Requires: exiftool in PATH (brew install exiftool)

require 'csv'
require 'json'
require 'open3'
require 'optparse'
require 'pathname'

IMAGE_EXTENSIONS = %w[
  .heic .jpg .jpeg .jpe .png .tif .tiff .webp
].freeze

# Non-GPS tags; all GPS IFD tags are requested separately via -gps:all.
EXIFTOOL_TAGS = %w[
  DateTimeOriginal CreateDate
  OffsetTime OffsetTimeOriginal OffsetTimeDigitized
  SubSecTime SubSecTimeOriginal SubSecTimeDigitized
  Software FileModifyDate ModifyDate
  CreatorTool ProcessingSoftware
  ExposureTime FNumber ISO ExposureCompensation MeteringMode ExposureMode BrightnessValue
  WhiteBalance ColorSpace ProfileDescription
  CompositeImage HDRHeadroom HDRGain HDRGainMapHeadroom HDRGainMapVersion
  Orientation FocalLength FocalLengthIn35mmFormat LensMake LensModel DigitalZoomRatio Flash
].freeze

# Column order after core fields + Modified (tags use exiftool -j keys; -n numeric where applicable).
EXTRA_COLUMNS = %w[
  ExposureTime FNumber ISO ExposureCompensation MeteringMode ExposureMode BrightnessValue
  WhiteBalance ColorSpace ProfileDescription
  CompositeImage HDRHeadroom HDRGain HDRGainMapHeadroom HDRGainMapVersion
  Orientation FocalLength FocalLengthIn35mmFormat LensMake LensModel DigitalZoomRatio Flash
].freeze

# Substrings in Software that suggest an editor/export (not in-camera OS strings).
EDIT_SOFTWARE_PATTERN = /photoshop|lightroom|affinity|pixelmator|gimp|darktable|capture\s*one|dxo|luminar|snapseed|vsco|acetone|preview\.app/i.freeze

def find_photos(folder, recursive:)
  root = Pathname(folder).expand_path
  pattern = recursive ? root.join('**', '*') : root.join('*')
  Dir.glob(pattern.to_s, File::FNM_SYSCASE).select do |path|
    File.file?(path) && IMAGE_EXTENSIONS.include?(File.extname(path).downcase)
  end.sort
end

def exiftool_json(files)
  return [] if files.empty?

  args = ['exiftool', '-j', '-n', '-charset', 'UTF8'] +
         EXIFTOOL_TAGS.flat_map { |t| ["-#{t}"] } +
         ['-gps:all'] +
         files

  stdout, stderr, status = Open3.capture3(*args)
  unless status.success?
    warn "exiftool failed (#{status.exitstatus}): #{stderr.strip}"
    return []
  end

  JSON.parse(stdout)
rescue JSON::ParserError => e
  warn "Failed to parse exiftool JSON: #{e.message}"
  []
end

def csv_cell(value)
  case value
  when nil then ''
  when Array then value.map { |x| x.is_a?(Array) ? x.join(' ') : x.to_s }.join('; ')
  else value.to_s
  end
end

# Heuristic: non-empty ProcessingSoftware; known editor in Software; EXIF ModifyDate != capture time.
def modified_flag(row)
  return 'true' if row['ProcessingSoftware'].to_s.strip != ''

  sw = row['Software'].to_s
  return 'true' if sw =~ EDIT_SOFTWARE_PATTERN

  orig = row['DateTimeOriginal'].to_s.strip
  orig = row['CreateDate'].to_s.strip if orig.empty?
  mod = row['ModifyDate'].to_s.strip
  return 'false' if orig.empty? || mod.empty?

  normalize_exif_datetime(orig) == normalize_exif_datetime(mod) ? 'false' : 'true'
end

def normalize_exif_datetime(s)
  t = s.to_s.strip
  return t if t.empty?

  t = t.sub(/\A(\d{4}):(\d{2}):(\d{2})\s+(\d{2}:\d{2}:\d{2}).*/, '\1-\2-\3 \4')
  t.sub(/[Z+-].*\z/, '').strip
end

# EXIF-style base time "2026:03:20 16:55:44" + optional ".746" + optional "+00:00" (matches exiftool -CreateDate style).
def date_and_zoned_datetime(row)
  using_original = row['DateTimeOriginal'].to_s.strip != ''
  base = if using_original
           row['DateTimeOriginal']
         else
           row['CreateDate']
         end
  base = base.to_s.strip
  return ['', ''] if base.empty?

  date_part = nil
  if base =~ /^(\d{4}):(\d{2}):(\d{2})\s+(\d{2}:\d{2}:\d{2})$/
    date_part = "#{$1}-#{$2}-#{$3}"
  end

  subsec =
    if using_original
      row['SubSecTimeOriginal'] || row['SubSecTime']
    else
      row['SubSecTime'] || row['SubSecTimeDigitized']
    end
  subsec = subsec.to_s.strip
  subsec = '' if subsec.empty?

  offset =
    if using_original
      row['OffsetTimeOriginal'] || row['OffsetTime'] || row['OffsetTimeDigitized']
    else
      row['OffsetTime'] || row['OffsetTimeDigitized'] || row['OffsetTimeOriginal']
    end
  offset = offset.to_s.strip
  offset = '' if offset.empty?

  fraction = subsec.empty? ? '' : ".#{subsec}"
  zone = offset.empty? ? '' : offset

  datetime = "#{base}#{fraction}#{zone}"
  [date_part || '', datetime]
end

options = { folder: '.', recursive: false, output: nil, stdout: false }

parser = OptionParser.new do |opts|
  opts.banner = "Usage: #{$PROGRAM_NAME} [options] [FOLDER]\n\n" \
                "Export photo metadata to CSV using exiftool. Default folder is the current directory.\n" \
                "Default output: photo_metadata.csv inside FOLDER (use -o to override, --stdout for terminal)."

  opts.on('-r', '--recursive', 'Include subfolders') { options[:recursive] = true }
  opts.on('-o', '--output PATH', 'Write CSV to PATH') { |p| options[:output] = p }
  opts.on('--stdout', 'Print CSV to terminal instead of a file') { options[:stdout] = true }
  opts.on('-h', '--help', 'Show this help') do
    puts opts
    exit
  end
end

parser.parse!

options[:folder] = ARGV[0] if ARGV[0]

unless system('command -v exiftool >/dev/null 2>&1')
  warn 'exiftool not found. Install with: brew install exiftool'
  exit 1
end

folder = options[:folder]
files = find_photos(folder, recursive: options[:recursive])

metas = exiftool_json(files)
gps_keys = metas.flat_map(&:keys).grep(/^GPS/).uniq.sort

rows = metas.map do |meta|
  path = meta['SourceFile'].to_s
  next if path.empty?

  date, datetime = date_and_zoned_datetime(meta)
  name = File.basename(path)
  modified = modified_flag(meta)

  extras = EXTRA_COLUMNS.map { |k| csv_cell(meta[k]) }
  gps_vals = gps_keys.map { |k| csv_cell(meta[k]) }

  [date, datetime, name, modified, *extras, *gps_vals]
end.compact

headers = [
  'Date', 'Datetime', 'Photo name', 'Modified',
  *EXTRA_COLUMNS,
  *gps_keys
]

output_path =
  if options[:stdout]
    nil
  elsif options[:output]
    Pathname(options[:output]).expand_path
  else
    Pathname(folder).expand_path.join('photo_metadata.csv')
  end

out = output_path ? File.open(output_path.to_s, 'w:UTF-8') : $stdout
begin
  out.write("\uFEFF") if output_path # BOM helps Excel on Windows
  out.write(CSV.generate_line(headers))
  rows.each { |r| out.write(CSV.generate_line(r)) }
ensure
  out.close if output_path
end

if output_path
  warn "Wrote #{rows.size} row(s) from #{files.size} photo(s) to #{output_path}."
else
  warn "Wrote #{rows.size} row(s) from #{files.size} photo(s) to stdout."
end