# Reading rows from excel sheet and passing as parameters.

**URL:** <https://forums.eggplantsoftware.com/t/reading-rows-from-excel-sheet-and-passing-as-parameters/3739>\
**Category:** Eggplant Functional\
**Created:** [July 19, 2017, 3:54am UTC](https://forums.eggplantsoftware.com/t/reading-rows-from-excel-sheet-and-passing-as-parameters/3739 "2017-07-19T03:54:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nandurkar](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nandurkar](https://forums.eggplantsoftware.com/u/nandurkar)\
**Post date:** [July 19, 2017, 3:54am UTC](https://forums.eggplantsoftware.com/t/reading-rows-from-excel-sheet-and-passing-as-parameters/3739/1 "2017-07-19T03:54:30Z")

</div>

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.

```auto

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

---

<div class="post-metadata">

**Author:** ![drozwick](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/d/ba8739/32.png) [@drozwick](https://forums.eggplantsoftware.com/u/drozwick)\
**Post date:** [July 19, 2017, 6:39am UTC](https://forums.eggplantsoftware.com/t/reading-rows-from-excel-sheet-and-passing-as-parameters/3739/2 "2017-07-19T06:39:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![DaveHester](https://forums.eggplantsoftware.com/user_avatar/forums.eggplantsoftware.com/davehester/32/104_2.png) [@DaveHester](https://forums.eggplantsoftware.com/u/DaveHester)\
**Post date:** [July 25, 2017, 10:13am UTC](https://forums.eggplantsoftware.com/t/reading-rows-from-excel-sheet-and-passing-as-parameters/3739/3 "2017-07-25T10:13:48Z")

</div>

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.

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

```

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

```auto
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.

```auto
put the records of myScriptData into global testpatients

```

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

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

```auto
repeat with each item global testpatient of global testpatients

```

```auto
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.

```auto
end repeat

```

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