Applescript search with Excel to search/exclude file directories

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I have written a script but I am having trouble with the do shell script part.

set theDirectory to quoted form of POSIX path of (choose folder with prompt "Select Search Directory")

set theDestination to quoted form of POSIX path of (choose folder with prompt "Select Directory to Copy Files")

tell application "Microsoft Excel"
	
	tell active sheet
		
		tell used range
			
			set rc to count of rows
			
		end tell
		
		set theList to get value of range ("A1:A" & rc) as list
		
		repeat with theItem in theList
			
			if contents of theItem is not {""} then
				
				do shell script "find " & theDirectory & " -iname " & theItem & " -type d -ipath '*Archive' -prune -o -ipath '*WIP' -prune -o -ipath '*APPROVED/*/*OLD' -prune -o -ipath '*APPROVED/*/*supplied' -prune -o -ipath '*APPROVED/*/*Amends' -prune -o -ipath '*APPROVED/*/*Brief' -prune -o -ipath '*Approved/*' -print -exec cp {} " & theDestination & " \\;"
				
				
			end if
			
		end repeat
		
		
		
	end tell
	
end tell

Our folder structure is “Level 1” — “Level 2”---- “Level 3 Jobcode”

Inside “Level 3 jobcode” are three directories at “Level 4”(“APPROVED” “TO BE ARCHIVED” and “WIP”)

We are trying to target all files inside the “Level 4”(“APPROVED”) folder only.

Inside the “Level 4”(“APPROVED”) folder is a folder “Level 5”(“Datexxx”) which the naming can change and inside that are a further five folders called “Level 6”(“AMENDS” “BRIEF” “LINKS” “OLD” and “SUPPLIED”)

The code is actually just copying the files in the folder “Level 5”(“Datexxx”) excluding all files in “Level 6”(“AMENDS” “BRIEF” “LINKS” “OLD” and “SUPPLIED”).

This script seems to be ignoring the Excel workbook.

Any help would be much appreciated

#2

Welcome to Macscripter.

I’m fixing the shell script by mostly just avoiding using it. Shell commands are more concise, but I generally find the syntax is a lot less confusing to just use Applescript for this kind of file manipulation.

First off, this is a confusing folder structure, so to be sure we’re on the same page here, here’s an image of what I created to test the script:

https://www.dropbox.com/s/sj63cfdtgwqv49j/file%20heirarchy.png?dl=0

This is working for me:


--get directories
set theDirectory to (choose folder with prompt "Select Search Directory")
set theDestination to (choose folder with prompt "Select Directory to Copy Files")

--get jobcodes from Excel
tell application "Microsoft Excel"
	tell active sheet
		tell used range
			set rc to count of rows
		end tell
		set theList to the value of range ("A1:A" & rc)
	end tell
end tell

--find job folders
set foundFoldersList to {}
repeat with aJobcode in theList
	if aJobcode is not "" then
		set foundFoldersList to foundFoldersList & (do shell script "find " & quoted form of POSIX path of theDirectory & " -iname \"*" & aJobcode & "\"")
	end if
end repeat

-- find files to move
set filesToMove to {}
repeat with posixJobFolder in foundFoldersList
	set aliasJobFolder to (POSIX file posixJobFolder as alias)
	tell application "System Events"
		set filesToMove to filesToMove & (every file of the first folder of folder "APPROVED" of aliasJobFolder whose visible is true)
	end tell
end repeat

-- move them
tell application "System Events"
	move filesToMove to theDestination
end tell

While it’s working, I worry it may contain assumptions that might not work in your environment, such as:

  • The “Level 5” folder is always the sole subfolder of the “Approved” folder.
  • You want to move every non-invisible file (as opposed to folder) inside the Level 5 Folder.

If these aren’t correct, this is easy to adjust. I’m just not 100% clear on the allowed logic.

#3

I also suspect it could use a lot more error handling, but I wasn’t sure what that would be. For example, is it always supposed to find exactly one folder per jobcode? If so, I’d error if it finds more or less. If the assumption that:
“The “Level 5” folder is always the sole subfolder of the “Approved” folder.”
was correct, then probably best to check that the count of folders in “Approved” = 1.

That kind of thing.

#4

Wow this is awesome…
many thanks.

If nothing is in Approved it comes up with an error. Is there anyway to log the error and move onto the next code in the list.

#5 

--get directories
set theDirectory to (choose folder with prompt "Select Search Directory")
set theDestination to (choose folder with prompt "Select Directory to Copy Files")

--get jobcodes from Excel
tell application "Microsoft Excel"
	tell active sheet
		tell used range
			set rc to count of rows
		end tell
		set theList to the value of range ("A1:A" & rc)
	end tell
end tell

--find job folders
set foundFoldersList to {}
repeat with aJobcode in theList
	if aJobcode is not "" then
		set foundFoldersList to foundFoldersList & (do shell script "find " & quoted form of POSIX path of theDirectory & " -iname \"*" & aJobcode & "\"")
	end if
end repeat

-- find files to move
set filesToMove to {}
set emptyApprovedFolders to {}
repeat with posixJobFolder in foundFoldersList
	set aliasJobFolder to (POSIX file posixJobFolder as alias)
	tell application "System Events"
		try
			set levelFiveFolder to the first folder of folder "APPROVED" of aliasJobFolder
			set filesToMove to filesToMove & (every file of levelFiveFolder whose visible is true)
		on error
			set emptyApprovedFolders to emptyApprovedFolders & posixJobFolder
		end try
	end tell
end repeat

-- move them
if filesToMove ≠ {} then
	tell application "System Events"
		move filesToMove to theDestination
	end tell
end if

if emptyApprovedFolders ≠ {} then display dialog "The following Jobcode folders did not contain any subfolders in \"APPROVED\":" & return & return & list_to_text(emptyApprovedFolders)

on list_to_text(aList)
	set textOutput to ""
	set itemCount to count of aList
	repeat with i from 1 to itemCount
		set currentItem to item i of aList
		if i ≠ itemCount then
			set textOutput to textOutput & currentItem & return
		else
			set textOutput to textOutput & currentItem
		end if
	end repeat
end list_to_text