Sorting Files with Excel Spreadsheet

It's almost certainly possible to do, but not without more information.

My first recommendation, though, would be to get the data out of Excel. Even copying the data to the clipboard, or saving the spreadsheet as a text file would be a help. Seriously. Excel's AppleScript implementation is so broken it makes grown men cry.

Once you have the data, though, a simple loop through the rows could identify the files you want, from there it's easy to select them, move them, copy them, etc. You just need to state your goals.

in applescript, you'll want something like so:

tell application "Microsoft Excel"

tell worksheet 1 of active workbook

set fileList to value of used range

end tell

end tell

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

set filesToMove to {}

repeat with thisItem in fileList

try

set end of filesToMove to alias (rootFolder & thisItem & "_John_Doe")

on error

display dialog "File " & thisItem & " is not in the folder"

end try

end repeat

tell application "Finder"

move filesToMove to folder "hard Drive:users:yourname:path:to:destination folder:"

end tell

unfortunately, you haven't given enough detail for me to be sure this will do what you want. you'll either have to explain more or experiment with it to make it work.

hmmmm... looks to me like you did figure it out. But I'm glad I could get you started. 🙂

Hi,

Here is an example of how to do that.

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

set tName to "1234567_John_Doe.jpg"

setnewnametomyremoveCharsInFirstName(tName) ----> 1234567_J_Doe.jpg

onremoveCharsInFirstName(t)

settidtotext item delimiters

settext item delimitersto "_"

set L to text items of t

if (countL) > 2 then -- two underscores or +

set firstName to item 2 of L

if firstName is not "" then

set item 2 of L to text 1 of firstName

set t to L as string

end if

end if

settext item delimiterstotid

return t

endremoveCharsInFirstName

I think they moved applescript to a different folder.

The applescript editor used to be here:

/Applications/AppleScript/Script\ Editor.app/

I think they moved it to /Applications/Utilities.

This line does the rename.

inside a tell finder.

set name of file actualName to newName

For debugging use the log statement.

(*

excel file where the first column is the new SKU name and the second column is the name of the photo that needs to be changed.

use excel to save as csv file -- comma separated

example file:

rename.csv

a,1

b,2

c,3

*)

on run

-- Write a message into the event log.

-- To see,

-- Run this applescript in Sript Editor.

-- Click on Event Log tab at bottom of screen.

-- Click run.

log " --- Starting on " & ((current date) as string) & " --- "

-- Ask user for the name of the file

set fileAlias to choose file with prompt "Pick the file with your list of files to rename."

log fileAlias

set aFile to fileAlias as text

log "aFile = " & aFile

-- Ask user for the name of the folder

set folderAlias to choose folder with prompt "Pick the folder that contains your list of files to rename."

log folderAlias

set aFolder to folderAlias as text

-- Based on Camelot's script in

-- http://discussions.apple.com/thread.jspa?threadID=2739645&tstart=0

set files2Rename to (paragraphs of (read file aFile))

log files2Rename

repeat with eachFile in files2Rename

log "eachFile = " & eachFile

set splitData to textToList(eachFile, ",")

set newName to item 1 of splitData

set oldName to item 2 of splitData

log "newName = " & newName & " oldName = " & oldName

tell application "Finder"

set actualName to aFolder & oldName

log "actualName = " & actualName

try

-- example rename command

-- set name of file "path:to:file" to (month of (current date)) & " " &

-- day of (current date) & ", " & year of (current date) as string

set name of file actualName to newName

on error msg

log "!!! could not rename newName = " & newName & " oldName = " & oldName

log "!!! error was " & msg

end try

end tell

end repeat

end run

-- textToList was found here:

-- http://macscripter.net/viewtopic.php?id=15423

on textToList(thisText, delim)

set resultList to {}

set {tid, my text item delimiters} to {my text item delimiters, delim}

try

set resultList to every text item of thisText

set my text item delimiters to tid

on error

set my text item delimiters to tid

end try

return resultList

end textToList

AppleScript

Learn AppleScript: The Comprehensive Guide to Scripting and Automation on Mac OS X, Third Edition the book

AppleScript Language Guide pdf download the pdf file

Intro to applescript with sending an email
http://mac.appstorm.net/how-to/applescript/the-ultimate-beginners-guide-to-apple script/

ktsbatis wrote:

Do you know how I get this script to apply to my folder containing all my files? I haven't been able to figure that out. My folder is located on my desktop but I don't know how to tell AppleScript to go to it and rename the files.

Like this :

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

setFolderNameto "Untitled" --** a folder name located on my desktop --**

tell application "System Events"

repeatwithfin (getfilesoffolderFolderNameofdesktop folderwhosevisibleistrue)

set tName to name of f

setnewnametomyremoveCharsInFirstName(tName)

if newname is not tName then set name of f to newname

endrepeat

endtell

onremoveCharsInFirstName(t)

settidtotext item delimiters

settext item delimitersto "_"

set L to text items of t

if (countL) > 2 then -- two underscores or +

set firstName to item 2 of L

if firstName is not "" then

set item 2 of L to text 1 of firstName

set t to L as string

end if

end if

settext item delimiterstotid

return t

endremoveCharsInFirstName