Skip to Content

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

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-xlsm-files-with-standard-pi-modules--easily

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.

1.jpg (15.4 kB)
2.jpg (85.6 kB)
3.jpg (105.3 kB)
4.jpg (44.4 kB)
5.jpg (94.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Aug 13, 2015 at 01:26 AM

    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.

    ExcelTransformBean Part 1: Convert various Excel formats to simple XML easily

    Rgds

    Eng Swee

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 13, 2015 at 07:37 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Mar 30 at 05:50 AM
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.