Copy file names and directories to a spreadsheet?

Question

Level 1

51 points

Copy file names and directories to a spreadsheet?

I have a directory that contains several sub directories. Each sub directory contains many files.

EXAMPLE:

(The example here is shortened. The actual main directory has hundreds of subdirectories which each contain hundreds of files)

Is there anyway to put this data into a spreadsheet, so the file name is listed with it’s immediate parent directory

EXAMPLE:

I know I can just copy and paste file names from the Finder, but given the volume of files, I was hoping their might be an automated method. Thanks!

I am using Mac OSX 10.14.6 and Numbers version 6.2.1

Posted on Jan 13, 2020 3:05 AM

Reply

Answer 1

VikingOSX

Level 10

122,922 points

Jan 13, 2020 11:37 AM in response to big_smile

The simplest approach is to use a script that processes those subfolders and their files into a CSV on the Desktop, and then you simply open that CSV in Numbers.

Here is an AppleScript that prompts you for the parent folder, and it will process the sub-folders and their files, generating a CSV on your Desktop — which you can then just open in Numbers.

Open Dock : Launchpad : Other : Script Editor.

Copy and paste the following code into Script Editor.

Click the Compile (hammer) button

Click the Run button.

-- make_csv.applescript
-- Given a selected folder that contains just one level of children folders,
-- generate a CSV 
-- Tested: Ruby 2.3.7.p456, 2.6.3p2, and 2.7.0 on 10.14.6 and 10.15.2
-- VikingOSX, 2019-01-13, Apple Support Communities, No warranties.

set outCSV to POSIX path of ((path to desktop as text) & "dirfiles.csv")
set myfolder to POSIX path of (choose folder default location (path to desktop))
set args to myfolder's quoted form & space & outCSV's quoted form
my build_csv(args)
display dialog "The following CSV is on your Desktop" & return & return & outCSV
return

on build_csv(args)
	return do shell script "ruby <<-'EOF' - " & args & "
#!/usr/bin/env ruby
# frozen_string_literal: true

require 'csv'

# ability to sort csv content by column names
mystruct = Struct.new(
  :file_name,
  :folder_name
) do
  def reset
    self.members.each { |k| send(\"#{k}=\", nil) }
  end
end

csv_heading = %w[Filename Parent]
foo = []

inpath,outcsv = *ARGV

CSV.open(outcsv, 'wb') do |csv|
  csv << csv_heading
end

csv_row = mystruct.new
# put the entire folder/file hierarchy into an array
CSV.open(outcsv, 'a+') do |csv|
  Dir.glob(\"#{inpath}/**/*\").each do |f|
    # construct a row in the csv
    next if File.directory?(f)

    csv_row.file_name = File.basename(f)
    csv_row.folder_name = File.basename(File.dirname(f))
    foo.push(csv_row)
    csv_row = mystruct.new
  end
  foo = foo.sort_by { |s| [s.folder_name, s.file_name] }
  foo.each do |row|
    csv << row
  end
end
csv_row.reset
foo.clear
EOF"
end build_csv

Reply