Hey friendly folks. I’m a graduate student in the social sciences and I’m quickly finding out that developing some scripting skills would greatly enhance my ability to do research, or at least to deal with data. So that will be a big part of my summer plans! But first I’ve got to get a project finished this semester for my advisor (in addition to finishing my term papers and exams!).
The current project runs text files through an analysis tool. Each text file is an observation for a subject, with multiple observations per subject. The text files are named by subject name followed by date of observation (e.g. “Sam Ellton 1995.txt”).
The problem is the files need to be processed in the order they are listed in the dataset. The software processes files in alpha-numeric order of the file name. So I need to find a way to add the identifier as a prefix (e.g. if Sam Elton was subject 450, filename above to change to 450_Sam Elton 1995.txt). In the spreadsheet, I’ve got subject name in one column and subject identifier and underscore in a second column.
I’ve modified a script that I found elsewhere on MacScripter (see below), that works perfectly, except it runs crazy slow. I let it run all night long last night and it only got through about 80 text files. At this rate I’ll never get through all the text files in time.
Any help would be greatly appreciated!
set excelFile to choose file with prompt "Choose the Excel file" of type {"XLS6", "XLS7", "XLS8", "XLSX"}
set theFolder to choose folder with prompt "Choose the folder containing the files to rename"
--get a list of the old and the new filenames
set oldnames to {}
set newnames to {}
tell application "Microsoft Excel"
open excelFile
tell document 1
tell sheet 1
repeat with i from 1 to 9999
if value of cell ("A" & i as text) ≠"" then
set oldnames to oldnames & value of cell ("A" & i as text)
set newnames to newnames & value of cell ("C" & i as text)
else
exit repeat
end if
end repeat
end tell
end tell
close window 1
end tell
--loop through the files and rename them.
tell application "Finder"
repeat with k from 1 to (count of (get every item of theFolder))
repeat with i from 1 to count of oldnames
if ((name of item k of theFolder) as text) contains ((item i of oldnames) as text) and ((name of item k of theFolder) as text) does not contain "_" then
set name of (item k of theFolder) to (item i of newnames) & (name of item k of theFolder) as text
end if
end repeat
end repeat
end tell