# Polling data from Excel spreadsheet and testing until no more rows

**URL:** <https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042>\
**Category:** Eggplant Functional\
**Tags:** excel, sensetalk, eggplant-functional\
**Created:** [October 5, 2022, 2:49pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042 "2022-10-05T14:49:15Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![MJ\_1998661](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/m/e79b87/32.png) [@MJ\_1998661](https://forums.eggplantsoftware.com/u/MJ_1998661)\
**Post date:** [October 5, 2022, 2:49pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042/1 "2022-10-05T14:49:15Z")

</div>

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

---

<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:** [October 5, 2022, 3:58pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042/2 "2022-10-05T15:58:47Z")

</div>

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.

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

---

<div class="post-metadata">

**Author:** ![MJ\_1998661](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/m/e79b87/32.png) [@MJ\_1998661](https://forums.eggplantsoftware.com/u/MJ_1998661)\
**Post date:** [October 5, 2022, 4:22pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042/3 "2022-10-05T16:22:04Z")

</div>

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 😛 ).

Thanks for the great responsiveness!

–M

---

<div class="post-metadata">

**Author:** ![MJ\_1998661](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/m/e79b87/32.png) [@MJ\_1998661](https://forums.eggplantsoftware.com/u/MJ_1998661)\
**Post date:** [October 6, 2022, 1:55pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042/4 "2022-10-06T13:55:08Z")

</div>

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 😛

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 🙂

---

<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:** [October 6, 2022, 2:29pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042/5 "2022-10-06T14:29:53Z")

</div>

Hi Mario,

Your Repeat loop would likely look like this:

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

```

---

<div class="post-metadata">

**Author:** ![MJ\_1998661](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/m/e79b87/32.png) [@MJ\_1998661](https://forums.eggplantsoftware.com/u/MJ_1998661)\
**Post date:** [October 6, 2022, 2:45pm UTC](https://forums.eggplantsoftware.com/t/polling-data-from-excel-spreadsheet-and-testing-until-no-more-rows/8042/6 "2022-10-06T14:45:00Z")

</div>

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