Issue with reading excel file -xlsx format using standard PI modules

[former_member285898]

Hello All,

I am having an issue with reading excel file -xlsx format using Standard PI Modules. Please find below details and query:

Requirement:

Need to create a scenario for reading excel file -xlsx format using Standard PI Modules.

Excel sheet data: (Below dummy data for example)

Configuration Part:

I have used Michal's blog for reference to achieve my requirement.

URL:  http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/12/06/pixi-reading-ms-excels-xlsx-and-x...

1) I am using FTP as a sender channels with following standard modules.

-> Here I used two standard modules 1) PayloadZipBean = which unzip excel file & 2) PayloadSwapBean = Which swaps two payloads.

-> As per below I get 9 xml files after unzipping excel file, also here sharedStrings.xml have actual data.

->Shared String xml:

Problems :

1) Numeric value which is mentioned in Excel file is not coming to xml file. ( for example: in 1st screenshot showed numbers: 100,101,102 are not coming to xml file.)

2) Values inside xml is not coming in particular fix sequence due to which Java mapping is also difficult.

3) How to map this two structures without java mapping.

Query:

1) Is there any standard module which can retrieve this numeric values , which are not coming xml file?

2) How to map this two structures surpassing above issues?

Kindly Help... !!!

Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

Unfortunately, Microsoft's XSLX format has the values of the spreadsheet spread over the different XML files of the zipped XSLX file. Reading just the sharedStrings.xml file is not sufficient to retrieve all the content.

As an alternative, you can try using the custom adapter module described in my article below.

Rgds

Eng Swee

[former_member285898 · Accepted solution]

Hello Eng,

Thanks for your valuable input, will try using custom adapter module.

By the way, can "MessageTransformBean" standard module  with parameter 'Transform.ContentDisposition' can solve my issue regarding numeric value reading from excel file?

Or any other Standard module can help me?

Regards,

Karan K.

[engswee · Accepted solution]

Nope. AFAIK, no standard module is able to handle this.

[former_member285898 · Accepted solution]

Hello Eng,

I have used your above mentioned blog to create custom module to read Excel file.

I have used below blog to download all jar files required.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2015/04/16/recompile-comequalizexpiafmodules...

but still I am short of some jar files which are required.

So can you provide me this jar files, so I can continue to build custom module for reading excel file.

Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

For the Apache POI files, refer to the Source Code section of the following article.

For the other SAP related libraries, can you please confirm both the version of PI and also the NWDS version are you using? These files are very dependent on the PI version used, so I can't provide you the JAR files from my system as it won't be compatible.

Rgds

Eng Swee

[former_member285898 · Accepted solution]

Hello Eng,

I have downloaded Apache POI folder from given link only, and I have imported full folder but still it showing error. In below screenshot you can find full folder.

And I am using NWDS 7.3 SP01 and I my PI version is 7.31 SP01 (single stack).

So NWDS and PI version should match? Hope you can give me jars for this version.

Or should I check with BASIS people?

Please suggest me from which path location I get this jars from PI system.

Regards,

Karan Kholakiya

[engswee · Accepted solution]

Hi Karan

Your NWDS and PI should match exactly for version and SP level. Your PI system is 7.31 but you are using NWDS 7.3 which is incorrect. Make sure you use NWDS 7.31 SP01 instead, it is also named EHP1 for NWDS 7.3.

For NWDS 7.31, it comes built in with the required JAR files for the Adapter module libraries.

Just follow the screenshots in the reply on the thread below on how you can include those libraries. It should resolve the remaining errors.

Issue in Excel to XML Conversion

Rgds

Eng Swee

[former_member285898 · Accepted solution]

Hi Eng,

I have checked again version of NWDS and found it is EHP1 with version 7.3 SP01, so I believe no need to upgrade it again as it matches with PI version.

Also above blog helped me to include inbuilt Adapter & Mapping module libraries, after that remaining error got removed. Really thanks for it...

But still I am stuck with Apache POI library files. I have include below jar files which are mentioned in your blog & which you referred previously , but still It's showing error. I really don't understand what thing I am missing.

Apache POI library files which are included:

Please help me in this.

Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

I think you are just adding the JAR files directly to your project which will not work. You need to add them to the build path as external JAR files.

Right click your project name > Build Path > Configure Build Path

Switch to Libraries tab

Add External JARs

Rgds

Eng Swee

[former_member285898 · Accepted solution]

Hi Eng,

Thanks, it worked...

I am done with creating class file and code that showing no errors, also as I am new to create custom module creation so I am stuck with further custom module creation process, I was searching steps after creating class file for custom module and found some blogs where it mentioned that, we need to maintain JNDI name , handling "application-j2ee-engine.xml" file in EAR package and "ejb-j2ee-engine.xml" file in EJB package.

So as per your blog do we need to maintain JNDI name, and what things need to be included in above mentioned two xml files?

Kindly suggest.

Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

Please follow step 4.5, 4.6 and 4.10 in my blog. The files are already in the zip file.

Rgds

Eng Swee

Message was edited by: Eng Swee Yeoh

[former_member285898 · Accepted solution]

Hi Eng,

Given steps from the blog helped me a lot... Thanks for it.

Now I successfully deployed custom module on PI server.

Also I have created file to file scenario for it with sender channel having custom module parameters which you stated in your blog for reading xlsx file. but now my sender channel giving below error.

Module parameters:

Error in Communication Channel:

I didn't got what went wrong now.

Please help me for this now.

Regards,

Karan K.

[engswee · Accepted solution]

Your project is not built correctly with the Apache POI library files.

Can you provide screenshot of the following?

i) Your EJB project in the Project Explorer window. I need to see which Apache POI JARs that have been included

ii) Open the EAR file in WinZip/7Zip and provide view of the contents.

[former_member285898 · Accepted solution]

Hello Eng,

Please find below screenshot as you required:

1) EJB project in Project explorer window-

2) EAR file in 7zip-

And one more thing I noticed that under EJB project "build folder" is empty, I think its not got build properly, I checked option under Project and  "Build Automatically" option is checked.

Hope this much information enough for you, if you want more info please let me know.

Thanks & Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

You need to remove com.equalize.xpi.af.modules.ejb.jar from the build path. It should not be added as a JAR file.

Instead, it has to be built through the source codes in the project. I see that you have only imported Excel2XMLTransformer.java into your project. That is not enough.

You need to minimally import the source codes in the below packages:-

• com.equalize.xpi.af.modules
  • FormatConversionBean.java
• com.equalize.xpi.af.modules.util
  • All files
• com.equalize.xpi.util
  • All files

Please refer to step 4.4 of my blog.

It seems you are not following the steps in the blog properly and it is very difficult to identify what you have actually done or missed out. If it still does not work, I'd suggest you redo a new EAR/EJB project following the blog exactly unless you know what are the consequences if you differ from any particular step.

Rgds

Eng Swee

[former_member285898 · Accepted solution]

HI Eng,

I know its very difficult for you to give solution on the basis of screenshots only, I really appreciate your efforts for helping me. Thanks a lot for your time and help.

This time I followed each steps properly as per your blog and I succeeded almost but getting error for following two library files in 'MessageDispatcher.java' file. Please find below screenshot for it.

Other than this there is no issue. I believe I have imported all jar files required for it.

Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

As I've mentioned before, please remove the com.equalize.xpi.af.modules.ejb.jar from the build path - this is really important to prevent the runtime issue that you had from your previous deployment.

To resolve the remaining errors, the easiest way is to delete MessageDispatcher.java and AttachmentSplitterBean.java from your project, since you are only interested in the Excel transformation.

Rgds

Eng Swee

[former_member285898 · Accepted solution]

Hi Eng,

I have removed both java files which you have mentioned , also I have removed com.equalize.xpi.af.modules.ejb.jar file from build path, but after removing jar file I am getting an error in AbstractModule.java file , please find below screenshot for your reference.

Can you please let me know what to do, as there are many dependencies in these files.

Thanks and regards,

Karan K.

[engswee · Accepted solution]

Karan

As I mentioned earlier, you need to follow the steps exactly. I've already covered this in step 4.8.

Rgds

Eng Swee

[former_member285898 · Accepted solution]

Hello Eng,

There are no errors now...

Please look below screenshots which shows how my EAR file now looks like:

Please let me know if something wrong in this file now...

Also I have one queries regarding deploying module again..

1) Should I deploy it in normal way or there is some redeployment option available in NWDS which will overwrite modules at PI system again?

2) Or I need to remove old deployed modules from PI system and doing normal deployment again?

could you please help me in this doubts too.

Regards,

Karan K.

[engswee · Accepted solution]

Hi Karan

I think it looks okay. Just redeploy the normal way, it should overwrite the older modules.

Rgds

Eng Swee

[former_member285898]

Mentioned link by Eng has broken, please find below link for same.

https://blogs.sap.com/2014/10/21/exceltransformbean-part-1-convert-various-excel-formats-to-simple-x...

[Former Member]

Hello Karan,

1. In order to have Number column in XML part, you need to convert it into string in excel. To do this, Kindly select the corresponding column, copy them and paste them in another excel sheet. Then in the original one, please select that same column, and right click and choose Format cells. There select "Text". Then paste the same numbers back to the original excel sheet from the other excel sheet. They will get converted to string.

2. In order to have the xml part in the desired format, kindly maintain "Type" of "si" as xsd:string make the occurrence of field t to 1..unbounded.

3. Mapping can hence be done easily by putting if condition in graphical mapping without java mapping.

Kind Regards,

Souvik

[former_member285898]

Hello Souvik,

Thanks for your feedback too...

1) As per your first point it will be difficult to maintain format for every excel files which are placed at customer FTP location, which is maintained by them.

2) I have imported XSD file in External definition as source data, also here "si" segment repeating many times with single value in "t". So It is not usefull to change occurrence for "si" segment.

Regards,

Karan K.