Announcement

Collapse
No announcement yet.

Excel or Delimited File to SQL Destination

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

  • Excel or Delimited File to SQL Destination

    Hello,
    I would like to take data from and excel spreadsheet Source, and update a SQL table Destination.
    I've tried saving the spreadsheet as tab delimited text file and setting the Source and Destination Data Types to Text Delimited, and I've also tried with Destination Data Type of XML.

    I created a Source File Reader, with and without the following Transformer
    SerializerFactory.getSerializer('DELIMITED').toXML (message);


    Then I created a Destination with variations of the following JavaScript code that I've used previously to write to a SQL database. When I used a version of this code previously though, I was reading from an HL7 file, so I'm not sure how to make it work with Excel or Delimited file.

    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://192.168.xxx.xxx:1433/CentricityPS','xxxxx','xxxxx');

    //var msg = new XML(SerializerFactory.getHL7Serializer().toXML(con nectorMessage.getEncodedData()));
    //var msg = new XML(SerializerFactory.getSerializer('Delimited').t oXML(connectorMessage.getEncodedData()));
    var msg = connectorMessage.getTransformedData()


    var vFee = msg['row']['column10'].toString();


    function encodeSQLString(str1) {
    return str1.replace("'", "''");
    }

    var result = dbConn.executeUpdate("INSERT IGNORE INTO _CUS_TEST_ChargeMasterImport (Fee) VALUES (vFee)");
    dbConn.close();


    I'm pretty new to more advanced, non HL7 and Javascript functions of Mirth. I was hoping someone could help me determine a way to create a Source that reads rows from an excel document, and for each row, I need to update some of the fields to a SQL database.
    My plan is to have an end user copy a file with the data to a directory that I will read, so I would like to make the process as easy as possible for them. The original data that they have exists in an excel format, so if there is a way to read an excel document without saving as a delimited file, I'd probably prefer to do that, but if not, whatever I can get to work, will be better than what I've got now.
    Thanks for any help you may be able to provide!

  • #2
    If you search forums, there is information about reading Excel files.

    http://www.mirthproject.org/communit...ght=read+excel.

    I have done a lot of work with CSV's but not Excel. Familiarize yourself with the batch options, "Set Data Types", and "Inbound and Outbound Properties".

    Unless there is a compelling reason, I would have the users save as a CSV.
    Best,

    Kirby

    Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

    Appliance Version 3.11.4
    Mirth Connect Version 3.8.0
    Java Version 1.6.0_45-b06
    Java (64 bit) Version 1.6.0_45-b06
    Java 7 (64 bit) Version 1.7.0_151-b15
    Java 8 (64 bit) Version 1.8.0_181-b13
    PostgreSQL Version 9.6.8

    Comment


    • #3
      I am not sure why you need the serializer code.

      Performing this with a CSV is the easiest.

      In your Summary change your inbound connector to "Delimited Text" and make sure your column and record delimiters are set correctly and set the "Split batch by" to record. Also, if you have a header row set the "Number of Header Records".
      In your source file reader, make sure process batch is set to yes.

      For testing you can setup a channel with just these settings and try to process a file through and see if it separates each line correctly into it's own message.

      Comment

      Working...
      X