Scripts to the rescue

My thesis work involves working on several applications.. The data processing is usually done in excel before it is sent as an input to the MATLAB. The usual data processing in excel is something thats very recurring and monotonous.. I should process atleast 25 files in excel before i input it to matlab for one test case. And doing these were becoming too cumbersome for me. Though i knew Mac had some softwares like Automator, Applescript etc., i never knew how to use them. But today, after a day long testing, and collecting data for over 72 testcases, I face a cold reality of how many files i need to process, let alone execute using Matlab.
To find an alternative solution to the daunting task ahead of me, I ventured into studying about how Automator and Applescript works.. Applescript sounded a lot easier and simpler. So i googled about it, read some basics and wrote a couple of scripts that does the necessary processing on my excel sheets. Basically, I remove the unnecessary columns and rows from the excel data so that Matlab can process the data easily. As many of you know Matlab can't accept inputs if they have texts in them. So part of this processing is to remove the texts, if any, from the input data. Since I know exactly which column and row would need to be removed, I wrote these scripts that would make my life much better. How I wish, I had known this earlier. Would have saved me lots of time in the past. Here is one of the scripts, just in case anyone wants to modify it for their own use.

--- Apple script ---

set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"

set theWorkbookName to name of (info for theWorkbookFile)

tell application "Microsoft Excel"

open theWorkbookFile

set theWorkbook to active workbook

tell worksheet "Sheet1" of active workbook

delete row 1

delete column 1

repeat with x from 39 to 10 by -1

delete column x

end repeat

end tell

save active workbook

close active workbook

end tell

0 comments:

Me!

My photo
Music lover, gamer, love Apple products, Neuroscientist by profession and by choice.

My inspirators


When I speak tech