Apply Filter to Header before reading CSV

Product Forums Katalon Studio
,
1

Hi,
I have the CSV with nearly 20 fields and has nearly 6k records.
The Main fields are ID and Status.
My task is to have filter for all single column and then fetch the data after applying filter.
As I have 6k records fetching each field by separator and checking the column value doesn’t work as it is time consuming.

If column ‘Status’ is ‘Scheduled’ need to fetch the ‘ID’ corresponding to that ‘Status’
Pls suggest way to apply filter to CSV(As we do for Excel) and then fetch the ID when status is ‘Scheduled’

2

@Nila Sample script for you:

import static com.kms.katalon.core.testdata.TestDataFactory.findTestData

import com.kms.katalon.core.testdata.TestData as TestData

TestData data = findTestData('id of test data')

String columnNames = data.getColumnNames()

int statusColumn = 0
int idColumn = 0

// Detect column index of Status and ID column
for (int i = 0; i < columnNames.length; i++) {
	String column = columnNames[i]
	if (column == 'ID') {
		idColumn = i
	}
	
	if (column == 'Status') {
		statusColumn = i
	}
}

List allData = data.getAllData()
List filteredIds = new ArrayList<String>()
for (List rowData : allData) {
	if (rowData.get(statusColumn) == 'Scheduled') {
		filteredIds.add(rowData.get(idColumn))
	}
}

println filteredIds

This script can apply for all type of test data

3

Hi ,
Thank you for the code.
But We need to create test data within katalon to make this code to work as we have statement ‘TestData data = findTestData(‘data1’)’
Sorry to say I cannot create Testdata as the csv is downloaded from website in my local downloads.
So I need to fetch from download path.
I modified code using bufferedReader. but getting error.
Pls guide me to get the file from local downloads and use it

4

Moreover, I am expecting code to apply filter as we apply manually in CSV . As I have 6k records,checking each record will take more time.

Capture
Capture1359×248 31.1 KB

Sorry to ask this.
Is there any way to apply filter as shown in image and work.
If I apply filter as shown above ,i will get only 60 records which is easy to manipulate rather than checking each of 6000 records

5

You want to do filtering data in Excel, rather than in Katalon, right?

Using Excel, you should be able to create a CSV file which contains the filtered result. See

https://www.extendoffice.com/documents/excel/3303-excel-save-filtered-data-to-csv.html

You should create a temporary CSV file and pass it to Katalon.

Katalon does not understands the filter you configured in .xlsx file.

6

Let me explain the steps in detail which need to be done using Katalon code
1.Open the CSV in the local path “c://Downloads”
2. Apply filter(As do manually) for the opened csv
3. Using the Applied filter select only the records whose ‘Booking-Status’ is ‘Scheduled’ (Other avaialable Booking-Status are Open and closed)
4. Get the Booking-ID corresponding to ‘Booking-Status’ -‘Scheduled’
Pls help with code.
Does any of the robot functionality help in thsi

7

By Visual Basic Macro in Excel app, you can apply filter to the data in the .xslx file with 6k rows and produce a CSV file with much less rows.

You can execute a Macro in Excel from commandline as explained here:

Test Case script in Katalon can start any Windows command in the commandline. I mean, Katalon Test Case script can execute a vbscript which executes Excel.app while specifying Macro to execute. See

@duyluong suggested a code for this.

8

Hi @Nila,

You can programmatically create test data from local csv data like this:

import com.kms.katalon.core.testdata.CSVData
import com.kms.katalon.core.testdata.reader.CSVSeparator

CSVData data = new CSVData('path to file', true, CSVSeparator.COMMA)

You can also use Windows testing to open the CSV file, simulate click on the filters but I think you don’t need. All you can done by using my above script.

9

Hi Nila, Please share your code and how it worked for you. I am facing the same issue and need help on that.