I would like to log specific data during testing to a csv file. This file should be available after the test has run. How to do this in an easy way?
According to katalon studio people, At this time, Katalon has no keyword to write data to test data file. If you would like to use it, you could define a custom keyword, which is writing data to file in Selenium.
Next question may be : If we create a keyword using selenium, we need to install some jar files/external libraries. How do we do that?
Solution: You can import your jar file at Project > Settings > External Libraries
Please refer to https://www.katalon.com/resources-center/tutorials/import-java-library/
for more details
Hi!
I have @Ed van Zoolingen question, too.
@Praveen Kumar you said:
we need to install some jar files/external libraries.
Can you tell us which libraries should we have to install? java.io.FileReader or something like that?
Thanks in advance.
I was able to write using selenium code
import all lib for HSSFWorkbook and HSSFWorkbook
String xlsPath=(“D:\Abhay\Projects\Katalon Studio\OrangeHRM\Data Files\EmpInfo.xls”);
File f1=new File(xlsPath);
FileInputStream fIPS= new FileInputStream(xlsPath); //Read the spreadsheet that needs to be updated
HSSFWorkbook wb = new HSSFWorkbook(fIPS);
HSSFSheet ws=wb.getSheet(“EmpInfo”);
int rountcnt=ws.getLastRowNum();
// for(int k=0+1;k<=rountcnt;k++)
//{
ws.getRow(row).createCell(3)
ws.getRow(row).createCell(3).setCellValue(empID)
FileOutputStream fout=new FileOutputStream(f1);
wb.write(fout);
Hi Abhay! your code snippet supported me to write data into a file.
Thank you!
Hi,
Can any one tell me how to use the above code snippet in my Katalon studio for writing data into Excel.
Thanks in Advance!
you can also write a custom keyword in groovy by reading and updating a csv key value pair.(made it in soapui but you can rewrite it)
testdata.csv example is
key,Value
OrderID,12345
InvoiceID,12355
code does the following
- read the csv file
- find the key invoice id
- write the update to the existing file
hope I gave some inspiration:)))
good luck
hereby the code snippet
@Grab(‘com.xlson.groovycsv:groovycsv:1.1’)
import com.xlson.groovycsv.CsvParser
//Change the filepath as per your environment
// 1) read csv
def csvc = new File( ‘S://Katalon//Testdata.csv’ ).readLines()
//2) find the key(
csvc.findAll{it.startsWith(“InvoiceID”)}.each {
log.info it
log.info csvc.indexOf(it)
//change the testdata (this is not parameterized for this example, hardcoded)
csvc[1] = “OrderID,34567”
log.info csvc[ 1 ]
}
//3) write updated file to existing file
def newFile = new File(“S://Katalon//Testdata.csv”)
PrintWriter printWriter = new PrintWriter(newFile)
csvc.each
{
printWriter.println(it)
}
printWriter.close()
1 Like
Note, it is not reading and updating a csv, but reading a value pair. I have created two custom functions which can be used to update an excisting keyname keyvalue and reading a keyvalue from a name. you can modify it to your own liking. these are the code snippets.
**Read Testdata file **
public class ReadTestfile {
String strvalue
@Keyword
def GetkeyValue(String name)
{
//Change the filepath as per your environment
def keyvalue
def csvc = new File( ‘S://Katalon//Testdata.csv’ ).readLines()
csvc.findAll{it.startsWith(name)}.each
{
def strindex = csvc.indexOf(it)
//change the testdata
keyvalue = csvc[strindex].split(’,’)
strvalue = keyvalue[1].toString()
return strvalue;
}
return strvalue;
}
}
Update TestdataFile code example(to be modified to your liking 
)
public class UpdateTestfile {
@Keyword
def SetkeyValue(String name, String keyvalue)
{
//Change the filepath as per your environment
def csvc = new File( ‘S://Katalon//Testdata.csv’ ).readLines()
csvc.findAll{it.startsWith(name)}.each {
def strindex = csvc.indexOf(it)
//change the testdata
csvc[strindex] = name + “,” + keyvalue
}
def newFile = new File(“S://Katalon//Testdata.csv”)
PrintWriter printWriter = new PrintWriter(newFile)
csvc.each
{
printWriter.println(it)
}
printWriter.close()
}
}
The below worked for me… you need to bring in your imports… Also need to add external libraries from poi
package ms.excel
import…
class excelWriter {
@Keyword
def writeToExcel(String excelFileName, String spreadSheetName, java.util.List excelCellValue, int RowNum, List ColNum) {
try {
// Create blank workbook
XSSFWorkbook wb=new XSSFWorkbook();
//Create a blank spreadsheet
XSSFSheet spreadsheet = wb.createSheet(spreadSheetName);
//Create a row
XSSFRow row = spreadsheet.createRow((short)RowNum);
//Write to a cell column
for (int i = 0; i < ColNum.size(); i++){
row.createCell(ColNum.get(i)).setCellValue(excelCellValue.get(i));
}
// here we need to specify where you want to save file
FileOutputStream out = new FileOutputStream(new File(“C://…//…//…//” + excelFileName +".xlsx"));
// finally write content
wb.write(out);
// close the file
out.close();
System.out.println(excelFileName + “.xlsx saved successfully!”);
} catch (Exception e) {
System.out.println(e.getMessage());
}//end of try catch
}//keyword ending
}//class ending
Hello,
I am trying to get my write to excel keyword work, but it seems I am missing java lib (I don’t have any IDE java on my computer) Could anyone please please help me figure out how to solve this issue . Thank you . I attached the error msg and here is my custom keyword :
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(int iRow, int iCell, String iText){
FileInputStream fis = new FileInputStream(“C:\\Desktop\\Booker-Report.xlsx”);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheet(“Sheet1”);
Row myRow = sheet.createRow(iRow);
Cell myCell = myRow.createCell(iCell - 1);
myCell.setCellValue(iText);
FileOutputStream fos = new FileOutputStream(“C:\\Desktop\\Booker-Report.xlsx”);
workbook.write(fos);
fos.close();
}
}
Hi ,
Can you please update only one write column change code here.
When i am trying to do with above code it is replacing entire row i want to replace only 1 column and 1,2,3,4 row value only no other column
Additionally, for people just viewing this thread, there is a new plug-in to assist with such a problem in the Katalon Studio Store 
.
Hi,
Can you please tell what is the new feature and how to use it?
Thanks.
@minushailankoon90,
https://store.katalon.com/product/34/Excel-Keywords – This has some pre-defined keywords that you can use to easily write to a data file and whatnot – checkout the different functions. There are also some reviews that will bring you to examples and whatnot.
1 Like
Thank you.