Announcement

Collapse
No announcement yet.

Cycle through rows in .csv in Message Builder?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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 can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
    http://www.linkedin.com/pub/david-rothbauer/5/923/518
    codeismydrug.wordpress.com
    hl7coders.wordpress.com

    Test all my code suggestions prior to implementation

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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?
          I can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
          http://www.linkedin.com/pub/david-rothbauer/5/923/518
          codeismydrug.wordpress.com
          hl7coders.wordpress.com

          Test all my code suggestions prior to implementation

          Comment


          • #6
            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!

            Comment


            • #7
              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.
              I can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
              http://www.linkedin.com/pub/david-rothbauer/5/923/518
              codeismydrug.wordpress.com
              hl7coders.wordpress.com

              Test all my code suggestions prior to implementation

              Comment

              Working...
              X