Skip to Content
Author's profile photo srikanth kakani

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.


Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Usefull feature

      Author's profile photo srikanth kakani
      srikanth kakani
      Blog Post Author

      Thanks Sourabh.. 🙂

      Author's profile photo Former Member
      Former Member

      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.
      Author's profile photo srikanth kakani
      srikanth kakani
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hi Srikanth,

      I'm not getting you could you please brief me. I have ignored below stps mentioned in below link

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

      Also what you mean my server level deployment and JNDI.

      One more query I could see Module Name as 'ExcelToXML' I guess its customized module should we maintain that at basis level.

      Author's profile photo srikanth kakani
      srikanth kakani
      Blog Post Author

      Hi Rajesh,

      Please follow the step by step of the document which I mentioned. then only you can get to know the module deployment.

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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh

      Hi Srikanth

      Thanks for sharing. Just a suggestion - how about adding the following blog from Shabarish as a reference?

      Excel Files - How to handle them in SAP XI/PI (The Alternatives)

      This way, anyone who finds this can also explore the alternative approach using JExcel API.

      Rgds

      Eng Swee

      Author's profile photo srikanth kakani
      srikanth kakani
      Blog Post Author

      Hi Eng Swee,

      Thanks for your suggestion. i will add the shabs blog as reference. it will be useful 🙂

      Thanks,

      Srikanth.

      Author's profile photo Former Member
      Former Member

      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…

      Author's profile photo Former Member
      Former Member

      Nice one Srikanth ...

      Author's profile photo Bhargava krishna Talasila
      Bhargava krishna Talasila

      Thanks for sharing.. 🙂

      Author's profile photo Former Member
      Former Member

      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. 

      Author's profile photo deva Gembali
      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

      Author's profile photo Harshitha yadav
      Harshitha yadav

      Hi freinds,

      I dont find the java code attached.Could anyone please help me.

       

      Thank you.

      Regards,

      HarshithaYadav