Sidebar

How to get column count from the results of a query or CSV message

0 votes
414 views
asked Sep 17, 2015 by rich-c-2789 (16,180 points)
I have a need to determine the columns in a CSVMessageModel dynamically.  In my case to create a function that can process any number of columns passed in.

1 Answer

0 votes
There are a couple ways (at least) to determine the columns in a CSVMessageModel.    
In most cases you should know the number of columns in advance.  Like in this first example:
 
//Example using a CSVMessageModel parsed from a comma separated string.
//Setup
var data = '"column1","column2","column3"\n' +
 '1,"data","moreData"\n' +
 '2,"abc","apples"';
var csvModel = qie.parseCSVString(data, true, '"', ',', true);
 
//To get the column count from the headers
var columnCountFromHeaders = csvModel.getHeaders().size();
qie.debug('columnCountFromHeaders: ' + columnCountFromHeaders);
 
//to get the column count from the rows .44 or below
var columnCountFromRows = csvModel.getRows().get(0).size();
qie.debug('columnCountFromRows: ' + columnCountFromRows);
 
//to get the column count from the rows .45 or higher
var columnCountFromRows = csvModel.getColCount();
qie.debug('columnCountFromRows: ' + columnCountFromRows);
 
Results of running the above: 
columnCountFromHeaders: 3
columnCountFromRows: 3
 
However, in this query example, you may not know in advance how many columns are returned.  (Note: it is bad practice to use '*' in an SQL query select list, define the fields needed instead)
var queryResult = qie.doQuery(dbName, ‘SELECT * FROM person’);
//Get the column count from the queryResults .44 or below
var columnsInQueryResult = queryResult.getHeaders().size();
//Get the column count from the queryResults .45 or higher
var columnsInQueryResult = queryResult.getColCount();
qie.debug('columnsInQueryResult: ' + columnsInQueryResult);
 
One difference between the two approaches is that the getHeaders().size() will return 0 if we did not define the CSV with headers.  For example: qie.parseCSVString(data, true, '"', ',', false);
answered Sep 17, 2015 by rich-c-2789 (16,180 points)
edited Apr 4, 2018 by brandon-w-8204
...