Announcement

Collapse
No announcement yet.

Database to CSV HOWTO

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

  • Database to CSV HOWTO

    This was a bit of detective work so I thought i'd share my solution, which is mostly just cobbled together posts from Naru. I had a task of generating a CSV file from a postgres database table, very simple data flow. We want to email this report to various people. One issue is that I wanted all rows returned instead of just a row at a time, and I wanted to ensure each destination message contained the entire dataset in CSV format.

    The channel source is Javascript, and the following code goes there.


    var dbConn = DriverManager.getConnection('jdbcostgresql://localhost/db','user','pw');

    importPackage(java.sql);
    new com.mysql.jdbc.Driver();
    var ps = dbConn.prepareStatement("SELECT * from Table");
    var rs = ps.executeQuery();
    var rsmd = rs.getMetaData();
    var msg = <results/>;


    while(rs.next()) {
    var result = <result/>;
    for (var i = 1; i <= rsmd.getColumnCount(); i++)
    result[rsmd.getColumnName(i)] = rs.getString(i);
    msg.appendChild(result);
    }

    dbConn.close();

    return msg.toXMLString();


    Then in the source transform, the following code goes there.

    tmp = <delimited/>;

    var header = <row/>;
    var i = 1;
    header.appendChild(col('id',i++));
    header.appendChild(col('CreateDTTM',i++));
    header.appendChild(col('Patient Name',i++));
    header.appendChild(col('MRN',i++));
    header.appendChild(col('Practice ID',i++));
    header.appendChild(col('Member ID',i++));
    header.appendChild(col('Practice Name',i++));
    header.appendChild(col('Success',i++));
    header.appendChild(col('Message',i++));


    tmp.appendChild(header);

    for each (results in msg) {

    for each (result in results.children()) {
    var row = <row/>;
    var i = 1;

    row.appendChild(col(result.id.toString(),i++));
    row.appendChild(col(result.dttm.toString(),i++));
    row.appendChild(col(result.patname.toString(),i++) );
    row.appendChild(col(result.mrn.toString(),i++));
    row.appendChild(col(result.practiceid.toString(),i ++));
    row.appendChild(col(result.memberid.toString(),i++ ));
    row.appendChild(col(result.practicename.toString() ,i++));
    row.appendChild(col(result.success.toString(),i++) );
    row.appendChild(col(result.message.toString(),i++) );
    tmp.appendChild(row);
    }
    }

    var deserializationProperties = SerializerFactory.getDefaultDeserializationPropert ies('DELIMITED');
    var CSV = SerializerFactory.getSerializer('DELIMITED', null, deserializationProperties).fromXML(tmp);

    channelMap.put ('CSV', CSV);

    function col(str,num) {
    return new XML('<column'+num+'>'+escape(str)+'</column'+num+'>');

    }


    function escape(str){
    return str
    .replace(/&/g,'&amp;')
    .replace(/"/g,'&quot;')
    .replace(/'/g,'&apos;')
    .replace(/</g,'&lt;')
    .replace(/>/g,'&gt;');
    };

  • #2
    still struggling to get it to work

    I am a novice when it comes to scripting and mirth.

    I am trying to create a channel which will read from a database and will write to a csv file.

    So far I have done this

    created a channel with source as Database Reader.
    Added the below javascript on the source screen


    -----------------------------------------------
    var dbConn = DriverManager.getConnection(jdbc:mysql://localhost:3306/chinook','root','test');

    importPackage(java.sql);
    new com.mysql.jdbc.Driver();
    var ps = dbConn.executecachedstatement("SELECT * from document");
    var rsmd = ps.getMetaData();
    var msg = <results/>;


    while(ps.next())
    {
    var result = <result/>;
    for (var i = 1; i <= rsmd.getColumnCount(); i++)
    result[rsmd.getColumnName(i)] = ps.getString(i);
    msg.appendChild(result);
    }

    dbConn.close();

    ---------------------------------------------------------------


    then I added a step and have added the below script


    -----------------------------------------------------------------

    tmp = <delimited/>;

    var header = <row/>;
    var i = 1;
    header.appendChild(col('docid',i++));
    header.appendChild(col('encid',i++));
    header.appendChild(col('filename',i++));
    header.appendChild(col('customname',i++));
    header.appendChild(col('scandate',i++));
    header.appendChild(col('description',i++));
    header.appendChild(col('patientid',i++));
    header.appendChild(col('scannedby',i++));
    header.appendChild(col('reviewedby',i++));
    header.appendChild(col('dirpath',i++));
    header.appendChild(col('modifieddatetime',i++));


    tmp.appendChild(header);

    for each (results in msg)
    {

    for each (result in results.children())
    {
    var row = <row/>;
    var i = 1;

    row.appendChild(col(result.docid.toString(),i++));
    row.appendChild(col(result.encidid.toString(),i++) );
    row.appendChild(col(result.filename.toString(),i++ ));
    row.appendChild(col(result.customname.toString(),i ++) );
    row.appendChild(col(result.scandate.toString(),i++ ));
    row.appendChild(col(result.description.toString(), i ++));
    row.appendChild(col(result.patientid.toString(),i+ + ));
    row.appendChild(col(result.scannedby.toString() ,i++));
    row.appendChild(col(result.reviewedby.toString(),i ++) );
    row.appendChild(col(result.dirpath.toString(),i++) );
    row.appendChild(col(result.modifieddatetime.toStri ng(),i++) );
    tmp.appendChild(row);
    }
    }
    var deserializationProperties = SerializerFactory.getDefaultDeserializationPropert ies('DELIMITED');
    var CSV = SerializerFactory.getSerializer('DELIMITED', null, deserializationProperties).fromXML(tmp);

    channelMap.put ('CSV', CSV);

    function col(str,num)
    {
    return new XML('<column'+num+'>'+escape(str)+'</column'+num+'>');

    }


    function escape(str)
    {
    return str
    .replace(/&/g,'&amp;')
    .replace(/"/g,'&quot;')
    .replace(/'/g,'&apos;')
    .replace(/</g,'&lt;')
    .replace(/>/g,'&gt;');
    };

    ------------------------------------------------------------------------

    On destination tab, I have set is as filewriterand template is set as

    ${CSV}


    I am getting below error , not sure what is wrong here.


    [2018-10-06 11:02:00,039] ERROR (com.mirth.connect.connectors.jdbc.DatabaseReceive rScript:123): An error occurred while polling for messages, retrying after 10000 ms...
    com.mirth.connect.connectors.jdbc.DatabaseReceiver Exception: Unrecognized value returned from script in channel "DB2csv", expected ResultSet or List<Map<String, Object>>: [email protected]
    at com.mirth.connect.connectors.jdbc.DatabaseReceiver Script.poll(DatabaseReceiverScript.java:119)
    at com.mirth.connect.connectors.jdbc.DatabaseReceiver .poll(DatabaseReceiver.java:111)
    at com.mirth.connect.donkey.server.channel.PollConnec torJob.execute(PollConnectorJob.java:49)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:2 13)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run (SimpleThreadPool.java:557)

    Comment


    • #3
      The error is because the original solution used a javascript reader, not a database reader. However, that's making things way more complicated than they need to be if you are on mirth version 3.5 or higher that has an option for aggregate results. Depending on what you need to do with the results, there is still probably a better method for versions lower than 3.5.

      Comment

      Working...
      X