how to verify the spread sheet column value using Egg Plant

Hello guys…

I’ve a bunch of jobs in the spread sheet and i need to verify a particular column in the spreadsheet for all the jobs whether it contains the specified value or not. And i dont even have the count of jobs…

Please help me out to automate this scenario using Egg Plant…

Thanks & Regards…

If the spreadsheet containing the data is Excel running on the Eggplant machine, you can use AppleScript to access it, as described in this example. Basically, here’s the code that reads the spreadsheet (depending on the version of Excel you may need to change “usedrange” to “used range” as two words):

do applescript {{
tell application "Microsoft Excel"
	open "Macintosh HD:path:to:your:spreadsheet.xls"
	get value of the usedrange of worksheet 1
end tell
put the result into fullData -- a list of lists 

If the spreadsheet is not Excel on the Mac you can’t use AppleScript to access it, so you’ll need to export the data in CSV (comma-separated value) format. Then you can use code like this to read the CSV file and split it into a list of lists:

set fullData to ()
repeat with each line of file "/path/to/datafile.csv"
    insert it split by comma nested into fullData
end repeat

If items in the file are separated by tabs rather than commas, change the word “comma” to “tab”.

Either way, fullData is now a list, with one item per row. Each of those items is also a list, with one item per column. So, to verify that all of the values in column 3 are numbers between 1 and 7, for example, you could now do this:

repeat with each item of fullData
    if item 3 of it is not between 1 and 7 then
        logError "Invalid data in column 3"
    end if
end repeat

Thanks for the response…

Here i dont have the option to export my spread sheet data into CSV file. I forgot to mention one thing …i.e. am running a webapplication on IE 7.0 so my spread sheet is displayed in the browser.

Am trying to verify my data by capturing the image on the particular column and verifying whether that image is displayed under the column or not. Here also i’ve another problem i.e. i’ve a bunch of items displayed in the spreadsheet in more than a page and i’ve to click on the navigation link to go to the next page. Hence i dont know the count of items displayed in the spreadsheet.

Is there any way to figure it out???

I’m trying to use the objects concept to verify my data in the spread sheet.

Can any one help me how to access the list of items of a particular column using Eggplant…

OK, just tried this as a user first: click on a spreadsheet in a browser (which should select the column), and then type control/command c (PC/Mac). Then try to paste. Does this work for you? On my online spreadsheet, each cell was seperated by an “0d” character, so I have to split it with each <0d> character (see below) to parse.

So if I wanted to to this in a test, grabbing all the data from column F, for example, I’d do:

Click “F” # pre-captured image, or use the TiG if it can match the font
Typecommand “c” # Mac, use control c for PC
put remoteClipboard() into myData

split myData by <0d>
repeat with each item of myData
# do something here
log it
end repeat