Convert XLSX into XML

[Former Member]

Hi All,

I am referring Michal's blog to convert xlsx excel sheet into xml file but i am facing few problems while implementing it.

I have configured the file sender adapter as per the modules mentioned in the blog and kept a excel file "Test.xlsx" on XI location (having two rows and two columns, first row is having name, age and second row is having its values XX,10) .
http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/12/06/pixi-reading-ms-excels-xlsx-and-x...

But when i am running the scenario, it is thrwing an error: Zip: error occured during processing: java.util.zip.ZipException: missing entry name

Please let me know what needs to be done?

Thanks!!

[MichalKrawczyk · Accepted solution]

Hi,

check if you can open the xlsx file from the location from which PI is trying to pull it

change it's extension to zip and try unzipping (in the same location) - did it work ?

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hi Michal,

Here comes the master. Thanks for replying!!

I have kept my xlsx file on XI location under AL11 and when i am trying to open that sheet, it is showing something like this:

Thanks!!

[MichalKrawczyk · Accepted solution]

Hi,

hmmm it seems like an correct zip file - but can you unzip it on the server ? can you also try that?

I've wasted like a day on issues with zip file (as I was not sending it correctly to the PI folder) and then everything was pretty smooth. So it's necessary to check if you can unzip it on the PI server first

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hi Michal,

How can i unzip that zipped xlsx file? I dont have server level access. I am uploading the files on PI server using sxda*.

Thanks!!

[MichalKrawczyk · Accepted solution]

hi,

>>>the files on PI server using sxda*.

ok - so now I know the issue sxda does not work for binary files

you need to upload it in some other way as sxsd will change the xlsx file (compare the size in AL11 and in your folder)

upload it in a correct way and it will work,

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hi Michal,

I have uploaded the file on the server directly and now i am able to unzip the xlsx file but in PI moni i am getting 11 payloads. And more impotantly my data in the "sheet1" of xlsx file is not coming, in fact more than half of the file data is coming under the "sharedstring.xml"?

My excel file is having data as:

Name Age

XX       10

Thanks!!

[MichalKrawczyk · Accepted solution]

Hi,

>>> And more impotantly my data in the "sheet1" of xlsx file is not coming

what do you mean ? - is it not there in the file ? as I see the file is there...

maybe you didn't save the file ?

can you unzip on your local computer and check if the data is there using a search function on the xml file ? as it has to be there... or maybe it's in sheet2 or 3 ? depends on this sheet you've saved it

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hi Michal,

I have unzipped the file on local desktop (remane the xlsx file to .zip and then unzip the same) and able to see the sheet1.xml under "worksheets" but i am not getting the my relevant data under it . As you can see below my xlsx file is having data under "sheet1", but when i am checking it under "worksheets" i am getting something else under "sheet1.xml" (the same xml i can see under PI Moni).

I guess i am doing something wrong butnot sure where?

Thanks!!

[Former Member · Accepted solution]

Hi,

One more observation:

The "sharedStrings.xml" under "xl" is having some part of my data:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>  
- <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="5" uniqueCount="3">
- <si>
  <t>Name</t> 
  </si>
- <si>
  <t>Age</t> 
  </si>
- <si>
  <t>XXX</t> 
  </si>
  </sst>

Thanks!!

[MichalKrawczyk · Accepted solution]

Hi,

but in the screenshow show the data - you don't need descriptions anyway - you just need

row and table number and data inside right ?

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hi,

Ok, i can think of leaving "Name" and "Age" (descriptions) value but why "XXX" (name under Row 2, Cloumn 1) is not coming under sheet1.xml??

Thanks!!

[Former Member · Accepted solution]

Hi,

Sheet1.xml data:  XXX ( Row2, cloumn 1) is not coming and i want that data to be in the xml?

<sheetData>
<row r="1" spans="1:2" x14ac:dyDescent="0.25">
<c r="A1" t="s">
<v>0</v>
</c><c r="B1" t="s">
<v>1</v>
</c>
</row>
<row r="2" spans="1:2" x14ac:dyDescent="0.25">
<c r="A2" t="s">
<v>2</v>
</c><c r="B2" s="1">
<v>10</v>
</c>
</row>
</sheetData>

[MichalKrawczyk · Accepted solution]

Hi,

I think the rule might be:

if cell has : t="s" then take the number (0,1,2 in your case) from the sheet1 and take (first second and third value) from the sharedstring.xml

if s="1" then we have a real integer value

I remember I was taking lots of sheets and combining them in an adapter module in one XML

so I only needed to have xml format of the excel - there was some additional logic to combine different sheets but all of the values were inside sheets

so it's not as easy but you should be able to combine it anyway in java,

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Hi Michal,

So, if i understood you correctly, if the "XLSX" sheet is having combination of string values along with integer (in most of the scenarios this wil be the case) then i have to build addtional logic in adapter module to fetch the values from "SharedString.xml" depending upon the numbers in "Sheet1.xml" (0,1,2,3...) and then populate it?

And only if the XLSX is having numeric values then only i can get whole excel sheet values under "Sheet1.xml" (and no adapter development is required)?

Thanks!! 

[MichalKrawczyk · Accepted solution]

Hi,

>>>>And only if the XLSX is having numeric values then only i can get whole excel sheet values under "Sheet1.xml" (and no adapter development is required)?

yes - but please check it - also with dates and minus numbers if possible

>>>>is having combination of string values along with integer (in most of the scenarios this wil be the case) then i have to build addtional logic in adapter module to fetch the values from "SharedString.xml" depending upon the numbers in "Sheet1.xml" (0,1,2,3...) and then populate it?

as you see from the example this seems to be true

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Thanks Michal for your wonderful help!!