web stats
Excel or Delimited File to SQL Destination - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-27-2017, 10:15 AM
ntaylor ntaylor is offline
What's HL7?
 
Join Date: Oct 2012
Posts: 5
ntaylor is on a distinguished road
Post 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 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!
Reply With Quote
  #2  
Old 06-09-2017, 04:37 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 588
kirbykn2 is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 06-09-2017, 11:11 AM
ryadical ryadical is offline
OBX.1 Kenobi
 
Join Date: Aug 2016
Posts: 36
ryadical is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
delimited, excel, sql, xls, xlsx

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 01:44 PM.


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