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.


To report this post you need to login first.

14 Comments

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

      1. Rajesh PS

        Hi Srikanth Kakani,

        I have implemented above steps and one to one mapping is done.

        But facing error in comm channel.

        Conversion from .xlxs to .XML or .csv

        https://scn.sap.com/message/16075007

        GRTEEEE.jpg

        Input Payload:

        /wp-content/uploads/2015/07/freww_741270.jpg

        Error:

        Time Stamp Status Description
        2015-07-06 16:30:50 Success Send binary file  “storeXL.xlsx” from FTP server “082.163.0033.13320:/homeUser/STORE_DATA/test_SENDER/”, size 12004 bytes with QoS EO
        2015-07-06 16:30:50 Error Attempt to process file failed with com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Object not found in lookup of ExceltoXML.
        2015-07-06 16:31:00 Success Send binary file  “storeXL.xlsx” from FTP server “082.163.0033.13320:/homeUser/STORE_DATA/test_SENDER/”, size 12004 bytes with QoS EO
        2015-07-06 16:31:00 Error Attempt to process file failed with com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Object not found in lookup of ExceltoXML.
        (0) 
        1. srikanth kakani Post author

          Hi Rajesh,

          The above error looks like your module is not deployed properly in the server.

          Check in the below location weather you are able to see your JNDI name or not.

          NWA–>Configuration–>infrastructure–>Application modules.

          Thanks,

          Srikanth

          (0) 
              1. Rajesh PS

                Hi Srikanth,

                It looks like more of administrative works.

                I will get in touch with Basis Team and do the Step-by-Step procedure mentioned in above pdf.

                (0) 
      1. Rajesh PS

        Hi Srikanth,

        Should we need to do all the steps mentioned in

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

        Looks totally complicated and involves basis kind of work.

        In A Simple approach in Reading Excel File the modules mentioned are:

        cc.PNG

        So you mean to say the custom module should be created and implement all the steps mentioned in below link.

        Am I right correct me If I’m wrong.

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

        (0) 
  1. Indu Khurana

    Hi Srikant,

    Thanks for sharing this blog.
    But I could not find the java code attachment in the blog, Could you please provide the java code and also if you can provide the link to download the .jar files to read xlsx files.

    Thanks,
    Indu Khurana. 

    (0) 
  2. deva Gembali

    HI Friends,

    Please help me if anyone’s development working accordingly? As per Srikanth, where can I find the Java code and UDF?

    Thanks in advance.

    Regards,
    Vasudeva G

    (0) 

Leave a Reply