How to Read the particular column(excel) value in Eggplant

Hi,

I have Gone through the both the links.

http://www.redstonesoftware.com/phpBB2/viewtopic.php?t=151
http://www.redstonesoftware.com/phpBB2/viewtopic.php?t=1147

But for my application I want to read the particular column value on the basis of header and assign the value to a variable.To write the value of that variable to find .

For Example in my Excel file I am having two fields request_name and request_desc, by reading only the request_name header want to find the request information.

I have enclosed Excel file in the attachment. Please resolve this issue.

Here is an expanded version of the script given in the first link above, showing how you can access the data in the records it produced:

do AppleScript {{ 
tell application "Microsoft Excel" 
open "Macintosh HD:Users:doug:Desktop:Data.xls" 
get value of the used Range of worksheet 1 
end tell 
}} 
put the result into fullData -- a list of lists 
put "Raw Data (a list of lists):"
put fullData

-- 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 

put return & "Records (a list of property lists):"
put recordList

put return & "Names and Descriptions:"
repeat with each record in recordList
	set (name, description) to record's (request_name,request_desc)
	put
	put "Name: " & name
	put "Description: " & description
end repeat

Using the data file you posted (be sure to set the path correctly in the AppleScript for your own file) and changing “usedRange” to “used range” I got the following output:

Raw Data (a list of lists):
((request_name,request_desc),(Test1,Checking 1),(Test2,Checking 2),(Test3,Checking 3),(Test4,Checking 4),(Test5,Checking 5),(Test6,Checking 6))

Records (a list of property lists):
((request_desc:“Checking 1”, request_name:“Test1”),(request_desc:“Checking 2”, request_name:“Test2”),(request_desc:“Checking 3”, request_name:“Test3”),(request_desc:“Checking 4”, request_name:“Test4”),(request_desc:“Checking 5”, request_name:“Test5”),(request_desc:“Checking 6”, request_name:“Test6”))

Names and Descriptions:

Name: Test1
Description: Checking 1

Name: Test2
Description: Checking 2

Name: Test3
Description: Checking 3

Name: Test4
Description: Checking 4

Name: Test5
Description: Checking 5

Name: Test6
Description: Checking 6

Hi,

Thanks for the code.It helped me a lot. Is we can check the content of the excel file .

Example in excel file mentioned above request_name and request_desc value are not exist in the excel file then it should log the information " No data to process" and ask for the alternative script to write to the excel file.

If a particular cell in the spreadsheet is empty, then your variable should be set to be empty. You can easily check for that. In the final loop in the example above, the code might look like this:

if name is empty or description is empty then
    logWarning "No data to process"
end if

Hi,

I have tried that earlier.But if the excel file content is empty with only header it is giving error.

STUnknownMessage:
ERROR: No Such Function: ‘request_name’

Example:Mydata.xls is where we are wirting the data for request creation using abc.suite script.Then we are using the same excel to find the request
created using xyz.suite. If user runs the xyz.suite then there is no content
so it should ask the user to run abc.script.

put return & “Names and Descriptions:”
repeat with each record in recordList
set (name, description) to record’s (request_name,request_desc)
put
put "Name: " & name
answer name
put "Description: " & description
if name is empty or description is empty then
logWarning “No data to process”
answer “empty”
end if

If your spreadsheet has only a header row and no data below it, then fullData will contain only 1 item (a list of the headers). So after deleting the header row, you can check whether fullData is empty:

do AppleScript {{ 
tell application "Microsoft Excel" 
open "Macintosh HD:Users:doug:Desktop:Data.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 

if fullData is empty then
    logWarning "No data to process" 
    answer "empty" 
end if

Hi,

Thanks a lot for information it works…

But if I want to read and write from the excel using and pass the vale to eggplant TextType command to write /read during automation.

Here I am enclosing the file I want to make eggplant code that is included images to automate as a function with arguments. These arguments should get the value from the applescript reading value.

global miniteam_name , miniteam_desc

do AppleScript {{
tell application “Microsoft Excel”
open “Leopard:Users:rajan:Documents:Test.xls”
get value of the used Range of worksheet 1
end tell
}}

function create_miniteam miniteam_name , miniteam_desc

Click "s_File"
Click "s_New"
Click "c_Miniteam"

if ImageFound("s_MiniTeam") then
	Wait Until	ImageFound("s_wait")
	Click "s_Name"
	TypeText "MT" & now
	TypeText CommandKey, "A"
	TypeText CommandKey ,"C"
	put remoteClipboard() into miniteam_name
	
	Click "s_des"
	TypeText "MT" & now
	TypeText CommandKey, "A"
	TypeText CommandKey ,"C"
	put remoteClipboard() into miniteam_desc
end if
Click "s_File"
Click "s_save"
Click "s_ok"

Wait Until ImageFound("s_Loading")
Wait Until ImageFound("s_MT_AfterLoading")
Click "s_Cr_Close"

end function

do AppleScript merge of {{
tell application “Microsoft Excel”
open “Leopard:Users:rajan:Documents:Test.xls”
set value of range “A[[rowNum]]:[[lastColumn & rowNum]]” of worksheet 1 to [[values.standardFormat]]
close active workbook saving yes
end tell
}}

// I want avoid this part this operation as to be done in apple script
(*
put the result into data
put item 1 of data into headers – item 1 is the first row
repeat with each item of headers
insert value(it) after values – put values in same order as column names
end repeat
– get the letter for the last column of the destination range
set count to the number of items in values
put char count of “ABCDEFGHIJKLMNOPQRSTUVWXYZ” into lastColumn

set rowNum to the number of items in data + 1 – the next empty row

– store the values:
*)

I have enclosed the excel file in the attachment.Please provide me the information on this.

Hi,

I want do both read and write operation using apple script and the values passed should to be from eggplant like .

TypeText “MT” & now
TypeText CommandKey, “A”
TypeText CommandKey ,“C”
put remoteClipboard() into
miniteam_name

TypeText “MT” & now
TypeText CommandKey, “A”
TypeText CommandKey ,“C”
put remoteClipboard() into miniteam_desc

These two variables as to be passed to applescript to write to the particular
column .Please provide me the details.
Here is the excel file.
After running the script it has to writ the name description according to the header using apple script.
It has to execute according to the counter.

To transfer values from SenseTalk into an AppleScript script, use the merge function:

do AppleScript merge of {{
set name to "[[miniteam_name]]"
set desc to "[[miniteam_desc']]"
-- more applescript code here...
}}

The fragment shown above will assign the value of the miniteam_name and miniteam_desc variables from SenseTalk to the name and desc variables in AppleScript. The rest of your AppleScript code can then use those values however it needs them.

For example, to write these values into cells A3 and B3 of an Excel spreadsheet, the script might look like this:

do AppleScript merge of {{
set name to "[[miniteam_name]]"
set desc to "[[miniteam_desc']]"
-- write these values into an Excel spreadsheet
tell application "Microsoft Excel" 
open "Macintosh HD:Users:doug:temp:Sample.xls" 
set value of range "A3" of worksheet 1 to name 
set value of range "B3" of worksheet 1 to desc 
save 
end tell 
}}

For a more complex example, see the discussion here: http://www.redstonesoftware.com/phpBB2/viewtopic.php?t=1147

Hi,

Hey thanks for the information.It helps me a lot.javascript:emoticon(’:D’)

If I want to pass the variable from one function to another, in that what will be the syntax to pass the variables.

This is the case…

XYZ.script

global AccountName,AccountPass,tt

do AppleScript merge of {{
tell application “Microsoft Excel”
open “Macintosh HD:Users:rajan:Documents:test_Automation_Template.xls”
set AccountName to(get value of range(“D3”))
set AccountPass to(get value of range(“D4”))

end tell
return {AccountName,AccountPass}
}}

put the result into (AccountName,AccountPass)
start using “Login Dummy”

put functions.Login(AccountName,AccountPass)

log tt - that value from other function

do AppleScript merge of {{
tell application “Microsoft Excel”
open “Macintosh HD:Users:rajan:Documents:tetst_Automation_Template.xls”
set status to “[[tt]]”
set value of range “G3” to status

end tell
}}

ABC.script

global AccountName,AccountPass,tt

function Login AccountName,AccountPass,tt
log AccountName
log AccountPass
set tt to “FAIL”
log tt
return tt
end function

I want pass tt value FAIL to XYZ.script.

Please provide me the details to do.

Thanks in Advance…

The more usual (and generally preferred) way to pass values between scripts is to use parameters and return values. You’re already passing two parameters when you call

put functions.Login(AccountName,AccountPass)

(although if your script file is named ABC as you’ve shown it, this should be ABC.login… not functions.login)

You are also returning the value “FAIL”. To help make the use of passing parameters and returning values from functions clearer, here are slightly rewritten versions of the call and the function.

In XYZ.script, here’s how you might call the login function:

put ABC.Login(AccountName, AccountPass) into tt
log tt -- display the value returned by the Login function

Here’s the Login function in ABC.script:

function Login name, password
    log name
    log password
    set status to "FAIL"
    log status
    return status
end Login

The other way to pass values between scripts and handlers is to use global variables, but that requires declaring them within each handler. In your ABC script the global declaration was outside of the Login function. It would need to be moved into the function for the variables to be treated as globals there. Global variables are useful for information of a global nature that may not be convenient to pass to every script, but aren’t the best choice for a script like this one.