web stats
Parsing CSV - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2012, 04:42 AM
allencoded allencoded is offline
Mirth Newb
 
Join Date: May 2012
Posts: 7
allencoded is on a distinguished road
Question Parsing CSV

I am currently trying to find the best way to code Mirth to parse a CSV file into our SQL database. Does anyone have any examples they are willing to share where they have done this themselves? My current challenge is facing how to do this using javascript. Any help would be greatly appreciated!


EDIT:
I have successfully configured mirth and I am attaching my exported channel. This CSV export takes an incoming CSV file and writes the values into a SQL database. It's fairly barebones and basic edit it for you liking. Thanks for the help.
Attached Files
File Type: xml CSV_Demo.xml (40.7 KB, 368 views)

Last edited by allencoded; 05-24-2012 at 07:21 AM.
Reply With Quote
  #2  
Old 05-21-2012, 06:58 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,225
cory_cole is on a distinguished road
Default

var CSV = getCSV(); \\what means you are using to retrieve the data
var rowsCSV = CSV.split('\n'); \\could be '\r' or '\n\r'
for(i = 0; i < rowsCSV.length; i++)
{
var columnsCSV = rowsCSV[i].split(',');

data1 = columnsCSV [0];
data2 = columnsCSV [1];
data3 = columnsCSV [2];
data4 = columnsCSV [3];
data5 = columnsCSV [4];

var insertStatement = "INSERT INTO TABLE (COLUMN1, COLUMN2, COLUMN3,COLUMN4,COLUMN5) VALUES(data1,data2,data3,data4,data5);
}
Reply With Quote
  #3  
Old 05-23-2012, 07:37 AM
allencoded allencoded is offline
Mirth Newb
 
Join Date: May 2012
Posts: 7
allencoded is on a distinguished road
Default

Question? I set the source file up and it is a CSV file. When mirth reads this CSV file does it do any conversion to the file like, turn the CSV into a XML file? This would mean that inside of my destinations tab I will need to parse this file as a CSV, or does Mirth keep the file the same as the source and we parse it using javascript?

Thanks again
Reply With Quote
  #4  
Old 05-23-2012, 07:49 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,225
cory_cole is on a distinguished road
Default

It depends on what you are sending out. Mirth may convert it to XML for doing any processing that it needs to do but it will convert it back to CSV if you are sending it out as a CSV or convert it to what ever type you are sending out (i.e. HL7).
Reply With Quote
  #5  
Old 05-23-2012, 08:28 AM
allencoded allencoded is offline
Mirth Newb
 
Join Date: May 2012
Posts: 7
allencoded is on a distinguished road
Default

So here is my setup...

Source file is a CSV.
Source Connector Inbound: Delimited Text
Source Connector Outbound : HL7 (I thought this didn't concern us as we are only worried about fetching our CSV then using Javascript in destination tab to parse and set into our SQL server.)
In that same screen our Destination Connector is set to HL7. I left them as default. Does this mean that Mirth will convert our CSV to HL7 for us to parse through? If so then I am going about my destinations parsing tab wrong.

In our source tab I have setup our connector type to File Reader and filled in the appropriate text boxes for directories.

Destinations tab is set as Database Writer connector type. Driver SQL. Then we parse our CSV as a CSV. But this could be the wrong, as I have a feeling that the destination connector is set wrong and should be set as delimited text.


So did I mess up in the Set Data Types box?

Thanks again
Reply With Quote
  #6  
Old 03-31-2015, 04:20 AM
kkrgr8 kkrgr8 is offline
What's HL7?
 
Join Date: Jan 2015
Posts: 5
kkrgr8 is on a distinguished road
Default

Create a channel with source connector type as file reader and specify the path and filename

sample csv file
**********
name,securitynumber,aaaaa,aaaaa,aaaaa
YYYYYY,11111111,XXXXXXX,YYYYY,XXXXX
YYYYYY,11111111,XXXXXXX,YYYYY,XXXXX
YYYYYY,11111111,XXXXXXX,YYYYY,XXXXX

************
Iterate to get the values
for(var i=1;i<msg['row'].length();i++) // start from 1 to avoid the title.
{
var sn = msg['row'][i]['column2'].toString(); // value of security number

}

Set destination conncetor type as database writer. And proceed your query.

Thanks
Krishna
Reply With Quote
Reply

Tags
csv, parsing

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 07:08 PM.


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