Generate a CSV from select files' directory information

Question

I'm on a mac, and I'm afraid this command line problem has me stumped. At least I'm assuming the easiest way to do this would be through the CL.

I'd like to recursively search a directory tree for files within a specified set of extension (e.g. videos like .mov, .mkv, .flv,...). I'd then like to output a CSV format with the filename (path stripped off), and creation date on each line.

It sure seems like this should be doable from the command line, or at most a simple script, but I'm stumped.

Answer 1

MacOS tracks a file's creation time, but not many tools can work with it., and it's often not meaningful anyway. For most purposes, the modification time works fine, and it is supported by far more tools.

Note that all my solutions assume that no file name contain any of the characters ,, " or newline. Otherwise the output is ambiguous; to represent these characters in a CSV file, extra care is necessary. If your file names may contain commas, you may want to put double quotes around the file names, or use another character such as ; as the column separator.

Zsh, modification time

To enumerate the desired files, use the glob operator ** to traverse subdirectories and (…|…) to match multiple extensions. Use the glob qualifier . to restrict matches to regular files, N to not error out if there are no matches, and optionally D to include dot files. Optionally, use the globbing flag i to match the extensions case-insensitively.

To obtain the desired data, use the zstat builtin from the zsh/stat module. To print the file name without the directory part, use the history modifier t tacked to a parameter expansion.

zmodload -F zsh/stat b:zstat
for x in **/*.(#i)(mov|mkv|flv)(.N); do
  TZ=UTC zstat -A a -F '%Y-%m-%d %H:%M:%S' +mtime -- $x;
  print -lr -- "$x:t,$a[1]";
done

Zsh + /usr/bin/stat, creation time

With the system stat utility instead of zstat, you can list the file's creation time. Because stat has no option to strip off the directory part of the file name, I first switch to the directory containing the file.

for x in **/*.(#i)(mov|mkv|flv)(.N); do
  (cd $x:h && TZ=UTC stat -t '%Y-%m-%d %H:%M:%S' -f %N,%SB -- $x:t)
done

GNU find, modification time

With GNU find, you can use the -printf primary to present the output in the desired format. Use %f for the file name without the directory part and %T followed by a time format for the modification time. To match multiple extensions, -regex with -regextype posix-extended is convenient.

TZ=UTC gfind -type f -regextype posix-extended -regex '.*\.(mov|mkv|flv)' \
      -printf '%f,%TY-%Tm-%Td %TH:%TM:%TS\n'

/usr/bin/find + /usr/bin/stat, creation time

With the system find, the search part is similar to GNU find except for using the -E option instead of the -regextype posix-extended primary, but you need to work out the listing with shell code. The action -execdir conveniently lets you obtain the file's basename.

TZ=UTC find -E . -regex '.*\.(mov|mkv|flv)' -type f \
     -execdir sh -c 'for x do stat -t "%Y-%m-%d %H:%M:%S" -f %N,%SB -- "$x"; done' sh {} \;