DB Row Value - How to assign an each row value ( from particular table) into individual variable

  1. Have created New Data file in Data Files folder using database connection
  2. Able to configure the data from particular table using data = findTestData(‘’)
  3. Able to retrieve the rownumbers using for loop
    for (def i : (1…data.getRowNumbers()))
    {
    String rowcountdb = data.getValue(1, i)
    log.logWarning(‘rowcountdb’ + rowcountdb)

}

Now i would like to assign an each row value into individual variable so that i can do data count validation using if else condition.

Please let me know how can i proceed?

hi,

i am wonder what you are doing?
are you checking is there null value in some cell or counting how many rows are in every column?

hi,

is this something what you will need

//get instance of internal datafile
InternalData data = findTestData('SpamData/someSpamTestData')

//get column names from the datafile
def colNames = data.columnNames
println "column names "+colNames

def ro = data.getRowNumbers()

KeywordUtil log = new KeywordUtil();

List <String> row1 = new ArrayList<>()
List <String> row2 = new ArrayList<>()
List <String> row3 = new ArrayList<>()
List <String> row4 = new ArrayList<>()

List<ArrayList> allCol = new ArrayList<ArrayList>();//Create a list of lists
allCol.add(row1)
allCol.add(row2)
allCol.add(row3)
allCol.add(row4)

int row = 1  //values start from row 1
int rowCount = 1
int colCount = 0
int columnIndex = 1
//loop rows
for (def index : (0..data.getRowNumbers() - 1)) {
	//loop columns
	for (String col : colNames){
		//check is there value in the column cell
		if (data.getValue(col, row).equals("")){
			println "column: "+col+" has not value!!!! "+data.getValue(col, row)+ " row: "+row
			log.markWarning("column: "+col+" has not value!!!! "+data.getValue(col, row)+ " row: "+row);
			allCol.get(colCount).add(data.getValue(col, row))
			//log.markError("column "+col+" has not value!!!! "+data.getValue(col, row)+ " row "+row);
			//throw new com.kms.katalon.core.exception.StepErrorException("column "+col+" has not value!!!! "+data.getValue(col, row)+ " row "+row)
		}else{
			println "column: "+col+ " row: "+row+" value: "+data.getValue(col, row)
			allCol.get(colCount).add(data.getValue(col, row))
			println colCount	
		}
		
	}
	colCount++
	rowCount++
	columnIndex++
	row++
}
println "row 1 contents "+row1
println "row 2 contents "+row2
println "row 3 contents "+row3
println "row 4 contents "+row4


List <String> colRows1 = new ArrayList<>()
List <String> colRows2 = new ArrayList<>()
List <String> colRows3 = new ArrayList<>()
List <String> colRows4 = new ArrayList<>()

//loop col1
for (ArrayList s: allCol){
	colRows1.add(0, s.get(0))
}
//loop col2
for (ArrayList s: allCol){
	colRows2.add(0, s.get(1))
}
//loop col3
for (ArrayList s: allCol){
	colRows3.add(0, s.get(2))
}
//loop col4
for (ArrayList s: allCol){
	colRows4.add(0, s.get(3))
}

colRows1.removeAll(Arrays.asList("")) 
colRows2.removeAll(Arrays.asList(""))  
colRows3.removeAll(Arrays.asList(""))  
colRows4.removeAll(Arrays.asList(""))  
println colRows1
println colRows2
println colRows3
println colRows4

println "count of rows in column name first: "+colRows1.size()
println "count of rows in column name second: "+colRows2.size()
println "count of rows in column name third: "+colRows3.size()
println "count of rows in column name fourth: "+colRows4.size()

RESULTS:
count of rows in column name first: 3
count of rows in column name second: 4
count of rows in column name third: 4
count of rows in column name fourth: 2

datafiles

Hi Timo,

Thanks for your mail with detailed codes.

Let me implement the logic.]

hello,

fixed version which creates dynamic List of Lists

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

import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testdata.InternalData as InternalData
import com.kms.katalon.core.util.KeywordUtil


//get instance of internal datafile
InternalData data = findTestData('SpamData/someSpamTestData')

//get column names from the datafile
def colNames = data.columnNames
println "column names "+colNames

def ro = data.getRowNumbers()

KeywordUtil log = new KeywordUtil();

//Create a dynamic list of lists
List<List<String>> lists = new ArrayList<List<String>>();
for (int i = 0; i < ro; i++) {
	List<String> list = new ArrayList<>();
	lists.add(list);
}

int row = 1  //values start from row 1
int rowCount = 1
int colCount = 0

//loop rows
for (def index : (0..data.getRowNumbers() - 1)) {
	//loop columns
	for (String col : colNames){
		//check is there value in the column cell
		if (data.getValue(col, row).equals("")){
			println "column: "+col+" has not value!!!! "+data.getValue(col, row)+ " row: "+row
			log.markWarning("column: "+col+" has not value!!!! "+data.getValue(col, row)+ " row: "+row);
			//allCol.get(colCount).add(data.getValue(col, row))
			lists.get(colCount).add(data.getValue(col, row))
			//log.markError("column "+col+" has not value!!!! "+data.getValue(col, row)+ " row "+row);
			//throw new com.kms.katalon.core.exception.StepErrorException("column "+col+" has not value!!!! "+data.getValue(col, row)+ " row "+row)
		}else{
			println "column: "+col+ " row: "+row+" value: "+data.getValue(col, row)
			//allCol.get(colCount).add(data.getValue(col, row))
			lists.get(colCount).add(data.getValue(col, row))
			println colCount	
		}
		
	}
	colCount++
	row++
}

List <String> colRows1 = new ArrayList<>()
List <String> colRows2 = new ArrayList<>()
List <String> colRows3 = new ArrayList<>()
List <String> colRows4 = new ArrayList<>()

//loop col1
for (ArrayList s: lists){
	colRows1.add(0, s.get(0))
}
//loop col2
for (ArrayList s: lists){
	colRows2.add(0, s.get(1))
}
//loop col3
for (ArrayList s: lists){
	colRows3.add(0, s.get(2))
}
//loop col4
for (ArrayList s: lists){
	colRows4.add(0, s.get(3))
}

colRows1.removeAll(Arrays.asList("")) 
colRows2.removeAll(Arrays.asList(""))  
colRows3.removeAll(Arrays.asList(""))  
colRows4.removeAll(Arrays.asList(""))  
println colRows1
println colRows2
println colRows3
println colRows4

println "count of rows in column name first: "+colRows1.size()
println "count of rows in column name second: "+colRows2.size()
println "count of rows in column name third: "+colRows3.size()
println "count of rows in column name fourth: "+colRows4.size()

Thanks Timo