Retrieving data/value form an Excel file by a specific sheet, column and row

Hi,

I am attempting to retrieve data from a specific Excel sheet, row and column.
I’ve attempted attempted what is being said here “(TEST DATA) Define specific Excel Sheets to get Excel Values”, but I ran into errors. So I would appreciate it if someone can guide me or show me where I am going wrong.

The error I am getting Is on line number 29 which is:
“Multiple markers at this line
- implements groovy.lang.Script.run)
- unexpected char: '' @ line 29, column 65.”

I’m assume I will get another error on line 38

Thanks in advance

Below is my code:

import com.kms.katalon.core.testdata.reader.ExcelFactory as ExcelFactory
import com.kms.katalon.core.testdata.ExcelData as ExcelData

  1. WebUI.openBrowser(‘’)

  2. WebUI.navigateToUrl(‘Login Page | Test Creator - TestYou’)

  3. //excelData = findTestData(‘EI TestData Files’).getValue(1, 1)

  4. Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(‘C:\Temp\TestDataFile.xlsx’, ‘emailAdress’, true)

  5. println(excelData)

  6. WebUI.click(findTestObject(‘Page_Login Page Test Creator - TestYou/a_Forgot Password’))

  7. WebUI.verifyElementPresent(findTestObject(‘Page_Forgot Password Page Test Maker - TestYou/span_Forgot your password’), 3)

  8. //WebUI.setText(findTestObject(‘Page_Forgot Password Page Test Maker - TestYou/input_Email (Entry Field)’), excelData)

  9. WebUI.setText(findTestObject(‘Page_Forgot Password Page Test Maker - TestYou/input_Email (Entry Field)’), excelData.getValue(2, 2))

  10. Thread.sleep(1000)

How about removing the “hyphen” from your element’s name and see if that does anything? Move to the OR and choose “Rename”. That will change the name in code as well.

“Creator - TestYou” could become “CreatorTestYou”

Do this to all of them if the error moves down a line afterwards. Or you can replace the hyphen with a period (that’s what I use, so I know it works), like:
“Creator - TestYou” could become “Creator.TestYou”

Also, when you move from one page to another, you should add:
WebUI.waitForPageLoad(5)

Hi grylion54

I did as you suggested (Renaming and adding WebUI.waitForPageLoad(5))
The renaming did not change the error previously mentioned on line 29

Also I noticed only after uploading that the numbering changed, but the error is in this line (For me it is line 29):
“Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(‘C:\Temp\TestDataFile.xlsx’, ‘emailAddress’, true)”

See updates below:

  1. import com.kms.katalon.core.testdata.reader.ExcelFactory as ExcelFactory

  2. import com.kms.katalon.core.testdata.ExcelData as ExcelData

  3. WebUI.openBrowser(‘’)

  4. WebUI.navigateToUrl(‘Login Page | Test Creator - TestYou’)

  5. //excelData = findTestData(‘EI TestData Files’).getValue(1, 1)

  6. Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(‘C:\Temp\TestDataFile.xlsx’, ‘emailAddress’, true)

  7. println(excelData)

  8. WebUI.click(findTestObject(‘Page_Login Page Test Creator_TestYou/a_Forgot Password’))

  9. WebUI.waitForPageLoad(5)

  10. WebUI.verifyElementPresent(findTestObject(‘Page_Forgot Password Page Test Maker_TestYou/span_Forgot your password’), 3)

  11. WebUI.setText(findTestObject(‘Page_Forgot Password Page Test Maker_TestYou/input_Email (Entry Field)’), excelData)

  12. //WebUI.setText(findTestObject(‘Page_Forgot Password Page Test Maker_TestYou/input_Email (Entry Field)’), excelData.getValue(1, 1))

  13. Thread.sleep(1000)

At the bottom of Katalon Studio is an area that displays the cursor position. On my version, it is to the right of “Smart Insert”, displaying line 71, column 50. You could use that to place your error.

image

It could be that you need to change C:\Temp\TestDataFile.xlsx to have double slashes, like:
C:\\Temp\\TestDataFile.xlsx

As an example:

gTestIdPathWay = "G:\\Katalon Test Cases\\Data Files\\TestId RT-WP-012.xlsx";
gReportPathway = ".\\Reports\\Screenshots\\RT WP 012\\";

Edit: the reason for this is the slash is used as an Escape character, so the double slash means not to use it as an Escape character but as a slash. An example of an Escape character is \n, the new line symbol.

Hi grylion54,

So the double backshlashes seems to have solved the issue which was on my line 29.

New Issue on line 40, which is returning a Null Point Exception (See image below).
“WebUI.setText(findTestObject(‘Page_Forgot Password Page Test Maker_TestYou/input_Email (Entry Field)’), excelData.getValue(2, 2))”

Below is an image of excel sheet where I am attemting to retrieve the email addres from
image

Thanks in advance,

Hi grylion54,

Thanks you for you assistance.

The double backslashes fixed my problem.

Regarding the null point exception reported previously, this is what I did and now the code is working

  • re-saving my Excel sheets (Autosave was not active)
  • re-saving my Kode on Katalon

For those that might have simular issues as I had, below is the simple code extract I used to verify the “Retrieving data/value form an Excel file by a specific sheet, column and row” that worked for me.

So now I am able to switch between excel sheets by changing, in my case, the sheet named “email Address” to any of the other sheet names. (line 3 in code below)
and between (col number, row number) in my case code line 7 where the column is 2 and the row is 1 (2, 1) to any other applicable row and column.

Additional information.
The Excel file I am using, is not added under Katalon > Data Files it is only being referenced in the code itself.

CODE EXTRACT
A. import com.kms.katalon.core.testdata.reader.ExcelFactory as ExcelFactory
B. import com.kms.katalon.core.testdata.ExcelData as ExcelData

  1. WebUI.openBrowser(‘’)
  2. WebUI.navigateToUrl(‘Login Page | Test Creator - TestYou’)
  3. Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(‘C:\Temp\TestDataFile.xlsx’, ‘emailAddress’, true)
  4. WebUI.click(findTestObject(‘Page_Login Page Test Creator_TestYou/a_Forgot Password’))
  5. WebUI.waitForPageLoad(5)
  6. WebUI.verifyElementPresent(findTestObject(‘Page_Forgot Password Page Test 7. Maker_TestYou/span_Forgot your password’), 3)
  7. WebUI.setText(findTestObject(‘Page_Forgot Password Page Test Maker_TestYou/input_Email (Entry Field)’), excelData.getValue(2, 1))
  8. Thread.sleep(1000)

Results:

1 Like