Remove blank rows in excel Datafile from katalon

Dear all,

I have used the excelsheet for Datafile and excelsheet contains cells validation and macros. In Katalon studio I am getting blank lines and it runs suit for blank record as well. How we can approch in this scenarios and can we delete runtime blank lines from Datafile?


In this Test Data file getting many blank lines and because of that reason suite runs for blank lines as well. Can someone please help me on this?
Is it possible to delete the rows from katalon side as I am not able to delete it?


You should delete the unwanted lines in the Excel file before you pass it to Katalon Studio.

1 Like

An alternative approach is possible.

  1. You no longer use the KS built-in feature Data-driven Testing
  2. You want Excel Keyword
  3. Your test case script explicitly iterates over the lines in a spreadsheet. The script will have entire control which lines to be processed and which lines to be ignored. Your script can check out blank lines and simply ignore them.
1 Like

Hi @nishipate

If you don’t mind some programming, Katalon new version supports a (relatively less known) annotation called @BeforeTestDataBindToTestCase. The annotation can be used to invoke a function which will be executed prior to test data bind to test case.

Just want you to know this is a way to to do this, however I think you should remove the blank entries from Excel as @kazurayam suggested if the blank entries are accidental. If there are some (rare) circumstances under which you want to have blank entries in between your Excel rows (for example you want to quickly test that Excel data source without some specific rows) then you can use the annotation.

Here’s an example use case:

	def variableBindingConverter(TestSuiteContext tsContext, TestCaseContext tcContext, Map variableBinding) {
                Iterate over the variableBinding map, remove entries with empty string values
1 Like

There is another approach. Please have a look at the following article

Using Data-driven testing feature, there are 2 execution method:

  1. Executtion from test suites
  2. Execute from a test case

Possiblly you are using the 1st method. You can try the 2nd method.

The following is my sample code which uses the 2nd method.

This code iterates over the lines from Data storage. If the line is blank, the line is silently ignored. Therefore the blank lines in the Data storage become insignificant at all.

1 Like

Thank you @kazurayam and @ThanhTo

First of all wanted to tell you guys that you guys are doing amazing job by quick response and solve problems in Katalon… I really appreciate for that.

I am using iteration through for loop and check length using if condition in my test suite test cases. However, I am applying some validation macro in excelsheet for testcases in specific column, such as field should contain only numbers for first 20 records. If suppose I am applying macros for 1-20 rows without test record from 5-20 and I have test data from 1-4, katalon adds 20 rows in test cases. In test suit also it runs for 20 rows and at last in repost it show all that rows. If I remove my validation macro in excelsheet and save, that row remains 20 with blank records. it supposed to remove from katalon as well but it is not happening. At last suite runs for all the 20 rows first 4 with data and rest with without data.

I am using suite for better log report. Kindly help me to solve this problem

I am not sure if I understand your problem but …

The macros in Excel sheets will not be executed in Katalon Studio as KS is not Excel.

If you want to consume, in KS, the result of the Excel marcro, you should use Excel to execute the macros and save the resulting sheet into a plain text file (CSV, Tab-stop etc). Then you want to pass the text file to Katalon Studio.

Hi @nishipate,
Did you get any resolution of your problem? I am having the same issue. How did you resolve your issue? Can you please explain?
Or let me know if you did follow any alternatives.
(Please share any code if that helped you)