How to read xlsx files in Mirth

Question

I am new to mirth and trying to see if anyone is knowledgeable on how mirth handles excel files.

Right now, My mirth channel works great with zip files, csv, and txt files but not xlsx. When it encounters an xlsx file it errors out.

[2022-02-08 09:37:00,037]  ERROR  (com.mirth.connect.donkey.server.channel.Channel:1288): Error processing message in channel xlsx (5447c43e-7aee-4ade-9460-d490edef7f04).
com.mirth.connect.donkey.server.channel.ChannelException: 
    at com.mirth.connect.donkey.server.channel.Channel.dispatchRawMessage(Channel.java:1243)
    at com.mirth.connect.donkey.server.channel.SourceConnector.dispatchRawMessage(SourceConnector.java:192)
    at com.mirth.connect.donkey.server.channel.SourceConnector.dispatchRawMessage(SourceConnector.java:170)
    at com.mirth.connect.connectors.file.FileReceiver.processFile(FileReceiver.java:418)
    at com.mirth.connect.connectors.file.FileReceiver.processFiles(FileReceiver.java:328)
    at com.mirth.connect.connectors.file.FileReceiver.poll(FileReceiver.java:239)
    at com.mirth.connect.donkey.server.channel.PollConnectorJob.execute(PollConnectorJob.java:49)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)Caused by: com.mirth.connect.donkey.server.data.DonkeyDaoException: org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00
    at com.mirth.connect.donkey.server.data.jdbc.JdbcDao.insertContent(JdbcDao.java:287)
    at com.mirth.connect.donkey.server.data.jdbc.JdbcDao.insertMessageContent(JdbcDao.java:199)
    at com.mirth.connect.donkey.server.data.buffered.BufferedDao.executeTasks(BufferedDao.java:110)
    at com.mirth.connect.donkey.server.data.buffered.BufferedDao.commit(BufferedDao.java:85)
    at com.mirth.connect.donkey.server.data.buffered.BufferedDao.commit(BufferedDao.java:72)
    at com.mirth.connect.donkey.server.channel.Channel.dispatchRawMessage(Channel.java:1215)
    ... 8 moreCaused by: org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135)
    at com.mirth.connect.donkey.server.data.jdbc.JdbcDao.insertContent(JdbcDao.java:284)
    ... 13 more ```

Answer 1

At present, Mirth connect is not able to natively read Excel files. You might want to do a custom java class for this then import the jar file into the custom-lib. Overall, it all depends on what you wish to achieve. For instance, you can loop through and grab the Excel files using file reader on the source connector then send via HTTP sender on a Channel's destination connector, to any HTTP end point (i.e. REST API app in PHP or R) capable of processing the Excel files, and returning JSON/XML responses if needed. If all you want is a saving entries to a database, you can consider using a command such as executeRuntimeCommand("C:\Windows\System32\cmd.exe", "/C", "C:\sql_save.bat") inside a transformer step to launch a Windows batch script to do the save for you.

Answer 2

The obvious answer in the Java world (which you can call from Mirth) doesn't work. Poi, the standard framework to read from Office files can't be used. It has lots of dependencies, most of which are already inside Mirth. However, no version of Poi seems to match what Mirth has loaded. Thought I could work the dependencies such that I would get a fresh classloader, but apparently not.