A Faster Script
A Faster Script
- Subject: A Faster Script
- From: Chip Fillingane <email@hidden>
- Date: Tue, 6 Apr 2004 00:33:47 -0500
To whom it may concern:
I work in a chemistry lab at a university, and one of the things we
have to do is copy and past thousands of points into Microsoft Excel.
It's really very boring. So I tried to automate it with applescript.
My script works, but the problem is at the rate it is copying the data
into excel it will take some 5 to 6 hours to finish, which is far more
time than it takes me to do it myself! (And it hangs far before even
half an hour has past.) I'm certain there is a faster way to do this,
but I don't see it.
The data points are spread across around several tab delimited files.
Each files goes into two separate columns, and the columns are labeled
with a number and either an 'x' or a 'y'.
Once the data is in Excel it looks like this:
1x 1y 2x 2y. . .
1 2.345 1.476 3 . . .
0 1.23 -0.41 2 . . .
So 1x and 1y are the x and y coordinates for the first file, etc.
All of the time is spent in the repeat loop that actually moves the
data out of applescript into Excel:
repeat with n from 1 to ((count of unSortedData) / 2)
set yCoordinate to ColumnY & "$" & yCount as string
set yCount to yCount + 1
set xCoordinate to ColumnX & "$" & xCount as string
set xCount to xCount + 1
tell application "Microsoft Excel"
set Value of Cell xCoordinate to item (2 * n - 1) of unSortedData
set Value of Cell yCoordinate to item (2 * n) of unSortedData
end tell
end repeat
It take approximately 5 minutes to move 6000 data points by this
method. Considering each file has between 3000-6000 data points, and
there generally 70 to 80 files those five minutes add up to several
hours very quickly.
Originally I tried assigning all of the x points to a list and then all
of the y points to a list and then putting all of that into Excel in
one step. But that went much slower. (I assumed it did so because it
had two lists each around 6000 items long.) Plus, I was never able to
get Excel to assign the list to a column. (Again, maybe size related?)
Any help is greatly appreciated. If needed, my whole script is
attached at the bottom. I imagine to any scripter of greater talent it
looks verbose and inefficient, but the only time crunch is the one
listed above and it operates independently of most of the script.
Many Thanks,
Chip Fillingane
email@hidden
--Script attached below this point!!
--select the folder where the file to be imported to excel reside
display dialog "In the following window select the folder containing
the files to be imported into Excel" buttons {"Cancel", "OK"} default
button 2
if the button returned of the result is "OK" then
set source_folder to choose folder
set source_folder_string to source_folder as string
else
quit
end if
set folder_files to list folder source_folder
set files_count to the count of folder_files
--get the prefix of the desired files
set queryOne to display dialog "Enter the prefix of the files to be
imported into Excel." default answer "UN" buttons {"Cancel", "OK"}
default button 2
if the button returned of the result is "OK" then
set file_prefix to text returned of queryOne
else
quit
end if
tell application "Microsoft Excel"
Create New Workbook
end tell
--identify all of the desired files
set filesToExamine to {}
repeat with currentFile in folder_files
if currentFile begins with file_prefix then
set the end of filesToExamine to the contents of currentFile
end if
end repeat
set currentStep to 0 --this is necessary in order correctly label the
columns and to propery assign the data to the appropriate column
repeat with currentFile in filesToExamine
--This functions to examine each tab-delimited file and extract the
data from it. It does not, however, maintain any organizational
constructs.
set currentStep to currentStep + 1
set filePath to source_folder_string & currentFile as alias
set unSortedData to read filePath using delimiter {tab, reuturn}
--Label the first row of the columns of interest for easy reading
set ColumnX to cellLabel(1, (2 * currentStep - 1), false)
set ColumnY to cellLabel(1, (2 * currentStep), false)
set xCoordinate to ColumnX & "$" & 1
set yCoordinate to ColumnY & "$" & 1
tell application "Microsoft Excel"
set Value of Cell xCoordinate to ((currentStep as string) & "x")
set Value of Cell yCoordinate to ((currentStep as string) & "y")
end tell
--Here the unsorted data obtained from the tab delimited file is
reorganized into its x and y parts and shipped to Excel one point at a
time
set xCount to 2
set yCount to 2
repeat with n from 1 to ((count of unSortedData) / 2)
set yCoordinate to ColumnY & "$" & yCount as string
set yCount to yCount + 1
set xCoordinate to ColumnX & "$" & xCount as string
set xCount to xCount + 1
tell application "Microsoft Excel"
set Value of Cell xCoordinate to item (2 * n - 1) of unSortedData
set Value of Cell yCoordinate to item (2 * n) of unSortedData
end tell
end repeat
end repeat
--Objects to make life easier
on cellLabel(r, c, labelRows)
--cellLabel allows for any cell to be labeled according to Excel's
conventions, using the coordinates of the desired cell. To return the
column exclusively set the last term to false.
if labelRows is false then
set c to labelColumn(c)
set cell to "$" & c
return cell
else
set c to labelColumn(c)
set cell to "$" & r & "$" & c
return cell
end if
end cellLabel
on labelColumn(columnNumber)
--LabelColumn is an object which will covert the numeric label of a
column into its alphabetic counterpart, following the format Microsoft
Excel uses.
set letters to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K",
"L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y",
"Z"}
if columnNumber is less than or equal to 26 then
return item columnNumber of letters
else
--this next statement simply obtains the number of whole times 26
will fit into the given number
set multiple to (columnNumber - (columnNumber mod 26)) / 26
set letter to item (columnNumber mod 26) of letters
set label to ""
repeat multiple times
set label to label & letter
end repeat
end if
return label
end labelColumn
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.