Polling data from Excel spreadsheet and testing until no more rows

Eggplant Functional
, ,
1

Ok, I might be drowning in a glass of water (Worst feeling, right?)… Feel like I keep getting so close to the answer, but then can’t seem to find the right order for the code.

In short, I have an xlsx file with a list of 100s of items (Single column, pretty vanilla so far).
What I want to accomplish is as follows:

Grab A1 from xlsx > place into variable MyTestValue
Run Test step 1 with MyTestValue
Run Test step 2 with MyTestValue
Run Test step 3 with MyTestValue
Log results of testing
Search for next xlsx line (Say A2)
Return to Test step 1, but with the new test value
…And so on, until all the xlsx values have been used (say A105).

I am able to set/put the file contents, but so far not to be able to use the first value to run a test, and then repeat the same test with subsequent values of the same xlsx.

Thanks for your feedback. :slight_smile:

2

Hi Mario,
Here is my go-to, with a few possible modifications. We can also discuss via phone; I think that Frankie is trying to set up a call.

params ScriptID

If ScriptID="" then
	Ask "What script would you like to run?" with "REG-1" title "Script Number" message "Type script number in 3 letter one dash one digit format"
	Set global myScriptID to IT
Else
	Set global myScriptID to ScriptID
End if

log "My files in my resource path are:"&&the files of resourcepath()
put "data_"&global myScriptID&".xlsx" into datafilename
Log "My data is contained in"&&datafilename


Set global myConnectionString to (type:"excel",file:ResourcePath(datafilename),writeable:Yes)
set global RecordCount to the number of records of global myConnectionString
Log "There are"&&global RecordCount&&"records in my Excel"

put the records of global myConnectionString into AllMyRecords

Set RecordCountCounter = 1
Repeat while RecordCountCounter <= global RecordCount
	Set global CurrentPatient to the record of global myConnectionString where _pk_ = (RecordCountCounter + 1000)
	If global CurrentPatient.Gender = "M" then
		Set global CurrentPatient.PossessivePronoun to "His"
		Set global CurrentPatient.Appelation to "Mr."
		Set global CurrentPatient.Pronoun to "him"
	Else
		Set global CurrentPatient.PossessivePronoun to "Her"
		Set global CurrentPatient.Appelation to "Ms."
		Set global CurrentPatient.Pronoun to "her"
	End If
	Log global CurrentPatient.PossessivePronoun&&"name is"&&global CurrentPatient.Appelation&&global CurrentPatient.LastName&", but you can call"&&global CurrentPatient.Pronoun&&global CurrentPatient.FirstName&"."
	Set RecordCountCounter=RecordCountCounter+1
End Repeat

Hope this helps,
Dave

3

Hi Dave, thanks for the reply, adapting to my script right now. Got note from Francesca, have an internal meeting tomorrow re: Automation, and will set something up directly after that (want to make sure I’ve covered all my newbie questions first, then trouble you guys with the rest :stuck_out_tongue: ).

Thanks for the great responsiveness!

–M

4

Was able to review the code, what I am trying to do is more along running a script where I can check inventory of clothes of different colors, where the colors are in a table. For instance:

xlsx file contains following values-
Red
Blue
Green
Brown
(And a hundred more colors)

Then the test script would be: (Bolded/Italicized where I’m struggling specifically)

Load first value of the xlsx
Open website x
Check for inventory of clothing with color from xlsx
capture snapshot of response/save
Reset/close website
Load next value of xlsx
go back to step where website x opens, repeat process but with Color 2, then 3, etc.

My challenge is in setting up the xlsx values so that it goes down the list, and then having the script repeat, ala “Goto line 10” from the old BASIC days :stuck_out_tongue:

I did see Dave’s script that the solution to this is likely in setting up a global variable for the table, and then another with the actual value for that specific round of testing. Thoughts?

Thanks always for the help to new Eggplant users :slight_smile:

5

Hi Mario,

Your Repeat loop would likely look like this:

Repeat while RecordCountCounter <= global RecordCount
	Set global CurrentColor to the record of global myConnectionString where _pk_ = (RecordCountCounter + 1000) -- changes records with each loop
        LaunchWebsite myWebsite
        NavigateWebsite
        CompareDatabaseWithWebsite CurrentColor
	CloseBrowser
	Set RecordCountCounter=RecordCountCounter+1 -- moves to next row in Excel
End Repeat
1 Like
6

Ha. I was drowning in a glass of water. Thank you sir!