Wow Automator has saved me a ton of time. Rather than manually place ~2000 images into InDesign, it did it for me in minutes
My dilemma right now though is that I have about 2000 files, that correspond to a person. So I have an excel table that says the file name, and personâs last and first name.
The files were named arbitratily, however, so when I open the folder containing the 2000 files, the first person on my list might be near the bottom. Is there anyway I can possibly use Automator to take, say the names in my excel list, and rename the files they correspond to? This seems like a total shot in the dark but I canât think of any other way to approach the problem.
this is a plain Applescript, which should do it.
The script assumes three columns, the file names in the first, the last names in the second and the first names in the third column e.g.
myFile.jpg Doe John
If you have only two columns omit & â_â & item 3 of i in line 10
The script uses a binary search, which works much faster with large lists than a linear search
set excelFile to choose file with prompt "Choose the Excel file" of type {"XLS6", "XLS7", "XLS8"}
set theFolder to choose folder with prompt "Choose the folder containing the files to rename"
tell application "Microsoft Excel"
open excelFile
set P to value of used range of active sheet
end tell
set {fileNames, peopleNames} to {{}, {}}
repeat with i in P
set end of fileNames to item 1 of i
set end of peopleNames to item 2 of i & "_" & item 3 of i
end repeat
set theFiles to list folder theFolder
repeat with oneFile in theFiles
set idx to BinarySearch(fileNames, contents of oneFile)
set x to item idx of peopleNames
tell application "Finder" to set name of file oneFile of folder theFolder to item idx of peopleNames
end repeat
on BinarySearch(theList, value)
local low, mid, high
script o
property l : theList
end script
set low to 1
set high to (count theList)
repeat while (low ââ°Â¤ high)
set mid to (low + high) div 2
if ({value} is in items low thru mid of o's l) then
set high to mid - 1
else
set low to mid + 1
end if
end repeat
if (item low of o's l is value) then return low
return false
end BinarySearch
I have run into some problem, however (Iâm not too well versed with AppleScript).
For the line âset P to value of used range of active sheetâ it said âExpected end of line, etc. but found class name.â
So I changed it to A1 (I tried a few other things but they results in syntax errors). It accepted that, but once I ran it it said highlighted open excelFile and said "Microsoft Excel got an error: ââ could not be found.__Check the spelling of the file name, and verify that the file location is correct.
If you are trying to open "
Iâm a bit stuck here with the part.
Thanks again for your help, I really appreciate it!
Sorry to bother you again, but I have a slightly problem. It says nothing for a minute, and then it says âCanât get item 1037 of the range of my spreadsheetâ. I have 1036 rows of information. I tried playing around with the script but couldnât get it to stop giving me that error, or not crash
I really hate to do this since youâve given me so much help already, but I still canât seem to get it to work for me.
It says âCanât get item 1037 of {â1Y11007.TIFâ, â1Y11117.TIFâ, â1Y05021.TIFâ, â1Y04006.TIFâ, â1Y11018.TIFâ, â1Y02022.TIFâ, â1Y04009.TIFââŚâ and highlights value after I click ok.
if (item low of oâs l is value) then return low
return false
end BinarySearch
maybe a terminology clash with a scripting addition,
try this modified search routine
on BinarySearch(theList, theValue)
local low, mid, high
script o
property l : theList
end script
set low to 1
set high to (count theList)
repeat while (low ââ°Â¤ high)
set mid to (low + high) div 2
if ({theValue} is in items low thru mid of o's l) then
set high to mid - 1
else
set low to mid + 1
end if
end repeat
if (item low of o's l is theValue) then return low
return false
end BinarySearch
Thanks for the quick reply, although unfortunately I still get the same error. It keeps on saying that it canât get item 1037. I have 1036 rows of data (so from 1037 onwards is just blank).
Then there must be a problem with the search subroutine
Try this slightly modified one
on BinarySearch(TheList, theValue)
script o
property l : TheList
end script
set low to 1
set high to (count TheList)
repeat until (low = high)
set mid to (low + high) div 2
if ({theValue} is in items low thru mid of o's l) then
set high to mid
else
set low to mid + 1
end if
end repeat
if (item low of o's l is theValue) then return low
return false
end BinarySearch
Iâm in need of the same process. I have âItemcodeâ and âFiledescriptionâ in place of names in the Excel file. I adjusted your script, but I get the error:
Canât get item false of {âfiledescriptionâ, â082653_Poster1â, â082653_Poster2â, â082653_Poster3â}.
Highlighted line item:
set x to item idx of filedescription
