Skip to Content
Author's profile photo Michal Krawczyk

PI/XI: Reading MS Excel’s XLSX and XLSM files with standard PI modules – easily…

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.

Assigned Tags

      34 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Shabarish Vijayakumar
      Shabarish Vijayakumar
      I dont understand why the plain xls formats are not supported. Almost all of the worlds leagacy apps use xls and not the newer 2007 format. Unfortunate I say but still at least on the bright side there is support for the newer world 🙂

      Shabz

      Author's profile photo Michal Krawczyk
      Michal Krawczyk
      Blog Post Author
      Hi Shabz 🙂

      >>>What about the old way of things?

      xls is a thing of a past.... I wish 🙂
      for this case you still have to use the old ways of handling it in PI...

      Regards,
      Michal Krawczyk

      Author's profile photo Former Member
      Former Member
      Hi Michal,

      I mean to say .. Do PI7.0 also support this feature to pass (.xlsx excel worksheets).

      Regards
      Prabhat Sharma

      Author's profile photo Michal Krawczyk
      Michal Krawczyk
      Blog Post Author
      Hi,

      >>>I mean to say .. Do PI7.0 also support this feature to pass (.xlsx excel worksheets).

      did I mention anything about PI version ? 🙂
      as long as have those two adapter modules
      (and they are available as of PI 7.0) you can use it 🙂
      so the answer is yes - sure 🙂

      Regards,
      Michal

      Author's profile photo Former Member
      Former Member
      Thanks, I will try it and Get back to U .

      Surely it will be helpful to most of us 🙂

      Regards
      Prabhat

      Author's profile photo Michal Krawczyk
      Michal Krawczyk
      Blog Post Author
      I hope so 🙂

      Regards,
      Michal Krawczyk

      Author's profile photo Former Member
      Former Member
      Hi Michal ,

      I tried to access MS_EXCEL 2007 file from FILE Sender Adapter. but the data I got On Monitor is erronious (encodded Data).



      PK     !ëzÒ“b �  Ü [Content_Types].xml ¢Ø (  ¬TËNÃ0 ¼#ñ ‘¯(qË !Ô´  G¨ |€±7�UǶ¼ÛÒþ=›„V€J¤ª½$Š¢�™� {2Û4.[CB |)ÆÅHdàu0Ö/Jñþö”ߊ Iy£\ðPŠ- ˜M//&oÛ ˜ñ´ÇRÔDñNJÔ54

      What I tried is ,

      1. I created a sample scenario for Material.csv(It worked Fine)

      2. Then I changed the same CC for .xlsx with Same FCC which were used for .csv Only the File name is changed and On Source side Csv file is replaced with .xlsx file but with same rows and column.

      am i going the right way ?

      Author's profile photo Michal Krawczyk
      Michal Krawczyk
      Blog Post Author
      Hi,

      >>>. Then I changed the same CC for .xlsx with Same FCC which were used for .csv Only the File name is changed and On Source side Csv file is replaced with .xlsx file but with same rows and column.

      no it's wrong way - with my approach you don't use FCC
      (you receive XML data as per blog).

      Please read the blog once more to get more clarity

      Regards,
      Michal Krawczyk

      Author's profile photo Former Member
      Former Member
      Hi Michal, On Communication Channel ,

      1.     I have Changed message protocol to File now  instead of FCC.
      2.     And On adapter Module I am giving application/xml;name="xl/worksheets/sheet1.xml" value against swap.KeyValue.
      3.      My file is on /xi  folder on FTP server with filename student.xlsx.

      On clicking Payload in Monitoring : A MessageContentservlet.zip file is Coming . ON click (Open),

      It contains 3 folders :

      1. _rels  - Contains -  .rels

      2. docProps - Contains - app.xml , core.xml

      3. xl - Contains -     _rels            - contains - workbook.xml.rels
                     theme          - contains - theme1.xml
                     worksheets      - contains - sheet1.xml
                     sharedStrings.xml
                     styles.xml
                     workbook.xml

      and Message is failing on MMapping Level. With No Output on Target Side.

      Please Guide . What is Going Wrong?

      Do you want Log from CC monitoring also while picking Up The file ???

      Author's profile photo Michal Krawczyk
      Michal Krawczyk
      Blog Post Author
      Hi,

      >>>On clicking Payload in Monitoring : A MessageContentservlet.zip file is Coming . ON click (Open),

      this means that the unzip didn't work - check the blog again please and CC monitoring it you're unzipping correct file

      Regards,
      Michal Krawczyk

      Author's profile photo Former Member
      Former Member
      Yes I Had read It properly ,

      Still No Success, Massage failed into M-Monitoring

      Now On Monitoring  I am getting Nine Payloads including xl/worksheets/sheet1.xml

      and This Doesn't Contains my Excel Data. 

      But there is a xml payload, which contains some of my excel data but it is erronous (actually i contains all only  Unique value from each cell)

      here is Log in CC monitoring

      Success Send binary file  "xi.xlsx" from FTP server "10.0.0.18:/xi", size 8962 bytes with QoS EO
      Success Zip: unzipping payload
      Success Zip: unzipped payload
      Success Zip: unzipped additional 9 payloads
      Success Zip: successfully processed
      Success Swap: swapping by content-type ? application/xml;name="xl/worksheets/sheet1.xml"
      Success Swap: successfully swapped
      Success Application attempting to send an XI message asynchronously using connection File_http://sap.com/xi/XI/System.
      Success Trying to put the message into the send queue.
      Success Message successfully put into the queue.

      regards
      Prabhat

      Author's profile photo Michal Krawczyk
      Michal Krawczyk
      Blog Post Author
      Hi,

      >>>and This Doesn't Contains my Excel Data.

      I don't know which xml file contains your data
      it should be one of the sheets but just unzip
      the excel locally and check it yourself 🙂
      but it must be in one of the xml files for sure
      unless there is no data in the excel file itself

      Regards,
      Michal Krawczyk

      Author's profile photo Former Member
      Former Member
      Thanks , Now it is working 🙂 Finally
      Author's profile photo S Kumar
      S Kumar

      Hi Prabhat,

      I have placed one xlsx file as text.xlsx on XI server. I am facing issue in CC monitoring as given below:

      Zip: error occured during processing: java.lang.IllegalArgumentException: zip entry name contained non-utf8 chars, try system properties com.sap.jvm.ZipEntry.encoding or sun.zip.encoding

      Could you please suggest on this.

      Thanks!

      Kumar

      Author's profile photo Former Member
      Former Member

      Hi Prabhat,

      Please help on below thread.

      https://scn.sap.com/thread/3765676

      Author's profile photo Abhishek Sharma
      Abhishek Sharma

      Hi Prabhat,

      I know it's been a long time you tried this scenario but I was going through this blog and got stuck with same issue.

      Do you remember what you did that time to resolve this issue?

      Thanks in advance.

      -Abhishek Sharma

      Author's profile photo Former Member
      Former Member

      Hi Michal,

      I tried to read th XLSX file using Payload Swap Bean but it says no matching payload fine as shown.

      13:01:14.984 Information Zip: unzipping payload
      09.07.2012 13:01:14.984 Information Zip: successfully processed
      09.07.2012 13:01:14.984 Information Swap: swapping by content-type ? application/xml;name="xl/Worksheets/Sheet1.xml"
      09.07.2012 13:01:14.984 Information Swap: no matching payload found
      09.07.2012 13:01:14.985 Information Mail: Message leaving the adapter (call)
      09.07.2012 13:01:14.985 Information Application attempting to send an XI message asynchronously using connection AFW

      My Excel sheet which iam trying to read is named as sheet.xlsx and it has 2 sheets named as Sheet1 and Sheet2 , Sheet1 as the content which has to be converted into XML.

      Help me out if anything is missing from my side.

      Thanks for your help.

      Thanks,

      Kiran

      Author's profile photo Nagesh Chepuri
      Nagesh Chepuri

      Hi,

      I am also facing same issue. Did you get the solution . If you got solution please provide solution.

      Regards,

      NAgesh

      Author's profile photo indumathi maturi
      indumathi maturi

      Hi All,

       

      I am also facing same issue . kindly help

      Swap: no matching payload found

      Thanks

      Indu

      Author's profile photo S Kumar
      S Kumar

      Hi Michal,

      I have gone through with your above blog. It is really helpful. I am using SAP PI 7.1 SP 14. I have some query regarding this.

      1.       Are the given Adapter modules are standard or we have to write any customized code for this?

      2.       Can it be used for file or FTP adapter in PI 7.1 or not?

      3.       Can we have any blog for outbound scenario for XML to XLSX?

      Thanks in advance and appreciate your response asap.

      Shivdeep

      Author's profile photo Former Member
      Former Member

      Dear Michal,

      Please help with the below thread. Thanks.

      https://scn.sap.com/thread/3765676

      Author's profile photo Former Member
      Former Member

      Hi MichaI am facing an issue with the same excel file reading in PI 7.4 stack.The unzip and swap worked and the payload has mulitple contents,but the data is not there as expected.Can you please help,as to how the data would be displayed.Is this possible in 7.4?

      Author's profile photo Former Member
      Former Member
      Hi Michal,

      Thank you for the post.

      How do you handle multiple worksheets in the one module?

      Can we have multiple Beanprocessor modules for multiple worksheets in one Fileadpater?

      Thank you

      Author's profile photo Manikandan Shanmugam
      Manikandan Shanmugam

      Hi Michal,

      I am Using Mail adapter but no use still i m getting the Excel file as a payload in IE.

      Kindly find below Module Configuration and let me know if any mistake

      Processing Sequence:

      No  Module name                                                Module Type                   Modulekey

      1    localejbs/AF_Modules/PayloadZipBean             L E B                                   1

      2    localejbs/AF_Modules/PayloadSwapBean         L E B                                    2

      3   sap.com/com.sap.aii.adapter.mail.app/XIMailAdapterBean   L E B                  mail

      Module Configuration:

      Modulekey                 parameter Name     parameter Value

         1                                zip.mode                      unzip

         2                           swap.keyName               content-type

         2                    swap.keyName       application/xml;name="xl/Worksheets/sheet1.xml"

      Thanks & Regards,

      Manikandan

      Author's profile photo P. Willemsen
      P. Willemsen

      I know I'm replying to a one-year-old question, but anyway... Your xlsx probably comes as an attachment to the email? Then you first need to use the PayloadSwapBean to select the attachment as payload.

      Author's profile photo S Kumar
      S Kumar

      Hi Michal,

      I have gone through with your above blog. It is really helpful. I have same scenario XLSX to XML and I am using SAP PI 7.1 SP 14. I have some query regarding this.

      1.       Are the given Adapter modules are standard or we have to write any customized code for this?

      2.       Can it be used for file or FTP adapter in PI 7.1 or not?

      3.       Can we have any blog for outbound scenario for XML to XLSX?

      Thanks in advance and appreciate your response asap.

      Regards,

      Shivdeep

      Author's profile photo P. Willemsen
      P. Willemsen

      Hi Shivdeep,

      Answers to your questions:

      1. These are standard adapter modules.

      2. Yes

      3. Use the SCN search functionality 🙂

      KR,

      Pascal

      Author's profile photo P. Willemsen
      P. Willemsen

      For anyone creating their own xsd: Stop!

      You can download the standard here: http://www.ecma-international.org/publications/standards/Ecma-376.htm

      You only need Part 1, which contains OfficeOpenXML-XMLSchema-Strict.zip, which in turn contains sml.xsd.

      Kind regards,

      Pascal

      Author's profile photo Evgeniy Kolmakov
      Evgeniy Kolmakov

      Hi, Michal!

      Thank you for your helpful blog! One thing: non-numerical data in my excel worksheet is stored in xl\sharedStrings.xml inside xslx-file. So I can't just extract one xml from archive to use it in message mappings.

      Didn't you faced with such situation? What could I do with it?

      Author's profile photo Former Member
      Former Member

      Hi Michael,

      Your blog is really helpful...but I am not able to create XML files from XLSX files. The data in xlsx files is not getiing captured. Request you to helpCapture.PNG

      Author's profile photo Former Member
      Former Member

      Hi Nav..

      Did you happen to get the data?

      Author's profile photo Former Member
      Former Member

      Hi Michal Krawczyk ,


      Facing error in SXMB_MONI on using above steps. Please help. Many Thanks....!!



      /wp-content/uploads/2015/07/ddddddd_743546.jpg/wp-content/uploads/2015/07/dhyttrrr_743547.jpg

      Author's profile photo Former Member
      Former Member

      Hi Michael,
      Thank you for the informative blog.

      I implemented your approach, it works just fine but with an exception that my data is coming in sharedStrings.xml.

      Issue crops up when the duplicate values in cells are removed while unzipping the xlsx  and data is getting lost. Do we have any workaround for this?

      Please enlighten.

      Thanks,
      Indu Khurana.

      Author's profile photo ENRICO BARSALI
      ENRICO BARSALI

      You can access the xl/sharedStrings.xml attachment using a Java mapping and adding the content at the end of the swapped main document, so then you can get values using the indexes of the sheet that you are processing. This could be done also performing a graphical mapping with an extended XSD structure (that allow sharedStrings format). Prerequisite is PI>=7.1 (https://archive.sap.com/discussions/thread/1234143)

      Best Regards

      Enrico