Sidebar

How do I get data from a CSV message

0 votes
645 views
asked Mar 19, 2015 by brandon-w-8204 (30,600 points)
How do i retrieve fields from a csv message and set data back into fields

3 Answers

+1 vote
 
Best answer

for .45 Releases and above:

We have added a getColCount() in .45 release. Here is the updated sample code:

// Retreive Header Row
qie.info('Header Row = ' + parsedTableWithHeaders.getNode('*[0]'));

//Count rows in table for use in for loop
qie.info('Row Count = ' + parsedTableWithHeaders.getRowCount());

//Loop through each row and return the entire row
for (var row=1 ; row <= parsedTableWithHeaders.getRowCount() ; row++) {
   // Get the entire row as a string
   qie.info('Row ' + row + ' = ' + parsedTableWithHeaders.getNode('*[' + row + ']'));
   
   //Count Columns in the Row
   qie.info('Row ' +  row + ' Column Count = ' + parsedTableWithHeaders.getColCount());
   
   // Retreive the column value from a row to use
   for (var column=1 ; column <= parsedTableWithHeaders.getColCount(); column++) {
      qie.info('Row ' + row + ' - Column '+ column + ' = ' + parsedTableWithHeaders.getNode(column + '[' + row + ']'));
   }
}

answered Apr 4, 2018 by brandon-w-8204 (30,600 points)
selected Feb 20, 2020 by mike-r-7535
0 votes
For .44 releases and below:
Below is some sample code on how to retrieve the different data elements from a csv message. 
 
Sample Code:
 
// Retreive Fist row or Header Row
qie.info('Header Row = ' + parsedTable.getNode('*[1]'));
 
//Count rows in table for use in for loop
qie.info('Row Count = ' + parsedTable.getCount('*'));
 
//Loop through each row and return the entire row
for (var i=0 ; i < parsedTable.getCount('*') ; i++) {
   var row = parsedTable.getNode('*[' + (i + 1) + ']');
   qie.info('Row ' + (i + 1) + ' = ' + row);
   
   //Count Columns in the Row
   // Convert Row to string for split
   var row1 = row + '';
   var columnCount = row1.split(',');
   qie.info('Row ' +  (i + 1) + ' Column Count = ' + columnCount.length);
   
   // Retreive the column value from a row to use
   for (var j=0 ; j < columnCount.length ; j++) {
      qie.info('Row ' + (i + 1) + ' - Column '+ (j + 1) + ' = ' + parsedTable.getNode((j + 1) + '[' + (i + 1) + ']'));
   }
}

 

answered Mar 19, 2015 by brandon-w-8204 (30,600 points)
edited Apr 4, 2018 by brandon-w-8204
commented Mar 20, 2015 by jon-t-6024 (560 points)
An alternate way, though perhaps not the best practice: you could dump the data into a system variable table if your first row contains headers.  Just create a new system variable table, hit the checkbox to enable qie.setValue(), then use this:

   qie.setVariable('CSVData', source.getNode('/'));

From there, you can use qie.doTableLookup to get your data.
0 votes

Another method is to take the CSV data and populate it into a JavaScript object, using the data from the header of the CSV (first row) as the key values.

For example, the code below will pull in the first row as header information, and use each value of the header as the key for the JavaScript object.  It will then populate each row into an array in that object, with each item in the array being associated with the key.  

Thus, if your header row was "PatientID, LastName, FirstName", you could access PatientID by using the following syntax:  row[1].PatientID.  Or access the LastName field using this syntax: row[27].LastName.  And so on.

Code:

// Initialize variables
var row = [{}];
var i, x, curRow, curCol = 0;
 
// Use the first row of the CSV as a header row
var headerRow = source.getNode('*[1]');
 
// Find the number of rows
var numRows = source.getRowCount();
 
// Let's assign the values from all rows to the array.
// First, we start by expanding our array to have the same
// number of rows as the CSV
for (i = 0; i < numRows-1; i++){
   row.push( [] );
}
 
// Next we populate the array.
// NOTE: our array starts at 1, not 0!
// Access the first row like this: data[1].key
 
for (i = 1; i < numRows; i++){
   for (x = 0; x < headers.length; x++){
      curCol = x + 1;
      curRow = i + 1;
      row[i][headers[x]] = source.getNode(curCol + '[' + curRow + ']').replace(" ","");
   }
}
answered Mar 20, 2015 by jon-t-6024 (560 points)
...