Skip to Content

A Simple approach in Reading Excel File

 

            Hi folks! Working with Adapter-modules has always been challenging and great to use for typical business requirements dealing with Excel files. Excel files carry most of the financial data in current business landscapes. Being one among those who faced such challenge, here I would like to share one of my recent works on Adapter module that deals with Excel files.

 

This blog explains the way an adapter module can be designed in a simple way and this is unique in the manner that it is generic in nature converting .xls and .xlsx files into XML for SAP PI to process.

  Here we go!  All you need to do is, just follow the below steps.


1.     Create the Data Type for the excel sheet that you want to read.


/wp-content/uploads/2014/09/dt_549628.jpg

2.    Techie Buds, please follow the below link to know basics of creating an Adapter module in SAP PI. 

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0b39e65-981e-2b10-1c9c-fc3f8e6747fa?overridelayout=true


3.          Add the below Jars to your module

    

           poi-3.9.jar

          poi-ooxml-3.9.jar

          xmlbeans-2.3.0.jar

4.    Add the below attached java code in your project


5.     Configure the Sender channel parameters as per your data structure.

cc.PNG


UDF : In my business scenario I created the below  UDF ,which maps the first occurrence of the field in XML (Cell Data) to Field 1 (Project Id) on the target side ,2nd occurrence(Cell Data[2]) to Field 2 (Project Engineer). This way all the data in the XML file will be mapped to the actual fields on the target side for further processing. Isn’t it a simple one?


mmping.PNG


Testing:

Let’s test a file of .xls and .xlsx by placing in the FTP server for PI to pick.

file1.PNG


ftpinp.PNG


Output XML file equivalent to EXCEL file read by the module , made available in Target FTP.

ftpout.PNG

XML File :

final.PNG


  How did this approach help me?

  • By using this module I converted my Actual Excel file to an intermediate XML file.
  • The XML file is just like any other XML that we use in File based scenarios.
  • Excel file based scenario is now turned to XML file based scenario using this scenario.
  • The Adapter module has become a reusable one, as most of the financial data will have similar format the same can be used.


15 Comments
You must be Logged on to comment or reply to a post.