web stats
Cycle through rows in .csv in Message Builder? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 07-27-2011, 10:04 AM
DimitriA DimitriA is offline
Mirth Newb
 
Join Date: Jul 2011
Posts: 17
DimitriA is on a distinguished road
Default Cycle through rows in .csv in Message Builder?

Hi,

I am trying to generate a .csv file with patients' lab results in columns. I can store the different results in an array variable using JavaScript; however, I'm having trouble mapping that array to a column in the .csv file using a Message Builder. I can store all of the values to a single cell, but that's about it.

Is there anyway to do this? Any alternate ideas?

Thank you!
Reply With Quote
  #2  
Old 07-27-2011, 10:09 AM
Bostad Bostad is offline
Level 85 Channel Wizard
 
Join Date: Mar 2009
Location: Stratford Ontario
Posts: 712
Bostad is an unknown quantity at this point
Default

If I understand correctly, writing the text you want to add to the file into a channelMap variable, then, in the message body field of your destination set up put that variable and ensure you have append to file set to true would work.

If you're using message builder, the easiest way to do it is to use inbound and outbound code templates. In the Message Tree tab you would drag the destination node over the source node and release your mouse button. Mirth does the rest.
Reply With Quote
  #3  
Old 07-27-2011, 11:05 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

Not sure if this is even going to help you at all, but since you mentioned CSV's, I figured I would post it in case anyone could find some use.

So, basically, my intent was to create a CSV file that contained a set number of columns, with 1 row for each record.

In my Source, I had the channel call a proc that basically did all of the record gathering/modifying of data into a single staging table.

Then, using a JavaScript Source Transformer, I returned all of this data from the staging table and manually built the CSV structure myself without having to map any segments or worry about templates, etc.

Here is the code from my Source Transformer step:
Code:
 var sql = " SELECT LTRIM(RTRIM(header)) AS header,"+
           "LTRIM(RTRIM(study_id)) AS patient_id_med,"+      
           "LTRIM(RTRIM(location_id)) AS location_id_med,"+     
           "LTRIM(RTRIM(drug_name)) AS drug_name_med,"+     
           "LTRIM(RTRIM(drug_descriptor_id)) AS drug_descriptor_id_med,"+       
           "CONVERT(VARCHAR(8), supplied_date, 112) AS supplied_date_med,"+       
           "LTRIM(RTRIM(route_code)) AS route_code_med,"+     
           "LTRIM(RTRIM(amt_ingredient)) AS amt_ingredient_med,"+       
           "LTRIM(RTRIM(wastage_amount)) AS wastage_amount_med,"+       
 	   "LTRIM(RTRIM(amt_ingred_uom))  AS amt_ingred_uom_med,"+    
 	   "LTRIM(RTRIM(where_taken))  AS where_taken_med "+
           "FROM data_stage"

var results = dbConn.executeCachedQuery(sql);

var csv = "";
var delimiter = ",";
var row = "";

while(results.next()) {

//replace NULL values with empty string
var route 	= (results.getString('route_code_med') != null)?results.getString('route_code_med'):"" ;
var amt_med = (results.getString('amt_ingredient_med') != null)?results.getString('amt_ingredient_med'):"" ;
var wastage = (results.getString('wastage_amount_med') != null)?results.getString('wastage_amount_med'):"" ;
var uom_med = (results.getString('amt_ingred_uom_med') != null)?results.getString('amt_ingred_uom_med'):"" ;
var wtaken 	= (results.getString('where_taken_med') != null)?results.getString('where_taken_med'):"" ;

	row = results.getString('header') +delimiter +  results.getString('patient_id_med') +
		  delimiter + results.getString('location_id_med') + 
		  delimiter + results.getString('drug_name_med') + 
		  delimiter + results.getString('drug_descriptor_id_med') + 
		  delimiter + results.getString('supplied_date_med') + 
		  delimiter + route + 
		  delimiter + amt_med + 
		  delimiter + wastage + 
		  delimiter + uom_med + 
		  delimiter + wtaken + '\n';
    csv = csv + row;
}
channelMap.put('csv', csv);

Then, in the Destination Template, instead of using Encoded/Raw/Transformed, I instead use the ChannelMap variable:
Code:
${csv}
In the end, Mirth outputs a .txt file that contains something like the following:
Code:
MED,111111,000248,EPOGEN,007733,20090429,IV,7000,0,UNIT,C
MED,111111,000248,EPOGEN,007733,20090501,IV,7000,0,UNIT,C
MED,111111,000248,VENOFER,068506,20090429,IV,100,0,MG,C
MED,111111,000248,VENOFER,068506,20090501,IV,100,0,MG,C
MED,111111,000248,ZEMPLAR,074358,20090429,IV,2,0,MCG,C
MED,111111,000248,ZEMPLAR,074358,20090501,IV,2,0,MCG,C
MED,333333,000145,EPOGEN,007733,20090430,IV,26200,,UNIT,C
Reply With Quote
  #4  
Old 07-28-2011, 10:29 AM
DimitriA DimitriA is offline
Mirth Newb
 
Join Date: Jul 2011
Posts: 17
DimitriA is on a distinguished road
Default

Quote:
Originally Posted by Bostad View Post
If I understand correctly, writing the text you want to add to the file into a channelMap variable, then, in the message body field of your destination set up put that variable and ensure you have append to file set to true would work.

If you're using message builder, the easiest way to do it is to use inbound and outbound code templates. In the Message Tree tab you would drag the destination node over the source node and release your mouse button. Mirth does the rest.
I am currently doing this. However, I can only map the variable to a single cell as the destination node. If I do this, all of the lab values appear in that one cell.

I was wondering if there was some way of mapping the variable to the same column but multiple rows
Reply With Quote
  #5  
Old 07-28-2011, 10:52 AM
Bostad Bostad is offline
Level 85 Channel Wizard
 
Join Date: Mar 2009
Location: Stratford Ontario
Posts: 712
Bostad is an unknown quantity at this point
Default

Quote:
Originally Posted by DimitriA View Post
I am currently doing this. However, I can only map the variable to a single cell as the destination node. If I do this, all of the lab values appear in that one cell.

I was wondering if there was some way of mapping the variable to the same column but multiple rows
Hi Dimitri;

My brain isn't firing on all cylinders today so I'm not seeing what you're asking.

Do you have code we could look at?
Reply With Quote
  #6  
Old 07-28-2011, 01:22 PM
DimitriA DimitriA is offline
Mirth Newb
 
Join Date: Jul 2011
Posts: 17
DimitriA is on a distinguished road
Default

Sure. This would have probably been easier in the first place. Thank you for your time and patience. I really appreciate it.

First, here is the step in my Destination Transformer (that you helped me develop) that takes all of the lab results and creates an array resultVal out of them

Code:
var resultVal = new Array();
var counter = 0;

for each (mOBX in msg..OBX) 
{

	resultVal[counter] = mOBX['OBX.5']['OBX.5.1'].toString();
	counter += 1;
}

channelMap.put("resultVal",resultVal);

Here is my Message Builder. It is mapping the result array to a single cell in my .csv template. I'd like to map it to a column.



This is what the result looks like:



This is what I'd like it to look like:




Any ideas? Thank you!
Reply With Quote
  #7  
Old 07-29-2011, 06:39 AM
Bostad Bostad is offline
Level 85 Channel Wizard
 
Join Date: Mar 2009
Location: Stratford Ontario
Posts: 712
Bostad is an unknown quantity at this point
Default

Okay, I see what you're trying to do.

I'm going to try a couple of things really quick sometime this morning. I've got a couple of ideas. I'll post the channel when I get them working.
Reply With Quote
Reply

Tags
csv, message builder, transformer

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 04:19 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation