PI/XI: Reading MS Excel's XLSX and XLSM files with standard PI modules - easily...

[MichalKrawczyk]

There are many ways to read MS Excel files into PI:

- you can use Conversion Agent - which requires additional tool/license

- you can write your own adapter module that will parse the MS Excel's file into a readable XML format - but you need to build it yourself first

- you can send the data in an XML format to the ABAP engine and parse it there - but you loose message visibility in PI engine as you're passing binary data this way

- you can have your own tool that will parse the MS Excel's file before PI starts processing it - but then you have one more tool to monitor for errors


What if there would be a way to read excel files by using the SAP provided set of adapters ? IT turns out there is - the only drawback is that this method can only be used with the new MS Office 2007 files for Excel those would be: XSLX and XSLM for example. As some of you might know XSLX and XSLM are zipped XML files so there are two things we'd need to do in order to read a worksheet from a file like this:

1. At first we need to unzip the zipped XSLX or XSLM - we can do this using our standard PayloadZipBean

2. Next we need to make sure we're using correct worksheet in our mapping - again we can use a standard PayloadSwapBean module to facilitate this task
 
Let's see how it works now in configuration:

a) at first we need to configure PayloadZipBean to unzip the excel files by using the zip.mode parameter - unzip



b) as our MS Excel file contains multiple XML files inside and we only want to use one worksheet for our mapping we need to use PayloadSwapBean to switch our payload to a correct XML file. We can achieve this by using parameters keyName - content type and keyValue - application/xml;name="xi/worksheets/sheet1.xml" if we'd like to read the first worksheet.

The module configuration would look like shown below:

This way our mapping will receive all data from the sheet in an XML format. The only thing that's left is to create an XSD file from the XML file we received in order to be able to use it in the mapping and as our Service Interface and we can proceed with mapping. As you can see from the sheet.xml files all the data is placed with column name and row number so it's not that difficult to map it to an table type format using the Message Mapping only (no java, abap mapping required). 


Note:

This method obviously does not only work with File adapter but can also be used with Mail adapter to read MS Excel's attachments or with any other Java based adapter that has the possibility to pass MS Excel files.


I hope this method will save some of you time to develop special handling for MS Excel.