No announcement yet.

Parsing CSV

  • Filter
  • Time
  • Show
Clear All
new posts

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

    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
    Last edited by allencoded; 05-24-2012, 07:21 AM.

  • #2
    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 IGNORE INTO TABLE (COLUMN1, COLUMN2, COLUMN3,COLUMN4,COLUMN5) VALUES(data1,data2,data3,data4,data5);


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


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


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


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

            sample csv file

            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.