web stats
SQL -> XML -> Delimited (CSV) - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 11-01-2017, 06:51 AM
clarkmirth clarkmirth is offline
Mirth Newb
 
Join Date: Jun 2014
Posts: 10
clarkmirth is on a distinguished road
Default SQL -> XML -> Delimited (CSV)

#Mirth Connect 3.5.1


I was trying to run a sql query and get the XML the mirth source channel transforms the results into and convert that into CSV.

I have changed the Data Types to XML on inbound and outbound to Delimited Text. I doesn't seem to convert to delimited.

I've tried changing some of the serialization and deserialization, but don't seem to get the right settings.

I've tried changing the outbound template to include my headers.

I've tried manually parsing the XML to get the javascript, but I feel like there is probably an easier way to do it with the default settings.

Will correctly setting the data types and the serialization and deserialization settings allow mirth to convert the XML returned from the SQL query to CSV?
Reply With Quote
  #2  
Old 11-01-2017, 07:10 AM
appsbyaaron's Avatar
appsbyaaron appsbyaaron is offline
Mirth Guru
 
Join Date: Nov 2011
Location: Florida, USA
Posts: 377
appsbyaaron is on a distinguished road
Default

You have to map the values. You can't just say inbound is XMl and outbound is comma delimited and expect Mirth to know what to do with that because these are two different data types. Even if you have CD to CD or HL7 to HL7 you may still have to do some manual mapping...unless it's a passthrough with no manipulation required.

You'll need to tell Mirth how to move the data from XML to CD for each field.

tmp['row']['column1'] = msg['EVN']['EVN.1']['EVN.1.1'].toString();

Also, why not just map the results to the CD fields? Why mess with the XML version of the query results? I've attached a sample channel that is barebones to give you an idea.
Attached Files
File Type: xml DB to CD.xml (20.0 KB, 91 views)

Last edited by appsbyaaron; 11-01-2017 at 07:17 AM.
Reply With Quote
  #3  
Old 11-01-2017, 10:26 AM
clarkmirth clarkmirth is offline
Mirth Newb
 
Join Date: Jun 2014
Posts: 10
clarkmirth is on a distinguished road
Unhappy

Ok. Thanks. I thought it would auto map each result to a row and each column to the specified delimiter.

I'll manually map them.
Reply With Quote
  #4  
Old 11-06-2017, 05:52 AM
AlexNeiva AlexNeiva is offline
Mirth Guru
 
Join Date: Oct 2013
Location: Portugal
Posts: 277
AlexNeiva is on a distinguished road
Default

You can use JavaScript to do that, in Source/Destination Transformer:

Code:
var finalCSV="";
var len=msg.children().length();
var i=1;

for each(csv in msg.children())
{
    if(i<len)
        finalCSV+=csv + ",";
    else
        finalCSV+=csv;

    i++;
}

channelMap.put("CSV", finalCSV);
works perfectly.


Best Regards
Reply With Quote
  #5  
Old 09-11-2018, 05:17 PM
yeruva yeruva is offline
Mirth Newb
 
Join Date: Nov 2017
Location: Dallas, USA
Posts: 24
yeruva is on a distinguished road
Default

Hi Alex,
could you please help me to convert xml to csv using mirth connector. can u pls share channel which you used for above code
Reply With Quote
  #6  
Old 09-11-2018, 10:08 PM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 832
siddharth is on a distinguished road
Default

The above code doesn't work for you?
__________________
HL7v2.7 Certified Control Specialist!
Reply With Quote
  #7  
Old 09-12-2018, 06:18 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 964
agermano is on a distinguished road
Default

It doesn't have any of the escaping logic in it. It will produce bad or questionable output if any of the fields contain commas, newlines, or double quotes.
Reply With Quote
  #8  
Old 09-12-2018, 07:05 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 964
agermano is on a distinguished road
Default

All you need to do is convert the xml format returned by the database reader to the format expected by the delimited data type. This will return all fields in the order that they were queried and use the outbound delimited data type properties when serializing. Do not use an outbound template with this solution.
Code:
var newMsg = <delimited/>;
newMsg.appendChild(msg);
newMsg.result.setName('row');
msg = newMsg;
If you are using aggregate results in your db reader, it would look like this instead:

Code:
msg.setName('delimited'); // technically, this line is optional.
for each (var result in msg.result) result.setName('row');
Reply With Quote
  #9  
Old 09-13-2018, 04:55 AM
yeruva yeruva is offline
Mirth Newb
 
Join Date: Nov 2017
Location: Dallas, USA
Posts: 24
yeruva is on a distinguished road
Default

Hi Agermano - Where to provide this code. also can you please suggest what values to enter in the source & destination
Reply With Quote
  #10  
Old 09-13-2018, 07:01 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 964
agermano is on a distinguished road
Default

This code should go in a transformer. It can be the source transformer or any destination transformer depending on your needs. It should be placed in a javascript type step. Your inbound data type for that transformer should be XML, and your outbound type should be Delimited.

I recommend reviewing the User Guide from the mirth downloads page. It answers a lot of the basic questions about transformers, data types, and connectors.
Reply With Quote
Reply

Tags
csv, delimited, sql, xml

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 08:39 AM.


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