Excel to CSV

[joshmc82]

User "Amit Cudykier" in the Mirth Connect slack posted some great code on how to read an Excel spreadhsheet and convert it to CSV using Mirth Connect. Sample POC Channel is attached.

Prerequisites:

• Apache POI binaries (https://poi.apache.org/). I used the latest version. At the time of posting, that was 5.2.3
• Folder on your Mirth Connect server that is readable by the local user account running the mirth server. You will need this to transfer your jar files. (i.e. resources/poi)

Steps to Install:

1. Extract the POI archive to your local workstation
2. Tansfer all of the .jar files from your local poi-bin-x.y root folder to the remote folder you created above (i.e. resources/poi)
3. Transfer the entire ooxml-lib folder (and contents) from the local poi-bin folder to your remote folder from above.
4. Transfer the entire lib folder (and contents) from the local poi-bin folder to your remove folder from above.
5. From your Mirth Connect Administrator, create a new resource and point it to your folder from above that should now contain all of the jars. (Settings -> Resources -> Add Resource)
6. On your channel, make sure you set your depenancy for your new resource (Summary Tab -> Set Dependencies)

In case this channel fails to load, here is the code extract from the source transformer:
importPackage(org.apache.xmlbeans); importPackage(org.apache.commons.io); importPackage(org.apache.poi); importPackage(org.apache.poi.ooxml); var iso = Packages(contextFactory.getIsolatedClassLoader()); var strByte = new java.io.ByteArrayInputStream(FileUtil.decode(connectorMessage.getRawData())); var wb = new iso.org.apache.poi.ss.usermodel.WorkbookFactory.create(strByte) var sheet = wb.getSheetAt(0); var rows = sheet.getLastRowNum(); var cols = sheet.getRow(0).getLastCellNum(); var xml = new XML(<delimited/>); for (var r=0;r<=rows;r++){ var row = sheet.getRow(r); var xmlRow = <row/>; for (var c=0;c<cols;c++){ var cell = row.getCell(c); var cellValue = cell.getStringCellValue(); var xmlCell = <column>{cellValue}</column>; xmlRow.appendChild(xmlCell); } xml.appendChild(xmlRow); } tmp=xml;
Excel to CSV.zip