Read and write data from excel in katalon studio

How to Read and write data from excel in katalon studio or you can say data-driven automation testing in the katalon studio.

In this tutorial, I am going to show you how to do data-driven testing using Katalon studio.

Read Data from excel file in Katalon studio

 Step 1: create a new data file(excel) example “demo.xlsx”.

Step 2: And enter some data into that file.

Step 3: Create a new data file in katalon studio.

Step 4: And browse your external file and select the sheet.

Step 5: Create test case and define some variable in the test case.

Step 6: Now create a TestSuite And configure a test case. And bind the variables with the column name. If you stuck in between please follow the video tutorial for the same.

Step 6: Execute your TestSuite.

Write Data from excel file in katalon studio.

[embedyt] https://www.youtube.com/watch?v=fgdodORDjL0[/embedyt]

 Katalon team still not provides any built-in keyword to write data in the external file so we need to use the custom keyword here by using POI library.

How to create custom keyword in katalon studio. Please follow the tutorial.

package myPack

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject

import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.checkpoint.Checkpoint
import com.kms.katalon.core.checkpoint.CheckpointFactory
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testcase.TestCaseFactory
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.testobject.ObjectRepository
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords

import internal.GlobalVariable

import MobileBuiltInKeywords as Mobile
import WSBuiltInKeywords as WS
import WebUiBuiltInKeywords as WebUI

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcel {
  
  
  @Keyword
  public void demoKey(String name) throws IOException{

    FileInputStream fis = new FileInputStream("E:\\ExcelSheets\\Demo1.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
     
    XSSFSheet sheet = workbook.getSheet("Sheet1");
    int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
    Row row = sheet.createRow(rowCount+1);
    Cell cell = row.createCell(0);
    cell.setCellType(cell.CELL_TYPE_STRING);
    cell.setCellValue(name);
    FileOutputStream fos = new FileOutputStream("E:\\ExcelSheets\\Demo1.xlsx");
    workbook.write(fos);
    fos.close();
    
  }

}

Call that keyword inside your test case and pass the data it will store the data in targeting file.

WebUI.openBrowser('')
WebUI.navigateToUrl('http://demoaut.katalon.com/')
String result = WebUI.getText(findTestObject('Object Repository/Page_CURA Healthcare Service/a_Make Appointment'))
CustomKeywords.'myPack.WriteExcel.demoKey'(result)
WebUI.closeBrowser()

For more clarity, you can watch the video tutorial.

[embedyt] https://www.youtube.com/watch?v=XoCzZRdEKqM[/embedyt]

[embedyt] https://www.youtube.com/embed?listType=playlist&list=PLNPi-ByLMIQjZh8C4xJBZfqvNcdyTorkA&v=olAEjQizpCE[/embedyt]

3 thoughts on “Read and write data from excel in katalon studio”

  1. Hi Bhupendra,

    Kem cho Bhai. Jk

    I am trying to automate a task from my Instagram where I want to open a series of pictures from different accounts and like them. I stored all the links on excel file did the data binding and use test suites to check. The problem is when it iterates the login part keeps on looping and i don’t want that to happen. How can i stop doing login each time and keep opening new links from the file?
    also when I am running the script from the test cases then it throws me this error.
    com.kms.katalon.core.exception.StepFailedException: Unable to navigate to ”
    at com.kms.katalon.core.webui.keyword.internal.WebUIKeywordMain.stepFailed(WebUIKeywordMain.groovy:64)

  2. In GET API my JSON response had 487 record, which i need to verify and match data from excel
    how can i do that?
    any help

    JSON like:
    [{“id”:”1″,”value”:”Aberdeen, SD”},{“id”:”2″,”value”:”Aberdeen, WA”},{“id”:”3″,”value”:”Abilene, TX”},{“id”:”4″,”value”:”Ada, OK”},{“id”:”5″,”value”:”Adrian, MI”},{“id”:”7″,”value”:”Albany-Schenectady, NY”},……
    487

  3. hi, how to change above code to store all the outputs from multiple runs. ex: we want to automate the password entering process to a strength meter, taking inputs from a excel file and if we need to store the feedback for each run in to a excel file how should I do that. This code is over writing the feedback, at the end I only get one value

Leave a Comment

Your email address will not be published.