Help improving Automator find and copy action

I have the need for an automator action to search for files with a specified name contains, and copy them to a destination folder. This was simple to build in Automator and works well I just used the find files and copy files actions.

The problem is i need to do this for thousands of items. What I would like to do is be able to copy a big list of file names from Excel (100-200) into the action, have it go out and find all of the items (several thousand, between about 10 and 100 for each search term) and move each set to an individual folder that would be automatically named with the search term. So in the end if I entered 200 search terms I would end up with 200 folders named with the search term containing the files with the search term.

I can’t figure out how to get Automator to do this. Any help getting this to work with automator and or Applescript would be greatly appreciated.

Thanks,
Jeff

Hi Jeff,

searching and copying thousands of files with the Finder takes hours.
The best way to do this is to write a shell script which does the whole thing.
'Cause I’m not able to do it, here a (very schematic and) simple solution with AppleScript and shell commands
to do the time critical work

The script assumes the follwing things:
¢ an open Excel document with only search terms in the used range
¢ searching will only be done in the home folder
¢ the folders will be created on desktop (no error trapping at all)

Either the normal find command or the spotlight metadata search command (mdls) can do the search.
Note: It would be useful to test the script with a specific folder, which contains only a few items, the search is always recursive

tell application "Microsoft Excel" to tell active sheet
	set L to value of used range -- get the search terms
end tell

set inputFolder to quoted form of POSIX path of (path to home folder) -- the folder to search in (recursive)

repeat with i from 1 to count L
	set keyword to item 1 of item i of L
	   -- search with find
	-- set foundFiles to paragraphs of (do shell script "find " & inputFolder & " -type f -name '*" & keyword & "*' ! -name '.*'")
	   -- search with mdls
	set foundFiles to paragraphs of (do shell script "mdfind -onlyin " & inputFolder & " 'kMDItemFSName  = \"*" & keyword & "*\"'")
	set dest to do shell script "mkdir -v ~/Desktop/" & keyword -- makes new folder at desktop
	repeat with i in foundFiles
		try -- skips folders (only needed with the spotlight search)
			do shell script "cp " & quoted form of i & space & quoted form of dest
		end try
	end repeat
end repeat

Thank you so much, the script works great. I am new to Appplescript I tried to change the location where it makes the output folders but it did not seem to work. Do you know how I would go about specifying where to put the folders instead of the desktop? Also it would be great to specify what folder to search in.

Would there be a way to either in this script or in a second script to change the name of the folders from the search term to another associated term. The search term is a code that identifies the files, but it would be better if i could then reconcile it to the client name. i have the client names in excel as well.

Thanks again,
Jeff

Specifiying the location is quite tricky in this case because the shell command
expects an POSIX path (slash delimited path) while AppleScript works with the old fashioned
colon separated path (and the quoted form avoids problems with spaces in paths).
You can start e.g with desktop

set inputFolder to quoted form of POSIX path of ((path to desktop as Unicode text) & "path:toFolder"

path to desktop can be replaced by path to home folder, path to documents folder
and there a few relative paths more, to make the code portable.

Or you ask the user to choose the folder

set inputFolder to quoted form of POSIX path of (choose folder)

Of course it’s possible to change the folder names.
If you have the terms in the first column and the corresponding name in the second
you get the list L like {{term1, name1}, {term2, name2}

the changed script:

tell application "Microsoft Excel" to tell active sheet
	set L to value of used range -- get the search terms
end tell

set inputFolder to quoted form of POSIX path of (choose folder)
repeat with i from 1 to count L
	set {keyword, newname} to items 1 thru 2 of item i of L
	-- search with find
	-- set foundFiles to paragraphs of (do shell script "find " & inputFolder & " -type f -name '*" & keyword & "*' ! -name '.*'")
	-- search with mdls
	set foundFiles to paragraphs of (do shell script "mdfind -onlyin " & inputFolder & " 'kMDItemFSName  = \"*" & keyword & "*\"'")
	set dest to do shell script "mkdir -v ~/Desktop/" & newname -- makes new folder at desktop
	repeat with i in foundFiles
		try -- skips folders (only needed with the spotlight search)
			do shell script "cp " & quoted form of i & space & quoted form of dest
		end try
	end repeat
end repeat

Asking which folder is perfect. I can live with the results being on the desktop. With first and last names it did not seem to work but if i put an underscore between the names it worked great. Thanks again, this will save me so much time.

Jeff

sorry I missed that.
You can also choose the destination folder with

set destinationFolder to POSIX path of (choose folder)
...

...
set dest to do shell script "mkdir -v " & quoted form of (destinationFolder & newname)

put the second choose folder line just next to the first and replace the mkdir line

That worked great thanks again.

Jeff