Announcement

Collapse
No announcement yet.

Excel As Data Source

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

  • Excel As Data Source

    For those interested it is possible via the JExcel Api to use an Excel file as a data source in Mirth.

    1. Move attached jxl.jar to C:\Program Files\Mirth Connect\custom-lib\
    2. Restart Mirth
    3. Create a new channel with the follow as the JavaScript Reader data source:

      Code:
      //List to store each Excel row in
      var excelData = new Packages.java.util.ArrayList();
      
      //Open an Excel file
      var excelFile = new Packages.java.io.File("C:/Users/your_user_name/Documents/name_of_file.xls");
      var excel = Packages.jxl.Workbook.getWorkbook(excelFile);
      
      //Read in the first sheet
      var sheet = excel.getSheet(0);
      
      //Variables for the current row and column
      var myRow = 1;
      var myCol = 0;
      
      //Variable for the last row or last column
      var rowStop = 0;
      var colStop = 0;
      
      while (rowStop == 0) {
          var msgString = '<result>';
          while (colStop == 0) {
              //See if this column is valid
              //If so, write the cell
              try {
                  //logger.error(sheet.getCell(myCol, myRow).getContents());
                  msgString = msgString + '<col' + myCol + '>' + sheet.getCell(myCol, myRow).getContents() + '</col' + myCol + '>';
              }
              //If not, stop processing at this column and move on to the next row.
              catch(ex) {
                  colStop = 1;    
              }
              myCol++;
          }
          myRow++;
          myCol = 0;
          colStop = 0;
      
          //See if the next row is valid
          try {
              sheet.getCell(myCol, myRow);
          }
          //If not, stop processing the Excel file.
          catch(ex) {
              rowStop = 1;    
          }
      
          msgString = msgString + '</result>';
      
          //Clear out invalid XML characters
          msgString = msgString.replace(/&/g, '&amp;');
          msgString = msgString.replace(/\'/g, '&apos;');
          msgString = msgString.replace(/\"/g, '&quot;');
      
          //Add the message to the message list
          var msgXML = new XML(msgString.toString());
          excelData.add(msgXML.toString());
      }
      
      //Close the Excel file
      excel.close()
      
      return excelData;
    4. Destinations, etc. will be done as normal. Your inbound message template should look something like the following:

      Code:
      <result>
          <col0></col0>
          <col1></col1>
          <col2></col2>
          <col3></col3>
          <col4></col4>
          <col5></col5>
      </result>

    This is all working for me on my Windows 7 64-bit machine. Your mileage may vary. Make sure you set the data types in the summary tab to XML!
    Attached Files
    Last edited by marmanold; 05-25-2011, 06:15 AM.
    Michael Arnold| Electronic Data Interchange Specialist

    PlayMaker CRM
    Sales Innovation for Home Care
    A Division of The ADAM Group
    115 Southeast Parkway Court | Franklin, TN 37064
    866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com

  • #2
    Excel writer

    That looks pretty easy. I was looking at http://poi.apache.org/spreadsheet/index.html which looks like it can do 2007/2010 xml formats but I never figured out how to use it with Mirth. I need to be able to write Excel files.
    I think it would be really nice if we could make that a file connector but I don't know enough about programming to be able to do that and give to the community.

    Comment


    • #3
      Excel as File Destination

      Having Excel as a file destination is definitely possible with this module. I had a small proof of concept somewhere, but I can't find it at the moment. Basically you'd just loop through each row and column of the message writing each data element to the coordinating cell in Excel.
      Michael Arnold| Electronic Data Interchange Specialist

      PlayMaker CRM
      Sales Innovation for Home Care
      A Division of The ADAM Group
      115 Southeast Parkway Court | Franklin, TN 37064
      866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com

      Comment


      • #4
        can you use this for sample patient data?

        Please bear with me - I'm a Mirth newbie.
        What kind of data would you enter in your inbound Excel message template?
        I'm interested in running some bench tests using sample patient data - lab results, notes, etc. but have no good data source to pass through Mirth.
        Thx,
        Lou

        Comment


        • #5
          The javascript is written to be generic. You can provide it with any Excel (.xls only) file and it will read in all the data on the first worksheet.

          You shouldn't put any data in the inbound message template. It is simply the template used for the transformer in your destinations. You will put a <coln></coln> tag for each column used in your Excel report. Because this is read in as an array it's zero-based, so you start counting at zero. (E.g., column one is <col0></col0>, column two is <col1></col1>, etc.)

          I can tell you that the Java library is a little memory/processor intensive. I was hitting 250MB memory used and 50% processor used while it was processing. I would say it took about five minutes or so to read my 319KB Excel file with about 500 rows.
          Michael Arnold| Electronic Data Interchange Specialist

          PlayMaker CRM
          Sales Innovation for Home Care
          A Division of The ADAM Group
          115 Southeast Parkway Court | Franklin, TN 37064
          866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com

          Comment


          • #6
            Get an error when running the channel

            Hi,

            I've tried to follow your instructions, but when I try to use the Channel I reveiced an error saying:

            Code:
            [2011-07-08 09:55:32,817]  
            FATAL (org.mule.impl.DefaultComponentExceptionStrategy:84): The error is: null
            java.lang.NullPointerException
            	at com.webreach.mirth.connectors.js.JavaScriptMessageReceiver.poll(JavaScriptMessageReceiver.java:88)
            	at org.mule.providers.PollingMessageReceiver.run(PollingMessageReceiver.java:90)
            	at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290)
            	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
            	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
            	at java.lang.Thread.run(Unknown Source)
            [2011-07-08 09:54:52,754]  
            FATAL 
            (org.mule.impl.DefaultComponentExceptionStrategy:83): The Default Component Exception Strategy has been invoked but there is no current event on the context
            [2011-07-08 09:51:07,408]  
            ERROR 
            (com.webreach.mirth.connectors.js.JavaScriptMessageReceiver:152): org.mozilla.javascript.EcmaError: TypeError: Cannot call property getWorkbook in object [JavaPackage jxl.Workbook]. It is not a function, it is "object". (62c52ab0-7111-48f0-bafc-db7bccfbb578#9)
            Any hints for me?

            Thanks, Regards,
            - Bernd

            Comment


            • #7
              Originally posted by bernd05 View Post
              Hi,

              I've tried to follow your instructions, but when I try to use the Channel I reveiced an error saying:

              Code:
              [2011-07-08 09:55:32,817]  
              FATAL (org.mule.impl.DefaultComponentExceptionStrategy:84): The error is: null
              java.lang.NullPointerException
              	at com.webreach.mirth.connectors.js.JavaScriptMessageReceiver.poll(JavaScriptMessageReceiver.java:88)
              	at org.mule.providers.PollingMessageReceiver.run(PollingMessageReceiver.java:90)
              	at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290)
              	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
              	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
              	at java.lang.Thread.run(Unknown Source)
              [2011-07-08 09:54:52,754]  
              FATAL 
              (org.mule.impl.DefaultComponentExceptionStrategy:83): The Default Component Exception Strategy has been invoked but there is no current event on the context
              [2011-07-08 09:51:07,408]  
              ERROR 
              (com.webreach.mirth.connectors.js.JavaScriptMessageReceiver:152): org.mozilla.javascript.EcmaError: TypeError: Cannot call property getWorkbook in object [JavaPackage jxl.Workbook]. It is not a function, it is "object". (62c52ab0-7111-48f0-bafc-db7bccfbb578#9)
              Any hints for me?

              Thanks, Regards,
              - Bernd
              Unfortunately Mirth errors are rather cryptic. The "function not an object" error is something I've seen from time to time working with JAVA classes from within Mirth. It usually either means the JAVA class isn't loaded (Mirth hasn't been restarted since the .jar file was installed) or that the method isn't being used correctly.

              You could try instantiating the object before using it. Something like:
              Code:
              var excelBook = new Packages.jxl.Workbook;
              var excel = excelBook.getWorkbook(excelFile);
              I also just realized that I left out the part in the code where I import the JAVA package. I believe the following is what I do in my channel. (I have another client Mirth setup installed at the moment, so I can't verify.)
              Code:
              importPackage(Packages.jxl.Workbook);
              That might fix the issue as well.

              Viel Gl├╝ck!
              Michael Arnold| Electronic Data Interchange Specialist

              PlayMaker CRM
              Sales Innovation for Home Care
              A Division of The ADAM Group
              115 Southeast Parkway Court | Franklin, TN 37064
              866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com

              Comment


              • #8
                I've updated to the newest version of Mirth and now everything is fine.

                Thanks for your reply.

                Regards,
                - Bernd

                Comment


                • #9
                  Has anyone gotten this to work?

                  I am trying to write an excel file using this (from the tutorial on the API)

                  Code:
                  importPackage(Packages.jxl.Workbook);
                  
                  WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls")); 
                  WritableSheet sheet = workbook.createSheet("First Sheet", 0); 
                  
                  Label label = new Label(0, 0, "Name");
                  sheet.addCell(label);
                  Label label = new Label(1, 0, "Value");
                  sheet.addCell(label);
                  
                  for(count=0; count<$('resultVal'); count++)
                  {
                  
                  Label label = new Label(0, count, $('resultName')[count]);
                  sheet.addCell(label);
                  
                  Number number = new Number(1, count, $('resultVal')[count]);
                  sheet.addCell(number); 
                  }
                  Any help?

                  Comment


                  • #10
                    Your code is in Java. You will have to translate it to Javascript for it to work in Mirth.
                    Michael Arnold| Electronic Data Interchange Specialist

                    PlayMaker CRM
                    Sales Innovation for Home Care
                    A Division of The ADAM Group
                    115 Southeast Parkway Court | Franklin, TN 37064
                    866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com

                    Comment


                    • #11
                      Hi All,
                      I got a error as below when I used jxl.jar library:
                      "Wrapped jxl.read.biff.PasswordException: The workbook is password protected"
                      When I opened in hand, excel file didn't require a password.

                      After read this file, I want to delete it. Can I do it and how?
                      Thanks,
                      Huy
                      Last edited by doanhuy; 07-27-2011, 08:12 PM.

                      Comment


                      • #12
                        Hi,

                        This works great, the only thing is dat it keeps looping through the excel file because the source connector is polling every 5 secs. What I want is to stop processing the excel file once it reached the last row. Any ideas?

                        Casper

                        Comment


                        • #13
                          I would add some additional code at the end to move the file to an archive directory after processing.
                          Michael Arnold| Electronic Data Interchange Specialist

                          PlayMaker CRM
                          Sales Innovation for Home Care
                          A Division of The ADAM Group
                          115 Southeast Parkway Court | Franklin, TN 37064
                          866.930.6847 | F: 866.232.6470 | PlayMakerCRM.com

                          Comment


                          • #14
                            excel read

                            Hi I am trying this code to read exel file and I am getting error below. please help.


                            ======== code =============

                            importPackage(Packages.jxl.Workbook);

                            //List to store each Excel row in
                            var excelData = new Packages.java.util.ArrayList();


                            //Open an Excel file
                            var excelBook = new Packages.jxl.Workbook;
                            var excel = excelBook.getWorkbook(excelBook);

                            var excelFile = new Packages.java.io.File("C:\Users\Test\Desktop\New folder\test.xls");
                            excel = Packages.jxl.Workbook.getWorkbook(excelFile);



                            //Read in the first sheet
                            var sheet = excel.getSheet(0);

                            //Variables for the current row and column
                            var myRow = 1;
                            var myCol = 0;

                            //Variable for the last row or last column
                            var rowStop = 0;
                            var colStop = 0;

                            while (rowStop == 0) {
                            var msgString = '<result>';
                            while (colStop == 0) {
                            //See if this column is valid
                            //If so, write the cell
                            try {
                            //logger.error(sheet.getCell(myCol, myRow).getContents());
                            msgString = msgString + '<col' + myCol + '>' + sheet.getCell(myCol, myRow).getContents() + '</col' + myCol + '>';
                            }
                            //If not, stop processing at this column and move on to the next row.
                            catch(ex) {
                            colStop = 1;
                            }
                            myCol++;
                            }
                            myRow++;
                            myCol = 0;
                            colStop = 0;

                            //See if the next row is valid
                            try {
                            sheet.getCell(myCol, myRow);
                            }
                            //If not, stop processing the Excel file.
                            catch(ex) {
                            rowStop = 1;
                            }

                            msgString = msgString + '</result>';

                            //Clear out invalid XML characters
                            msgString = msgString.replace(/&/g, '&amp;');
                            msgString = msgString.replace(/\'/g, '&apos;');
                            msgString = msgString.replace(/\"/g, '&quot;');

                            //Add the message to the message list
                            var msgXML = new XML(msgString.toString());
                            excelData.add(msgXML.toString());
                            }

                            //Close the Excel file
                            excel.close()

                            return excelData;




                            ==============ERROR ===================

                            [2013-08-27 12:33:17,802] ERROR (com.mirth.connect.connectors.js.JavaScriptReceive r:94): Error executing JavaScript Reader script 32f9f5d6-9b5a-4747-aedf-b1f9fad9ebf4.
                            com.mirth.connect.server.util.javascript.JavaScrip tExecutorException: com.mirth.connect.server.MirthJavascriptTransforme rException: CHANNEL:
                            xl_to_xmlCONNECTOR:
                            SourceSOURCE CODE:
                            10: return AttachmentUtil.createAttachment(connectorMessage, data, type);11: }12: function validate(mapping, defaultValue, replacement) {var result = mapping;if ((result == undefined) || (result.toString().length == 0)) { if (defaultValue == undefined) { defaultValue = ''} result = defaultValue; } result = new java.lang.String(result.toString()); if (replacement != undefined) {for (i = 0; i < replacement.length; i++) { var entry = replacement[i]; result = result.replaceAll(entry[0], entry[1]); } } return result; }function createSegment(name, msgObj, index) {if (arguments.length == 1) { return new XML('<' + name + '></' + name + '>'); };if (arguments.length == 2) { index = 0; };msgObj[name][index] = new XML('<' + name + '></' + name + '>');return msgObj[name][index];}13: function createSegmentAfter(name, segment) {var msgObj = segment;while (msgObj.parent() != undefined) { msgObj = msgObj.parent(); }msgObj.insertChildAfter(segment[0], new XML('<' + name + '></' + name + '>'));return msgObj.child(segment[0].childIndex() + 1);}14: function doScript() {15: importPackage(Packages.jxl.Workbook);16: //importPackage(JavaClassjxl.Workbook);17: 18: //List to store each Excel row in19: var excelData = new Packages.java.util.ArrayList();LINE NUMBER:
                            15DETAILS:
                            Function importPackage must be called with a package; had "[JavaClass jxl.Workbook]" instead.
                            at 32f9f5d6-9b5a-4747-aedf-b1f9fad9ebf4:15 (doScript)
                            at 32f9f5d6-9b5a-4747-aedf-b1f9fad9ebf4:87
                            at com.mirth.connect.server.util.javascript.JavaScrip tUtil.execute(JavaScriptUtil.java:62)
                            at com.mirth.connect.connectors.js.JavaScriptReceiver .poll(JavaScriptReceiver.java:92)
                            at com.mirth.connect.donkey.server.channel.PollConnec tor$PollConnectorTask.run(PollConnector.java:125)
                            at java.util.TimerThread.mainLoop(Unknown Source)
                            at java.util.TimerThread.run(Unknown Source)Caused by: com.mirth.connect.server.MirthJavascriptTransforme rException: CHANNEL:
                            xl_to_xmlCONNECTOR:
                            SourceSOURCE CODE:
                            10: return AttachmentUtil.createAttachment(connectorMessage, data, type);11: }12: function validate(mapping, defaultValue, replacement) {var result = mapping;if ((result == undefined) || (result.toString().length == 0)) { if (defaultValue == undefined) { defaultValue = ''} result = defaultValue; } result = new java.lang.String(result.toString()); if (replacement != undefined) {for (i = 0; i < replacement.length; i++) { var entry = replacement[i]; result = result.replaceAll(entry[0], entry[1]); } } return result; }function createSegment(name, msgObj, index) {if (arguments.length == 1) { return new XML('<' + name + '></' + name + '>'); };if (arguments.length == 2) { index = 0; };msgObj[name][index] = new XML('<' + name + '></' + name + '>');return msgObj[name][index];}13: function createSegmentAfter(name, segment) {var msgObj = segment;while (msgObj.parent() != undefined) { msgObj = msgObj.parent(); }msgObj.insertChildAfter(segment[0], new XML('<' + name + '></' + name + '>'));return msgObj.child(segment[0].childIndex() + 1);}14: function doScript() {15: importPackage(Packages.jxl.Workbook);16: //importPackage(JavaClassjxl.Workbook);17: 18: //List to store each Excel row in19: var excelData = new Packages.java.util.ArrayList();LINE NUMBER:
                            15DETAILS:
                            Function importPackage must be called with a package; had "[JavaClass jxl.Workbook]" instead.
                            at 32f9f5d6-9b5a-4747-aedf-b1f9fad9ebf4:15 (doScript)
                            at 32f9f5d6-9b5a-4747-aedf-b1f9fad9ebf4:87
                            at com.mirth.connect.server.util.javascript.JavaScrip tUtil.executeScript(JavaScriptUtil.java:466)
                            at com.mirth.connect.connectors.js.JavaScriptReceiver $JavaScriptReceiverTask.call(JavaScriptReceiver.ja va:117)
                            at java.util.concurrent.FutureTask$Sync.innerRun(Unkn own Source)
                            at java.util.concurrent.FutureTask.run(Unknown Source)
                            at java.util.concurrent.ThreadPoolExecutor$Worker.run Task(Unknown Source)
                            at java.util.concurrent.ThreadPoolExecutor$Worker.run (Unknown Source)
                            at java.lang.Thread.run(Unknown Source)

                            Comment


                            • #15
                              How to read the excel file throw mirth connect

                              How to read the excel file through mirth connect? please share the channel.

                              Comment

                              Working...
                              X