Katalon Studio

Determine if a Spreadsheet Cell contains Numeric or Text data

While working with some text data from a spreadsheet, I needed to read different kinds of SKU data. In some cases it would be a number such as 1313, while in others it might be a mix of text and numbers such as OU812.

Reding the value is normally done with .getNumericCellValue() or .getNumericCellValue(), but what happens when you don't know what the next value would be?

To overcome that, it's possible to the use .getCellType() and adjust the read based on type. Using some previous code, reading values from a spreadsheet and populating a Global List would look like the following:

for (loop = 1; loop <=rowCount; loop++) {
    //Assign spreadsheet columns to variables
    String cellType=sheet.getRow(loop).getCell(0).getCellType()
    //Determine if the cell contains Text or Numeric data
    if (cellType=="1"){
        String cellTextValue=sheet.getRow(loop).getCell(0).getStringCellValue()
        GlobalVariable.inventorySKU[loop]=cellTextValue
    }else{
        long cellIntValue=sheet.getRow(loop).getCell(0).getNumericCellValue()
        GlobalVariable.inventorySKU[loop]=cellIntValue
    }
}

In this case, 1 is String and 0 is Integer or Numeric. Based on the length of the SKU, I used long to store the value.

Additionally, in order to use the Integer value within a form, it needs to be converted to a String using:
String.valueOf(GlobalVariable.inventorySKU[loop])) such as:

WebUI.setText(findTestObject('Object/input-SKU'), String.valueOf(GlobalVariable.inventorySKU[loop]))

Apache POI – Cells

Other articles of interest:

Adding entries to an open Excel spreadsheet during runtime

When running the API test for several thousand entries, I noted that matching up an invalid request with the correct source data could be a needle in the haystack affair. Instead, it would be better to record any errors right alongside the source data in the XLSX spreadsheet file. That can be done by writing to a new cell during runtime.

The process is only a couple of additional lines of code.

To start, a new file needs to be opened using:

FileOutputStream outFile = new FileOutputStream(new File("//Postman Testing//Data Files//pricing.xlsx")

Data can then be written to the sheet using, createCell(5).setCellValue():
The .createCell(5) refers to column 5 in the spreadsheet. I'm using columns 0-4 for source data.

sheet.getRow(loop).createCell(5).setCellValue("ERROR: Failure Message on pricing request " + loop + " " + failure_message)

At the end, all data needs to be written to the workbook:

workbook.write(outFile)

And like other files, it needs to be closed when finished:
outFile.close()

During runtime, if an error is encountered, it is written inline with the source data. When the pricing.xlsx file is opened, the error message and data used to replicate the error is all in one place.

Other articles of interest:

Create a new and random UUID for an API call

When working with an API call, it may be necessary to create a new and valid UUID. This can actually be accomplished in a single line of code.

def order_id = UUID.randomUUID().toString()

This can be sent to the API object as a variable

mydate = new Date()
def order_id = UUID.randomUUID().toString()

//Initialize Order
initialize_order = WS.sendRequest(findTestObject('Initialize Order',
    [('order_id') : order_id,
    ('date') : mydate]))

Other articles of interest:

Getting started with Katalon Studio

You don't have automate an entire website to gain a benefit from automation. Even for yourself, there are plenty of ways Katalon Studio can help you save time. One of the main goals of automation is to perform repetitive tasks and emulate the same functions you do on a daily basis. And on one of the best places to start is filling in forms.

Entering form data is by no means a difficult job, but it's tedious and after a couple of passes, you really don't care what you type as long as the field isn't blank. Plus, it can be a waste of time. If it takes 5 minutes to fill in a form accurately and wait for it to save, that's roughly 10 forms an hour. And that's an hour that can be invested in something more meaningful.

With some simple automation code, filling in the entire form can be reduced to 30 seconds. Plus, it will be filled out correctly each time with predictable data you can search for and confirm exists. So instead of spending 5 minutes for each form, it would be possible to create 10 contacts in 5 minutes, or 10 times the amount of work with 1/10th the effort. And that savings of nearly 45 minutes can be put toward other projects like training, self-study, or for improving the automation code itself.

Filling in form data is one of the easier tasks to accomplish with automation. In most cases, the objects will follow a similar naming pattern such as:

css=input[name="contact_email"]
//div[@id='add-Contact']/form/input[7]

It would be a matter of copy/paste to change the xpath to the correct name, or use a variable for multiple inputfield objects.

The same should be true for entering text:

WebUI.setText(findTestObject('Object Location/inputfield-Contact-Contact Email'), 'user@domain.com')

After creating one SetText entry, copy/paste the rest, change the object and entered text and the job is done.

With a little bit of prep work, it would be possible to spend an hour or so and put together a script that will give a return on the time investment after the first couple of runs. And keep giving back time every time it's run.

Running the Test Case to create a new contact is something I run regularly. I can make dozens of entries while working on something else. That is a very big win in my opinion.

The core of the script could be some as simple as:

WebUI.setText(findTestObject('Object/inputfield-Contact-First Name Last Name'), 'Bob Smith')
WebUI.setText(findTestObject('Object/inputfield-Contact-Role or Title'), 'Customer Title)
WebUI.setText(findTestObject('Object/inputfield-Contact-Contact Phone'), '5552221112')
WebUI.setText(findTestObject('Object/inputfield-Contact-Contact Mobile'), '5553331113')
WebUI.setText(findTestObject('Object/inputfield-Contact-Contact Office'), '5554441114')
WebUI.setText(findTestObject('Object/inputfield-Contact-Contact Email'), 'user@domain')
WebUI.setText(findTestObject('Object/inputfield-Contact-Address Line'), '12 West Upper Court')
WebUI.setText(findTestObject('Object/inputfield-Contact-City'), 'Tempe')
WebUI.setText(findTestObject('Object/inputfield-Contact-State'), 'AZ')
WebUI.setText(findTestObject('Object/inputfield-Contact-Zip Code'), '85281')
WebUI.setText(findTestObject('Object/inputfield-Contact-Phone Number'), '3335551212')
WebUI.setText(findTestObject('Object/inputfield-Contact-Email Address'), 'user@domain.com')
WebUI.click(findTestObject('Object/btn-Contact-Save Button'))

Other articles of interest:

Create a Dynamic Object at Runtime

I'm not quite at the point to need to make an object outside of the Object Repository, but I've seen reference to it multiple times and wanted to put together a simple example because who knows when it might come up. There have been several comments about keeping the size of the Object Repository small so it's better to programmatically create a one-off object rather than commit it to the project.

Katalon Studio allows creating objects during runtime through the TestObject library.

import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.testobject.ConditionType

The object is created by giving it a name and associating a property to it. In the very simple example below, "xpath" is set with the location of a tab on the page.

TestObject is created with the name "dynamicObject"

The "dynamicObject" is given an xpath value that equals the contents of the xpath String

Once the object has been created, it can then be clicked.

The main work is done through this command:

TestObject dynamicObject = new TestObject('dynamicObject').addProperty('xpath', ConditionType.EQUALS, xpath, true)

xpathOfObject="//a[contains(text(),'Contacts')]"
TestObject dynamicObject = new TestObject('dynamicObject').addProperty('xpath', ConditionType.EQUALS, xpathOfObject, true)

WebUI.click(dynamicObject)

There is one important thing to note, since this object is not part of the project Object Repository, the call to manipulate it is slightly different. Note there is no, findTestObject, or the path of the object as part of the command. If the object were part of the project, the command would look like this:

WebUI.click(findTestObject('Project/Customer Profile/Tabs/tab-Contacts'))

The same would be true if this object were passed to a Custom Keyword:

WebUiCommonHelper.findWebElement(objectReference,5)
vs

WebUiCommonHelper.findWebElement(findTestObject(objectReference,5))

Again, I don't know that I have a use case for this scenario, but others have brought it up, and Katalon fully supports it. A dynamic object is quite an easy thing to create and the only real change is how the object is referenced by telling Katalon not to look in the Object Repository, but "locally" if you will.

Other articles of interest:

Recent Comments

  • How To Disable the Quicken Registration Prompt (26)
    • Denise Defoor: I have a Quicken SE Version 6 .. My computer crashed a few weeks ago and I finally got it up and running. Now Quicken is telling me I must registered it. What? I have had this program for 20 years. How can I disable the...
    • Greg: For me, holding the *LEFT* CTL + Shift then clicking Online, One Step Update worked. I originally tried holding the right CTL + Shift, and it didn’t work. I’m using Quicken 2006, so I don’t know if it will work...
    • Joe SR>: My monthly income is deposited into my Credit Union account. I use debit whenever possible. I write checks manually and mail them. I use Quicken 2012 off-line only. I have entered all my money and investment accounts. I...
    • Prtet: Never say never….every time I swear I will never use Quicken again, I discover that there are still no viable alternatives. Amazing that there is no decent personal finance software.
    • Joe D.: Holding CTRL + Shift keys and selecting On Line | One Step Update from the main menu worked for my Quicken 2004. I’m grateful that you wrote a synopsis (“Simply put, …”) just beneath the link to the blog...
    • Susan Long: I bought my quicken disc in the beginning and it came with a registration number. I rang the helpline and they gave me the code to put in and talked me through it. It you downloaded your version then you don’t own it...
    • Peter: You might consider running your old version of Quicken on an ancient computer. This is what I have done for years. The newer versions are fraught with problems- criminal, in my opinion. One version made mathmatical errors when...
  • Parsing Strings in Katalon – Split, Substring and Readlines (4)
    • Anjana: Hey thanks for the response It is quite strange but initializing variable for delimiter and it worked String splitFormat = ‘-‘ WebUI.println(var_CampaignDura tion.toString().split(splitFor mat)[0])...
    • Don Pedro: I’m not really sure what the difference is, but I don’t think the “-“ in the split is the same as the one in the date. String var_CampaignDuration = “05/13/2019-06/07/2019&# 8221; String[] parsedDateRange =...
    • Anjana: Hi, I tried the same thing to split the string, but does not work for me, Could you plz point out whats wrong Variable var_CampaignDuration has value “05/13/2019-06/07/2019&# 8221; String[] parsedDateRange =...
    • Ellen: Thanks for sharing!! I like your contributions to Katalon topics.
  • How to Block games by Title and Tag on Steam (2)
    • Marcus861: I made an acc just to say: Same bro
    • JACK: Thanks, same just wanted to block anime games in my discover
  • Create a new and random UUID for an API call (1)
    • Anonymous: Hi ! I found this blog few weeks ago and it’s really helping me out with my tests! Thanks a lot for the Katalon tips and tricks ! Keep up the good work 🙂
  • Working with Dates and Date Formatting in Katalon Studio (6)
    • Ajoo: Thank you for the details. How do i remove leading “0” from dates. i.e. while formatting i receive 04/21/2019, but i need 4/21/2019. (same applies for date)