Applescript: Copy files from Excel or Text to a new folder folder (with error message)

[mayodeftin]

Hello! I’m have almost no knowledge about scripting and coworker gave me this script to simplifiy my work process but it’s not working like it should… Could anyone here help me?

We have a lot of files on a server, they are saved in different subfolders. Sometimes I get a list of file names and have to find these files. This would be a perfect job for a script and save me so much time, but it should also tell me when a file is not found.

Here’s an example of a list:
18-3045
23-2127
A BLA 18-4
CH-3450-1234

The files exist in many formats (JPG, TIFF, DNG ans so on) so the script should find all of these files and only report the error if there is no file at all. Currently my script reports an error if a file is not a subfolder, so basically all of them are in the error.txt because obviously not all files are the same folder.

#!/usr/bin/osascript

set theDirectory to 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 & "*' -exec cp {} " & theDestination & " \\;"
				do shell script "if ! ls " & theDirectory & theItem & "* ; then echo " & theItem & " >> ~/Desktop/error.txt ; fi"
			end if
		end repeat
	end tell
end tell

Any help would be greatly appreciated!

[Mockman]

I think it’s because your use of ‘ls’ always generates a ‘No such file or directory’ or ‘no matches found’ error. At least, that’s what I get when I use the command in the terminal.

Here is a variation that uses find to test for a matching file and inform the if..then and then again to find and copy the file. The advantage in taking this approach is that the test will match the actual result exactly.

I also changed the excel code to use intersect to create the range, and then moved the repeat loop outside of the excel tell block, since excel isn’t necessary after acquiring the values.

-- #!/usr/bin/osascript

use scripting additions
set searchDir to quoted form of POSIX path of (choose folder with prompt "Select search directory")
set destDir to quoted form of POSIX path of (choose folder with prompt "Select copy destination")

tell application "Microsoft Excel"
	tell active sheet to set valUsed to value of (intersect range1 used range range2 range "A:A") as list
end tell

repeat with theValue in valUsed
	if contents of theValue is not {""} then
		set conVal to contents of theValue
		set findCmd to "find " & searchDir & " -iname '" & conVal & "*' "
		set testExist to do shell script findCmd
		if testExist is not equal to "" then
			set findCopyCmd to "find " & searchDir & " -iname '" & conVal & "*' -exec cp {} " & destDir & " \\; "
			do shell script findCopyCmd
		else
			do shell script "echo " & conVal & " >> ~/Desktop/error.txt "
		end if
	end if
end repeat

I haven’t tested it as a shell script but did run it from the shell using osascript.

[mayodeftin]

You are right, it works perfectly now! Thank you so much!!!

[peavine]

Mockman has answered the OP’s question, and I wrote a shortcut solution just for practice. I don’t have Excel, so the file names are specified in separate lines in a text file. The error file is placed in the same folder as the source files. A counter is appended to copied files if a duplicate is encountered.

File Copy.shortcut (23.6 KB)

[mayodeftin]

Cool! Thanks you!!