Sorting Files with Excel Spreadsheet

ktsbatis wrote:

"File A638411 is not in the folder"

I'm wondering if I can tweak the script a little to get it to still pull the file even though my excel list does not contain entire file names.

Thanks Again!

It's possible.

Post your script, I prefer to modify your script, rather than give a few lines that you will not fit your script.

ktsbatis wrote:

This is what I'm using:

Here's the modified script.

-----------------------------------

set rootFolder to alias "hard Drive:users:yourname:path:to:source folder:"

set destFolder to alias "hard Drive:users:yourname:path:to:destination folder:"

tell application "Microsoft Excel"

tellworksheet 1 ofactive workbooktosetfileListtovalueofused range

endtell

repeatwiththisIteminfileList

tell application "System Events"

move (filesofrootFolderwhoseitsnamestarts withthisItem) todestFolder -- match exact name and beginning

endtell

endrepeat

Your problem, I think, is that you're moving files one at a time - that's costly. Use the Finder to move them all at once:

set rootFolder to alias "hard Drive:users:yourname:path:to:source folder:"

set destFolder to alias "hard Drive:users:yourname:path:to:destination folder:"

tell application "Microsoft Excel"

tell worksheet 1 of active workbook to set fileList to value of used range

end tell

set moveList to {}

repeat with thisItem in fileList

tell application "System Events"

set moveList to moveList & path of (files of rootFolder whose name starts with thisItem)

end tell

end repeat

tell application "Finder"

movemoveListtodestFolder

end tell

Hi,

This line is slow --> tell application "System Events" to (files of rootFolder whose name starts with thisItem)

Because filtering each item in the folder which contains 1000+ files is very slow.

For my tests on a old computer (PowerMac G5), I have 1000 items in Excel and 1000 files in the folder.

1- I test the script of twtwtw
--> filtering:16 minutes, moving:35 secondes

2- Move 1000 files one by one -- move file thisItem of rootFolder to destFolder --> 20 secondes

3- Filtering and moving each item using a do shell script --> 9 sec

Here's the script :

----------------------------

set rootFolder to alias "hard Drive:users:yourname:path:to:source folder:"

set destFolder to alias "hard Drive:users:yourname:path:to:destination folder:"

tell application "Microsoft Excel"

tellworksheet 1 ofactive workbooktosetfileListtovalueofused range

endtell

settidtotext item delimiters

settext item delimitersto "\\0"

set fileList to quoted form of ((fileList as string) & "\\0")

settext item delimiterstotid

setrftoquoted formofPOSIX pathofrootFolder

setdftoquoted formofPOSIX pathofdestFolder

do shell script "cd " & rf & "; echo " & fileList & " | while read -d $'\\0' line; do /bin/mv -f \"$line\"* " & df & " & done"

If you do not want to have a shell script.

Here another script , I did the same test script as my other --> filtering:1 minute, moving:20 seconds.

------------------------------

set rootFolder to alias "hard Drive:users:yourname:path:to:source folder:"

set destFolder to alias "hard Drive:users:yourname:path:to:destination folder:"

main(rootFolder, destFolder)

onmain(rootFolder, destFolder)

script o

property tNames : missing value

property fileList : missing value

property thislist : missing value

on getfirstPar(s)

set x to ""

settidtotext item delimiters

set text item delimiters to {"\\0" & s}

try

set x to text item 2 of my tNames

set text item delimiters to {"\\0"}

set x to s & (text item 1 of x)

end try

settext item delimiterstotid

return x

end getfirstPar

endscript

tell application "Microsoft Excel"

tellworksheet 1 ofactive workbooktoseto's fileListtovalueofused range

endtell

tell application "System Events" to set o's tNames to name of files of rootFolder

settidtotext item delimiters

settext item delimitersto "\\0"

seto's tNamesto "\\0" & o's tNames -- convert list to string, name separated by NULL

settext item delimiterstotid

set tc to (count o's fileList)

repeatwithifrom 1 totc

set o's thislist to item i of o's fileList

set tc2 to (count o's thislist)

repeat with j from 1 to tc2

settnametoo's getfirstPar(itemjofo's thislist) -- search in tNames, return the first paragraphs that begin with this name

if tname is not "" then tell application "System Events"

move file tname of rootFolder to destFolder

end tell

end repeat

endrepeat

endmain

It is easier to diagnose problems with debug information. I suggest adding log statements to your script to see what is going on. Here is an example.

(* 
    Author: rccharles
    
    For testing, run in the Script Editor.
      1) Click on the Event Log tab to see the output from the log statement
      2) Click on Run

 *)


on run
    -- Write a message into the event log.
    log "  --- Starting on " & ((current date) as string) & " --- "
    --  debug lines
    set desktopPath to (path to desktop) as string
    log "desktopPath = " & desktopPath
end run

Yes, it is a file that already exists in the destination folder.

This may be a duplicate cell in Excel or the beginning of a cell value is identical with another cell.

Example : cell x = "A005426_M_Larsh.tif" and cell y = "A005426_M", the script will find the same name.

You can use the logs as rccharles explain, or a try block like this :

---------------------------------------

try

if tname is not "" then tell application "System Events"

movefiletnameofrootFoldertodestFolder

endtell

onerrorerr

display dialog "file name = " & tname & return & "Error : " & err

endtry