Connecting Katalon Studio into XLS and CSV files

Since reading from external source files has become an important part of how we get data, I wanted to put together some simple examples to show reading data from a CSV file, an XLS file and from a Database source. All three are very similar and since Katalon handles most of the work, it's easy to grab data from a file with just a few lines of code.

We start by using the TestData library and reference the "Test Data File" from the "Data Files" object in the project. That is done with:
TestData csvData = findTestData("Data Files/Data Source Name")

Once we have that, we can use some of the built in Katalon keywords to get the number of rows in the file and the column names.
int rowCount=csvData.getRowNumbers()
List columnNamesList=csvData.getColumnNames()

Once we have that information we know how many rows we can read and how many columns. We then use getValue to read our piece of information.
temp1=csvData.getValue(columnName, rowNumber)

When working with a database, there is one small addition. We can use getAllData to retrieve all the items that have been returned from the query. This isn't a necessary step since we can still get the number of rows and parse through the data. However, if the entire block of data is needed, getAllData can be used.
List resultSet=dbData.getAllData()

In such a case, your data will be returned in this format:
[[04GA, 61], [04OC, 93], [02OC, 334]]

For the above we have the SKU followed by the quantity.

Again, Katalon provides a very simple way of connecting into different kids of data sources and retrieving information. This has certainly improved my Test Cases and opened up a variety of test possibilities.

Read data from CSV file

TestData csvData = findTestData("Data Files/csvDataSource")

int rowCount=csvData.getRowNumbers()
println rowCount
int rowNumber=1
List columnNamesList=csvData.getColumnNames()

println columnName
temp1=csvData.getValue(columnName, rowNumber)
println temp1
Read data from XLS file

TestData xlsData = findTestData("Data Files/Inventory Items")

int rowCount=xlsData.getRowNumbers()
println rowCount
int rowNumber=1
List columnNamesList=xlsData.getColumnNames()

columnName=columnNamesList.indexOf("Item Name")+1
println columnName
temp1=xlsData.getValue(columnName, rowNumber)
println temp1
Read data from Database

TestData dbData = findTestData("Data Files/PostgresDB")
List columnNamesList=dbData.getColumnNames()
println columnNamesList
List resultSet=dbData.getAllData()
for (loop = 0; loop <=(numOfResults-1); loop++) {

temp1=dbData.getValue(2, 1)
println temp1
temp2=dbData.getValue(3, 1)
println temp2
columnName=columnNamesList.indexOf("column name")
println "column Position:" + columnName

Other articles of interest:

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Comments
  • How To Disable the Quicken Registration Prompt (32)
    • Mike: Hi, for anyone still trying to run older versions of Quicken, mine is 2004, I hope this helps. I managed to get it running on my old laptop by adding the three missing dll files, when I needed to upgrade I thought I would be able...
    • Juani: Hi, I have a registered Quicken 2016 and now my files are being held hostage by Intuit, cannot log in with my ID unless I upgrade therefore buy a subscription, I need to access my files, please HELP. Thanks
    • David: A BIG Thank you! I regularly reinstall Windows OS’s using different hardware configs. In short, Quicken 2011 (it’s March of 2020 as I write this) has been a program I’ve been very happy with and continue to use....
    • Karen L: Today I rang Reckon Australia again, got a different guy and he talked me through the process of getting me a new product key to enter since I already had the licence and it had been extended. Reckon have only extended it by 1...
    • Karen L: I have Australian Quicken 2008 on Windows 8.1. It keeps asking me to renew my licence which Reckon did for me however the activation won’t work. I tried this to disable the registration prompt however it did not stop the...
  • Enter dates into a date picker for Chrome and Firefox (1)
    • Prasanna: Hello, How to enter a previous month FIRST day and LAST day from the system date. Thanks Prasanna
  • Building Relationships with Developers (1)
    • Carlos Herrera: Ah yes. I have a team of software developers and it is genuinely so hard to keep them in the loop during projects. Though the more we work the better the communication becomes and we go from a trail and error process to a...
  • Integrating DevonAgent Pro with Alfred (1)
    • J. Garr: Beautiful, sweet, and simple. I love it; thanks for posting this solution.
  • Round and Round with the For..Next in Katalon Studio (1)
    • Sweet Ophaline Labador: Hello can you help me how to loop this scenario. Checking the elements is just the same. I want to check that the following function is available in Track Shipment – clicking on this can...
  • Parsing Strings in Katalon – Split, Substring and Readlines (6)
    • Don Pedro: String tempText=”Date Month January. Revenue $1,355,721.00″ println(tempText.split(“ \$”)[1]) You need to escape the $ with \$ The [1] on the split captures characters on the right of the split [0]...
    • Prasad: Hi, I need to get the substring from the below string ‘Date Month January. Revenue $1,355,721.00’ I want to extract text after $ value. Please help Thank you
  • Working with Dates and Date Formatting in Katalon Studio (15)
    • Sangeethaa: How to get previous day’s date(Yesterday’s date)? Actually I was using today.previous() before,it was working fine till today morning.Now its not retrieving right date. Can anyone please guide me on this.
    • Don Pedro: That could be a little tricky. First, is the text/format always going to be the same? You will need to do some parsing. Separate the first but of text at 2019. After that, split the text again to get rid of Central Standard...
  • Output status messages and test information by writing to the Log File Viewer in Katalon Studio (6)
    • Saranya: Good One. Could you plz share link to access all your katalon related blogs. ThankQ
    • Rodrigo Calabretta: I’m using the KeywordUtil.markFailed or KeywordUtil.markError and my test stops is being shown as Error and If I use in the @afterTestCase the testCaseContext.getTestCaseSta tus() to show the status test case...