Srabanti Datta Pramanik
Katalon Apprentice
08/05/2017

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

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.

Upvote
Quote

Comments

  • Hanh Tran
    Katalon Ambassador
    08/06/2017
    Please refer to this video: https://www.youtube.com/watch?v=imko12p-qrg.
    Upvote
    Quote
  • Srabanti Datta Pramanik
    Katalon Apprentice
    08/06/2017
    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?
    Upvote
    Quote
  • vilmante
    Katalon Apprentice
    12/15/2017
    i have the same question.
    Upvote
    Quote
  • Hiral
    Katalon Apprentice
    07/02/2018
    Hi,

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

    Any suggestion/solution would be great help.

    Thanks
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    07/03/2018
  • Hiral
    Katalon Apprentice
    07/11/2018
    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.
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    07/11/2018
    edited July 11
    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/
    Upvote
    Quote
  • Hiral
    Katalon Apprentice
    08/09/2018
    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')
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    08/09/2018
    edited August 9
    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
    }
    Upvote
    Quote
  • Anthony
    Katalon Ambassador
    08/10/2018
    edited August 10
    It would be much cleaner to loop through your excel objects than write millions of nested if`s like I have shown here. 
    Upvote
    Quote
  • Hiral
    Katalon Apprentice
    08/14/2018
    Thank you, Anthony, for your time. 
    Your suggested solution is definitely much clear approach
    Upvote
    Quote
Sign In or Register to comment.