Reading rows from excel sheet and passing as parameters.

Eggplant Functional
1

Hi All,

I want to read test data from a excel sheet and populate the form in my script.

I have absolutely no idea how to do it, can someone please help me…

Attached is the test data sheet. [have zipped it ]

I want to send data from each row to FillDetails.
Right now I’m using property list to do something similar, here is the code I want to use it in.


My current script  
-----------------------------------------------------------
Script - AddDetails 
-----------------------------------------------------------


put ((servername"testServer",ip"10.10.10.1",username"testuser", passwd"PasswdForTestUser"),(servername"testServer",ip"IncorrectIP",username"testuser", passwd"PasswdForTestUser"),(servername"testServer",ip"10.10.10.1",username"IncorrectUsername", passwd"PasswdForTestUser") ,(servername"testServer",ip"10.10.10.1",username"testuser", passwd"IncorrectPasswd")) into credslist 

repeat with each creds of credslist 
FillDetails creds 
end repeat 

(*
Some other stuff
*)

-----------------------------------------------------------
Script - FillDeatils
-----------------------------------------------------------

Click ("AddCluster//AddCluster_ServerName")
TypeText creds.servername

Click ("AddCluster//AddCluster_LoginNode")
TypeText creds.ip

Click ("AddCluster//AddCluster_Username")
TypeText creds.username

Click ("AddCluster//AddCluster_Password")
TypeText creds.passwd

(*
Some other stuff
*)

Thank you
Nandurkar

2

here is the location of the documentation on how to read excel in
http://docs.testplant.com/ePF/SenseTalk/stk-excel-file-interaction.htm?Highlight=excel

we use this code to load a line into separate universal variable so we can pass to other scripts:

To fillVar Excelfile, ExcelWS, lineNumber
// Check if the file exists
If file ResourcePath(Excelfile) exists then
// Log the path and name
Log ResourcePath(Excelfile)
// set the variable to the excel work book
set caseBook to Workbook(ResourcePath(Excelfile))
// Log the variable
Log caseBook
// set the variable to the worksheet
set worksheet1 to caseBook.Worksheet(ExcelWS)
// log the name of the worksheet and the path
Log worksheet1
// this is getting the header row
put cellRange(1) of worksheet1 into columnsBasic1
// log the data found in row 1
Log columnsBasic1
// put the values from row 1 into a list
put item 1 of columnsBasic1 into c1of1
// log the list set above
log c1of1
// put the values found in the row defined by the passed variable lineNumber into another variable
put cellRange(lineNumber) of worksheet1 into columnsBasic2
// log the data in that row
Log columnsBasic2
// put the the first value in that row into the list
put item 1 of columnsBasic2 into c1of2
// log the list
log c1of2
// log the number of items the header / row 1 variable
Log the number of items in columnsBasic1
// now repeat until the end of items in the list
repeat with n=1 to the number of items in c1of1
// put the data found in the list into the variable
put item n of c1of1 into ItemName
// put the data found in the list into the variable
put item n of c1of2 into ItemValue
// Log the Name
Log ItemName
// Log the Value
Log ItemValue
// This is creating the universal variables by creating a string to execute with the Do
Do “put” && quote & itemValue & quote && “into Universal” && itemName – assign the value to the variable
end repeat

End if

End fillVar

the call to the above script looks like this:
VariableAssignment.fillVar(“PatientData.xlsx”),(""“LabOrderPatients”""), (CountDeFile)

the header row for excel can not have spaces.

I use the repeat function to loop through all of the rows in the spreadsheet

3

My code is quite a bit slimmer:

// Assumption is that your spreadsheet is formatted with a header row which contains your per patient variables e.g. MRN, FirstName, LastName

// ZephyrID is a script number in our repository. Our Excel files incorporate the script ID into the name of the file. Presumes Excel files are stored in your Resource folder.

put "data_" & global ZephyrID & ".xlsx" into datafilename

// Set the specified variable, myScriptData, to store the contents of the Excel file

set myScriptData to (type:"excel", file:ResourcePath(datafilename))

// This fetches all records from the myScriptData variable and places them into the testpatients variable. Note this variable name is plural to distinguish it from the record-by-record variable. Plural is not necessary, but different variable name is.

put the records of myScriptData into global testpatients

// counts records in Excel file. Used only for verifying that entire file was read.

put the number of records of myScriptData into global recordcount

//Call each record one at a time and perform a scripted activity. In this case log the patient’s MRN. Note the two variables: testpatient and testpatients

repeat with each item global testpatient of global testpatients

log global testpatient.mrn

– This is the secret sauce. .mrn represents the mrn column of the spreadsheet. You can call the value of any column in the record by the variable you define in the header.

end repeat

– No EOF or incrementing necessary. When reading from an Excel database, the script is aware of which record is last.