[eggPlant Functional] Reading data from Excel spreadsheets

Many users are familiar with spreadsheets, so you may find Microsoft Excel to be a convenient tool for storing and maintaining sets of values to be used for data driven testing. But how do you get the data from an Excel worksheet into a form that can be read by your Eggplant script?

One way is to export the data from the spreadsheet into a tab-delimited text file and have your script read that file. While this works, it means that each time values are updated you will have to remember to manually export the data again, being sure to export the correct set of data into the correct file each time. This process involves a manual step, and is prone to error. Fortunately, there is a better way.

Using SenseTalk's ability to call AppleScript, it is possible to have Eggplant read the data directly out of an Excel spreadsheet on your Mac (note: you will need to have Microsoft Office X installed on your Mac for this to work). Here is an example script that does this:
do AppleScript {{
tell application "Microsoft Excel"
open workbook workbook "Titanium:Users:doug:Eggplant:MyData.xls"
get value of the used range of worksheet 1
end tell
}}
put the result into fullData -- a list of lists

-- convert to a list of records, using the first row titles as the keys
put item 1 of fullData into header -- get the column titles
delete item 1 of fullData
repeat with each row of fullData
	set record to (:)
	repeat with n=1 to the number of items in header
		set property (item n of header) of record to item n of row
	end repeat
	insert record after recordList
end repeat
This script begins with a "do AppleScript" command that retrieves the data from the Excel worksheet. The first line of the AppleScript code is a "tell" command that identifies "Microsoft Excel" as the application we want to communicate with. The script then tells Excel to open a particular worksheet and get the "usedRange" from that worksheet. Note that the full file path of the worksheet is specified beginning with the machine name ("Titanium") and using ":"s to separate components of the path rather than the usual "/"s -- this is the way that Excel on the Mac expects to see the path.

When Excel is asked for the "usedRange" it returns just the part of the worksheet where values have been entered. The value returned by AppleScript's "get" command is retrieved using SenseTalk's "the result" function and stored into a variable called fullData. The data is returned as a list of lists, with the outer list being the rows and each inner list containing the values for the columns in that row.

At this point the values have all been retrieved from Excel. The next part of the script shows a technique for converting the data into a list of records. Each record is a property list containing values identified by keys. The keys are derived from the column headings in the first row of the worksheet. So this technique will work for any set of data where the first row contains the names of the columns and subsequent rows contain the data values.

Having the data organized as property lists generally makes it much nicer to work with. For example, suppose your spreadsheet contained information about different users like this:
User Name   Full Name           Password
fred        Fred Flintstone     wilma
barney      Barney Rubble       betty
pebbles     Pebbles Flintstone  bambam

then you might go on to use the recordList something like this:
sort recordList by the "User Name" of each

repeat with each user in recordList
	typeText user's "User Name" & return
	typeText user's password
	verifyFullName the "Full Name" of user
end repeat

Note that if your column titles contain more than one word, you will need to put quotes around the key when accessing that property from the property list.

Caveats

The simple AppleScript code presented above worked fine in my tests using simple string and number values. When the worksheet included date values, they were not returned correctly. I was able to get it to work with dates by using the following more complex AppleScript code:
do AppleScript {{
tell application "Microsoft Excel"
open "Titanium:Users:doug:Eggplant:MyData.xls"

set theRange to value of the usedRange of worksheet 1
set newData to {}
repeat with aRow from 1 to count of theRange
  set newRow to {}
  repeat with aCell from 1 to count of item 1 of theRange
    set newRow to newRow & text of cell aCell of row aRow of worksheet 1
  end repeat
  copy newRow to the end of newData
end repeat
get newData
end tell
}}
put the result into fullData

This took noticeably longer to run than the original version, but succeeded in returning the date values using the text representation displayed in the cells of the worksheet.

Both versions of AppleScript code shown above worked for me, using an older version of Microsoft Office. I understand that Excel 2004 includes significant changes to its AppleScript support dictionary, so some modifications may be needed to work with that version.

Comments

  • Hi,

    I tried to execute the code provided by you for accessing data in excel form.
    Like you mentioned i did come across issues with Excel 2004.

    error that i get is:

    Applescript error The variable the range is not defined.

    Note: it does launch the excel program but is stuck at opening the file.

    I'm new to both Eggplant and Applescript. As such, if you could guide me on resolving this, it will be great!

    thx.

    -veena
  • SenseTalkDougSenseTalkDoug ForumAdmin admin
    Unfortunately I don't have access to Excel 2004. But I downloaded the document "Microsoft Excel 2004 AppleScript Reference" from Microsoft's website here:
    http://www.microsoft.com/mac/resources/resources.aspx?pid=asforoffice

    Looking through this document I notice that there is no mention of the "usedRange" property as shown in the scripts above. Instead, it appears to now be called "used range" (as two words). I don't know if that's the only difference from the earlier version of Excel, but why don't you try making that change and see if it gets you a little closer.

    Good luck! And if you succeed in getting a comparable script that works for Excel 2004, please post it back here so other people can use it. Thanks!
  • Hi,

    The above code still works except for changing 'usedRange' to 'used range'.

    Although code
    open "Titanium:Users:doug:Eggplant:MyData.xls" works fine. The document recommends using:

    open workbook workbook file name "Titanium:Users:doug:Eggplant:MyData.xls"

    -veena :D
  • Hello,

    I am trying to implement this applescript fragment into an Eggplant scripts that I am trying to set up for data based testing. The script is not running, even in the Applescript editor. I am getting an error that states: "Expected 'end' but found unknown token."

    It seems to be triggering at the line commented below:

    tell application "Microsoft Excel"
    open workbook workbook file name "Osiris:Users:steveo:Documents:Address Book Words.xls"

    set theRange to value of the used range of worksheet 1
    set newData to {}
    repeat with aRow from 1 to count of theRange -- I think this is where the problem is cropping up
    ? set newRow to {}
    ? repeat with aCell from 1 to count of item 1 of theRange
    ? ? set newRow to newRow & text of cell aCell of row aRow of worksheet 1
    ? end repeat
    ? copy newRow to the end of newData
    end repeat
    get newData
    end tell

    put the result into fullData

    Again, I am simply trying to run this in the script editor (to attempt and troubleshoot) but I cannot see what is wrong.

    Thanks for any and all assistance.

    Regards,

    Steve O'Sullivan
  • SenseTalkDougSenseTalkDoug ForumAdmin admin
    That script compiles just fine in the AppleScript Script Editor for me. I'm still running an older version of Excel, which is likely the difference. The terminologies have changed in more recent versions. What version of Excel are you using on your Eggplant machine?
  • Hello Doug,

    I am using Excel 2004 with latest Office patches on OS X 10.5.2.

    SteveO
  • Hello Doug,

    Based upon your last email, I just decided to try this script on my work machine, and it does function! Ok, now to see what is not right on my home machine....

    However, when the script is run, the result is simply: {{,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}, {,}}

    I am simply a copy and past Applescripter, so any insight you might have is greatly appreciated.

    Thank you very much for your consideration.

    SteveO
  • Well I go the script to work in the script editor by changing the word 'cell' to 'value'. Now in script editor I get a list of my values back in groups of two, English and Spanish.

    However, when I try to run the following script (fragment) in Eggplant, I get the error that I mentioned earlier, "Expected "end" but found unknown token."

    Here is the eggplant script fragment:

    do AppleScript {{
    tell application "Microsoft Excel"

    open workbook workbook file name "Osiris:Users:steveo:Documents:Address Book Words.xls"

    set theRange to value of the used range of worksheet 1
    set newData to {}
    repeat with aRow from 1 to count of theRange
    ? set newRow to {}
    ? repeat with aCell from 1 to count of item 1 of theRange
    ? ? set newRow to newRow & value of cell aCell of row aRow of worksheet 1
    ? end repeat
    ? copy newRow to the end of newData
    end repeat
    get newData
    end tell
    }}

    put the result into fullData

    (* Add new card *)
    (*Click "m_file"*)
    Click (Text:item 1 of line 1 of fullData, TextSize:"13")
    ...

    Any thoughts as to what I am doing wrong here?

    Thanks again.

    SteveO
  • SenseTalkDougSenseTalkDoug ForumAdmin admin
    As far as I know, if the script compiles and runs in the AppleScript Script Editor, then it should work in Eggplant on the same machine. Try copying the text of the script directly out of Script Editor and pasting into your Eggplant script (if you did that already, try it again for good measure).

    If that doesn't work, I'm at a bit of a loss to understand why not. Did you do anything special in Script Editor to get it to work? For example, did you have to point out to it where Microsoft Excel was located?
  • Hi All,

    Please help me,

    I have one csv file that file contain parameters.

    Now, I would like to pass the parameters to the script.

    I mean to say copy the value of parameter 1 and paste in the script.

    Could please send the code..

    Thanks in advance,
    Vital U
Sign In or Register to comment.