How to read data from excel and enter them in web application regardless of how many rows of data are present in excel

Srabanti_Datta_Prama · August 5, 2017, 7:11pm

Hi, I am using the Katalon studio and it is new tool for me. It is nice tool. I am automating a web application and have a query for it. Query is : I have to enter datas into the fields of the web application by reading datas from the excel. I could do it if I pass the column and row numbers in Groovy code. But I need to write the groovy code in such a way that how many rows of data are there for the required fields it should be able to read and enter the data into the application.Need the groovy code for the query to proceed with the automation.

Srabanti_Datta_Prama · August 6, 2017, 3:19pm

Thanks for the video !! In the video it is shown that in PrepareData testcase the column and row numbers, i.e (1,1), (2,1) and (3,1) are there in data.getValue(1,1), data.getValue(2,1), data.getvalue(3,1) . But in case I have 10 sets of values i.e 10 usernames and passwords then how to write the groovy code? I do not want to hard code the column and row numbers in the code. It should be in such a way that no matter how many rows of data are there I do not have to use row and column numbers, i.e it should iterate for all rows of data from excel and enter into the web application. Can you please help me with this query?

Hanh_Tran · August 6, 2017, 12:54am

Please refer to this video: https://www.youtube.com/watch?v=imko12p-qrg.

vilmante · December 15, 2017, 2:13pm

i have the same question.

Hiral · July 2, 2018, 9:53pm

Hi,

I know this is old discussion and but I am trying to do exactly same.

Any suggestion/solution would be great help.

Thanks

Anthony · July 3, 2018, 5:51am

https://www.katalon.com/resources-center/tutorials/data-driven-testing/ does this do what you want?

Hiral · July 11, 2018, 2:09pm

Thanks Anthony,

I went through the data-driven tutorial.

I am trying to read data from excel and then select Correct selection from dropdown.

I have one test case where I have to enter user billing address data and there is dropdown for US state. In excel I have all the data that can be filled except state.

I am trying to read the data from excel and depending on state(from excel sheet), test case would select correct state from dropdown.

Anthony · July 11, 2018, 10:40pm

Hiral said:

Thanks Anthony,

I went through the data-driven tutorial.

I am trying to read data from excel and then select Correct selection from dropdown.

I have one test case where I have to enter user billing address data and there is dropdown for US state. In excel I have all the data that can be filled except state.

I am trying to read the data from excel and depending on state(from excel sheet), test case would select correct state from dropdown.

Do you know the ids of the states in the drop down list? If not can you get them? If you can and you can get your excel data as variables then you could use a long if elseif sort of script.

Something along the lines of:

if (state==("Texas")) { WebUI.selectOptionByIndex(findTestObject('statedropdown'), 1)} 
    else if (state=="California") { WebUI.selectOptionByIndex(findTestObject('statedropdown'), 2)} 
    //and repeat until
    else {println "Unknown State, throw an error here!"}

Hope that helps

https://www.katalon.com/resources-center/tutorials/how-handle-drop-down-menu/
http://www.sanspantalones.com/2018/02/10/conditional-statements-if-else-if-in-katalon-studio/

Hiral · August 9, 2018, 2:23pm

Thanks Anthony,

Your solution is working but there are 61 if and else statements and I am getting an error “Method code too large”.

Is there any other way to achieve this?

Appreciate for your time.

I get this error

Code sample:

if (state == 'Alabama') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 1)} else if (state == 'Alaska') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 2)} else if (state == 'American Samoa') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 3)} else if (state == 'Arizona') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 4)} else if (state == 'Arkansas') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 5)} else if (state == 'California') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 6)} else if (state == 'Colorado') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 7)} else if (state == 'Connecticut') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 8)} else if (state == 'Delaware') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 9)} else if (state == 'District of Columbia') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 10)} else if (state == 'Federated States of Micronesia') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 11)} else if (state == 'Florida') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 12)} else if (state == 'Georgia') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 13)} else if (state == 'Guam') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 14)} else if (state == 'Hawaii')

Screen Shot 2018-08-09 at 9.22.02 AM.png

Anthony · August 9, 2018, 10:19pm

Hiral said:

Thanks Anthony,

Your solution is working but there are 61 if and else statements and I am getting an error “Method code too large”.

Is there any other way to achieve this?

Appreciate for your time.

I get this error

Code sample:

if (state == 'Alabama') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 1)} else if (state == 'Alaska') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 2)} else if (state == 'American Samoa') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 3)} else if (state == 'Arizona') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 4)} else if (state == 'Arkansas') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 5)} else if (state == 'California') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 6)} else if (state == 'Colorado') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 7)} else if (state == 'Connecticut') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 8)} else if (state == 'Delaware') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 9)} else if (state == 'District of Columbia') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 10)} else if (state == 'Federated States of Micronesia') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 11)} else if (state == 'Florida') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 12)} else if (state == 'Georgia') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 13)} else if (state == 'Guam') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 14)} else if (state == 'Hawaii')

Oh wow never seen that before, can try nested if statements with some sort of control variable, may be some body else can give a much cleaner way to do it but I am thinking of something like this…

def boolean control = true
def choice = "red"
if (control == true) {
    if (choice == 'blue') {
    println ("It is " + choice)
    control =  false
    } else { println ("It is not blue")}
}
if (control == true) {
    if (choice == 'red') {
    println ("It is " + choice)
    control =  false
    } else { println ("It is not red")}
}
if (control == true) {
    if (choice == 'green') {
    println ("It is " + choice)
    control =  false
    } else { println ("It is not green")}
}
if (control == true) {
    if (choice == 'yellow') {
    println ("It is " + choice)
    control =  false
    } else { println ("It is not yellow")}
}

Hope that is clear what I am doing and helps you, the first if will not be evaluated as true once you have already found what you want and changed the control variable and each if statement is now its own thing.

Edited to add example using your code…

def boolean control = true
if (control == true) {
    if (state == 'Alabama') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 1)}
    control =  false
}
if (control == true) {
    if (state == 'Alaska') {	WebUI.selectOptionByIndex(findTestObject('statedropdown'), 2)}
    control =  false
}

Anthony · August 10, 2018, 4:50pm

It would be much cleaner to loop through your excel objects than write millions of nested if`s like I have shown here.

Hiral · August 14, 2018, 8:46pm

Thank you, Anthony, for your time.
Your suggested solution is definitely much clear approach

deepasaravanan1603 · October 17, 2019, 12:21pm

Hi,
This is Deepa,
I have integrated an excel file to my test case which is required, but I have a doubt in executing a logic. My query is: How can I get data from excel, without duplicate values. For eg., If I am running the script for a first time, It should take the value of Row 1 and Column 1, Same as., When I am running the script for a second time, It should take the value of Row 2 and Column 1 and it goes in an infinite times.
Is there any way to do this in our script?
Can anyone please guide me regarding this?
Thanks.

bhaveshbhai.patel · August 31, 2021, 2:05am

Hi @deepasaravanan1603
I have same query regarding duplicating values as yours! Did you find solution so far plz?
Thanks

Topic		Replies	Views
Import a List o users from excel to web application using Katalon Archive	1	947	November 1, 2017
Retrieving data/value form an Excel file by a specific sheet, column and row Tips & Tricks katalon-studio	5	1806	November 28, 2022
How to get data from excel for each iteraction from an example. please help me with an example Archive	5	2994	October 25, 2017
How To Create Repeat Looping But Read Next Row Katalon Studio katalon-studio , web-testing	1	855	February 8, 2022
Excel data file - Value reading to List Katalon Studio katalon-studio , web-testing	7	2390	January 11, 2022

How to read data from excel and enter them in web application regardless of how many rows of data are present in excel

Related Topics