Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Support (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=6)
-   -   SQL -> XML -> Delimited (CSV) (http://www.mirthcorp.com/community/forums/showthread.php?t=217724)

clarkmirth 11-01-2017 06:51 AM

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?

appsbyaaron 11-01-2017 07:10 AM

1 Attachment(s)
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.

clarkmirth 11-01-2017 10:26 AM

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. :(

AlexNeiva 11-06-2017 05:52 AM

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

yeruva 09-11-2018 05:17 PM

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

siddharth 09-11-2018 10:08 PM

The above code doesn't work for you?

agermano 09-12-2018 06:18 AM

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.

agermano 09-12-2018 07:05 AM

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');


yeruva 09-13-2018 04:55 AM

Hi Agermano - Where to provide this code. also can you please suggest what values to enter in the source & destination

agermano 09-13-2018 07:01 AM

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.


All times are GMT -8. The time now is 09:56 AM.

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