Reading rows from excel sheet and passing as parameters.

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"",username"testuser", passwd"PasswdForTestUser"),(servername"testServer",ip"IncorrectIP",username"testuser", passwd"PasswdForTestUser"),(servername"testServer",ip"",username"IncorrectUsername", passwd"PasswdForTestUser") ,(servername"testServer",ip"",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


  • drozwickdrozwick Member
    here is the location of the documentation on how to read excel in

    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
  • DaveHesterDaveHester Member ✭✭
    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.
Sign In or Register to comment.