Refer data from excel sheet

kparekh · November 16, 2021, 8:32pm

Hello… I have my web element recorded by its position. This one is use for expanding rows. Its xpath is //tr[contains(td[4],‘test1’)]/td[2]/input

Now I want to set the data to be taken from excel sheet . I made a sheet which shows Risk category ID. Can someone help me how can I refer the data from my sheet which map to my web element’s xpath to change the value of ‘test1’?

kazurayam · November 17, 2021, 12:58am

You want a tutorial that covers 2 topics together: “Data-driven testing in Katalon Studio” and “Parameterised Test Object in Katalon Studio”. There are a few articles on the Internet that cover each topics separately, but unfortunately there seems to be none that covers these 2 topics integrated. Katalon Official doc does not cover it either.

Please provide

the HTML full source of your target web page. If you hesitate to make “the original” public due to some confidentiality reason, then please newly create a HTML which contains the same <table> element as the original so that you have no concern about it.
an Excel sheet which you want to use as input

I would request you to agree with exposing these materials to public.

Provided with these, I would be able to make a demo project for you.

kparekh · November 17, 2021, 10:31pm

@kazurayam. Hi…I checked both of the tutorials. Its better that to find a better way. I have attached both file which is only refer to above table. I can’t share more than that. Still let me know if you need more information.

Strange I cannot upload .xlsx file here so I give you a sc for it.

HTML code.txt (19.0 KB)

Thank you

kazurayam · November 18, 2021, 9:31am

I have created a sample project. You can download the zip of the project from the following page.

https://github.com/kazurayam/ks_forum_kparekh/releases/tag/0.1.0

kazurayam · November 18, 2021, 9:33am

I used the HTML file you shared. I made a CSV file based on your requirement.

Test Case script TC1:

import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject

import java.nio.file.Path
import java.nio.file.Paths

import com.kms.katalon.core.configuration.RunConfiguration
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI

// resolve URL of the HTML file 
Path projectDir = Paths.get(RunConfiguration.getProjectDir())
Path html = projectDir.resolve('Include/fixtures/page.html')
String url = html.toFile().toURI().toURL().toExternalForm()

// open the page in a browser
WebUI.openBrowser('')
WebUI.navigateToUrl(url)

// read the Data from the CSV file
TestData data = findTestData("sampleData")

// iterate over the rows in the table body
for (int i: 1..data.getRowNumbers()) {
	// find value of the 'ID' column
	String id = data.getValue('ID', i)
	if (id != null & id.length() > 0) {
		// look for the <input> element in the row which you are interested
		TestObject tObj = findTestObject("main/rgMasterTable_tbody_tr(td4_RiskCategoryID)_td2_input", ["ID":id])
		// make sure you found the element
		WebUI.verifyElementPresent(tObj, 5, FailureHandling.STOP_ON_FAILURE)
		// do whatever you want
		def name = WebUI.getAttribute(tObj, "name")
		WebUI.comment("ID=\"${id}\", name=\"${name}\"")
	}
}

// close the browser
WebUI.closeBrowser()

Locator of the TestObject:

//table[@id='ctl00_cphMain_RadGridRiskCategory_ctl00']/tbody/tr[contains(td[4],'${ID}')]/td[2]/input

When I ran the test case , it emitted the following messages in the console:

2021-11-18 18:19:37.772 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - ID="RiskID3", name="ctl00$cphMain$RadGridRiskCategory$ctl00$ctl04$EditButton"
2021-11-18 18:19:38.615 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - ID="RiskID1", name="ctl00$cphMain$RadGridRiskCategory$ctl00$ctl06$EditButton"
2021-11-18 18:19:39.393 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - ID="VCAT4", name="ctl00$cphMain$RadGridRiskCategory$ctl00$ctl08$EditButton"
2021-11-18 18:19:40.157 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - ID="VCAT3", name="ctl00$cphMain$RadGridRiskCategory$ctl00$ctl10$EditButton"

kparekh · November 18, 2021, 6:11pm

Awesome @kazurayam. It is working well. However, requesting more help, as this is a for loop it continuously follow all the data from sheet. But if I want first ID to picked up for some step and then second Id for another steps and so on…, could you pls guide me how can I perform this scenario?

kazurayam · November 18, 2021, 11:22pm

This doesn’t make sense to me. I am puzzled what you want to do.

Taking your words literally,

TC2:

import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject

import java.nio.file.Path
import java.nio.file.Paths

import com.kms.katalon.core.configuration.RunConfiguration
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI

// resolve URL of the HTML file 
Path projectDir = Paths.get(RunConfiguration.getProjectDir())
Path html = projectDir.resolve('Include/fixtures/page.html')
String url = html.toFile().toURI().toURL().toExternalForm()

// open the page in a browser
WebUI.openBrowser('')
WebUI.navigateToUrl(url)

// read the Data from the CSV file
TestData data = findTestData("sampleData")

// iterate over the rows in the table body
for (int i: 1..data.getRowNumbers()) {
	// find value of the 'ID' column
	String id = data.getValue('ID', i)
	if (id != null & id.length() > 0) {
		// look for the <input> element in the row which you are interested
		TestObject tObj = findTestObject("main/rgMasterTable_tbody_tr(td4_RiskCategoryID)_td2_input", ["ID":id])
		// make sure you found the element
		WebUI.verifyElementPresent(tObj, 5, FailureHandling.STOP_ON_FAILURE)
		// do whatever you want
		switch (i) {
			case 1:
				def name = WebUI.getAttribute(tObj, "name")
				WebUI.comment("ID=\"${id}\", name=\"${name}\"")
				break
			case 2:
				castMagicSpell(i)
				break
			default:
				WebUI.comment("I don't know what to do for i=${i}")
		}
	}
}

// close the browser
WebUI.closeBrowser()

def castMagicSpell(int index) {
	WebUI.comment("Hello, ${index}")
}

This emits:

2021-11-19 09:04:18.820 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - ID="RiskID3", name="ctl00$cphMain$RadGridRiskCategory$ctl00$ctl04$EditButton"
2021-11-19 09:04:19.258 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - Hello, 2
2021-11-19 09:04:19.778 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - I don't know what to do for i=3
2021-11-19 09:04:20.214 INFO  c.k.k.c.keyword.builtin.CommentKeyword   - I don't know what to do for i=4

Is this useful for you?

kparekh · November 19, 2021, 8:14pm

@kazurayam Sorry my bad to make you confuse. I want to say like this. Let’s say we have 10 rows of data in sheet. When we execute the loop it run all the data one by one starting from i=1. Is it possible that it perform i=3 then 5 then 9 only? Just a random from a sheet that we can execute. Or I need to delete all that rows from the sheet which I don’t want to execute.

grylion54 · November 19, 2021, 8:46pm

You would need to tell the computer which rows to work on for any test run.

Perhaps you could add a column to your data in sheet that you could use to indicate which rows you wanted to run for a specific test run and then add an IF condition to check if the row has your indicator. Only let those with an indicator go through.

An alternative is to just use a Random value to pick the rows you get.

Another alternative is to put into another spreadsheet the rows in your data in sheet you want to use and either read the rows one at a time until there is a blank or the whole list of rows at once (in a loop into a list until there is a blank) before starting your testing.

kazurayam · November 20, 2021, 12:58am

Of course, grylion54’s suggestion would work. But it may not be enough in some cases.

@kparekh may say

“The Excel file is owned by others. The file is frequently changed by him. It is given to me as a fixture. I am not authorised to change it. So I can not add a column for my purpose. It contains too many rows (e.g, 6000 rows). It takes too long time for the test to go through all rows. I want some smaller portion of sheet rows are automatically selected. Say, 0.1% amongst 6000 rows, or 3%, or 10% …”

… once I have ever had such an objective.

kazurayam · November 20, 2021, 1:55am

No, I guess, I misunderstood the original requirement.

@kparekh

I suppose,

you want to iterate over the rows (<tr>) in the table (<table>) in the web page,
for each table rows, you want to select the value of “Risk Category ID”. For example, “RiskID3” out of the 2nd <tr>
you want find out a row of “RiskID3” in the Excel sheet.
If found, extract some valuable data out of the Excel sheet, and copy it into somewhere in the web page.

Am I right?

If my understanding is right, I would ask you:

please give us a new Excel sheet (or it’s screenshot) that contains a row of “some valuable data” which is to be copied from Excel into web page.
please specify into which HTML element you want to copy the “some valuable data” into.

I have a question to @kparekh

Do you want to select i=something out of rows in the <table> in the web page? Or, do you want to select i=something out of rows in the Excel sheet?

If you reply “out of Excel sheet”, then it will puzzle me again.

kparekh · November 22, 2021, 4:00pm

Hi @kazurayam. No I don’t want to copy/paste this data anywhere. The advice of @grylion54 helps.

I want i= something from excelsheet. As suggested by grylion, I have to tell computer which rows I want.

kparekh · November 22, 2021, 10:07pm

@kazurayam Can you pls give me one example with any of the @grylion54’s suggestion.

Let say I added all the risk category ID from the sheet having same data as above. Now I click on edit icon beside the 3rd data - KPtest which is also on same position in the sheet(actually on 4th row as header on the top). Then click on delete icon of record KP45.

Topic		Replies	Views
Web data fill up in box from excel data Katalon TestOps web-testing	17	518	February 16, 2023
Import Test Data into Web Application Testing Archive katalon-studio	6	4754	August 8, 2022
Read test object from excel in katalon studio Archive katalon-studio , web-testing	4	863	August 8, 2022
Format for using data in Excel spreadsheet Archive excel , katalon-studio , web-testing	4	1135	August 8, 2022
Excel data fetch in katalon Archive integrations , katalon-studio	1	1578	January 7, 2019

Refer data from excel sheet

Related Topics