Re: Parsing an XML-XLSX file using XML-INTO

Hi Jon,

I've tried various things without completing success here. I think I'm going to take the simpler route and parse the text, since there are enough consistent markers to do this in the XML. I'll wait for something simpler and smaller to intro the parser.

It's just a huge file, this one, one file went to nearly 112 megabytes, and Notepad++ counted almost 50,000 rows in one file. Many of the Cells have no "<Data>" content, so I presume allowmissing=yes would be required to be able to get past them without the error.

I even decided to go simple and just port your code straight in as is, and parse straight to the Row(..) data structure array instead of using the handler procedure.

But then I got the same error as I started this with, that is, a full count of returned Rows() but all blank data.

The code is at http://code.midrange.com/51fe4e4ae9.html.

Below the signature line are the two messages I got.

Alan



First message I got said it countpfx was not valid for XML-INTO (hunh???)

RNX0352: Option 'countpfx=Count' is not valid for XML-INTO; reason code 2;
Cause . . . . . : An RPG procedure attempted to use an option string
containing option 'countpfx=Count' which is not valid in the context of the
XML-INTO operation. The reason code is 2. The full option string is
"countpfx=Count path=Workbook/Worksheet/Table/Row doc=file datasubf=Data
ns=remove case=any trim=all allowextra=yes".
2. The option is not valid in the context of XML-INTO.

So I took out the countpfx= option and the count fields (except for rowcount in the PSDS).
Next was that there were missing elements in the XML.

RNX0353: The XML document does not match the RPG variable; reason
code 4.
Cause . . . . . : While parsing an XML document, the parser found that the
XML document does not correspond to RPG variable "row" and the options do
not allow for this. The reason code is 4. The exact subfield for which the
error was detected is "row(1).cell(2).data". The options are
"path=Workbook/Worksheet/Table/Row doc=file datasubf=Data ns=remove
case=any trim=all allowextra=yes". The XML document name is
/TESTRDP/DAILY/Daily Deposits-April 2014.xml; *N indicates that the XML
document was not an external file.
4. The XML document is missing XML attributes or elements to match
subfields.






-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jon Paris
Sent: Monday, May 19, 2014 11:57 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Parsing an XML-XLSX file using XML-INTO (was POI-HSSF classes)

If you have a problem with the path just send me details of the file format you are trying to process - or even better post an example of the XML to the midrange code area.

On 2014-05-19, at 6:45 AM, aec <cfuture@xxxxxxxxxxx<mailto:cfuture@xxxxxxxxxxx>> wrote:

Thanks millions, Jon!

Thing is, we're on v7r1 but without the TR's that allow for complete free-form (sigh) (for "political" reasons) which I'll be trying to take care of (after this month end the "politics" will change, but we'll see). I "studied" the RPG reference and guide both on XML-INTO to get as far as I did but I'll apply your points.

As to the Excel, I'm not at work yet till later this morning, I'll check the Excel version...

You say the path= is "way too deep". I first tried just doing it from "row" but I'll do that again.

Thanks again,
Alan



On 5/16/14 8:06 PM, Jon Paris wrote:

Turns out the Excel 2004 XML matched what you had better than I thought. This program works and processes all of the XML document I gave it.

A few points:

1) No allowmissing or allowextra.

allowextra is probably needed - particularly if you don't care about the Type fields. allowmissing was not needed because I supplied a count variable (countprefix) wherever it was needed (rows and cells) which as you will see also simplifies the processing.

By using both you basically guaranteed that any valid XML document would process without error no matter what it contained.

2) ns=remove option needed as namespaces are present and need to be stripped out.

3) datasubf was needed because data and attributes occur together in the <Data> element.

4) your path= went way too deep. You needed to target the first of the repeating elements - which (at least in 2004 Excel XML) appears to me to be row - which is what I would have expected. Because your path was wrong no data was found.

Think that's about it. Except to say that if you are on V5R4 you have a bit of a problem as you are missing a number of needed features. You could live without but it won't work as well. If you need V5R4 let me know.

Here's the code (Free-form 'cos fixed feels like hard work now):

dcl-ds *n PSDS;
RowCount int(20) pos(372);
End-Ds;

dcl-s r int(3);
dcl-s c int(3);

dcl-ds Row Dim(100) Qualified Inz;
Cell LikeDs(Cell_T) Dim(10);
CountCell int(5);
End-Ds;

dcl-ds Cell_T Template Qualified;
Data LikeDs(Data_T);
End-Ds;

dcl-ds Data_T Template;
Type char(20);
CountType int(3);
Data char(40);
End-Ds;

// Much as I hate CtData I couldn't be bothered to copy the XML to the IFS
// so the spreadsheet XML was all dumped at the end of the source
dcl-ds xmlData;
xml Char(80) Dim(200) CTData;
end-ds;

xml-into Row %XML( xmlData: 'countPrefix=Count datasubf=Data +
ns=remove case=any
path=Workbook/Worksheet/Table/Row' );

dsply ( 'Processed ' + %Char(RowCount) + ' Rows' );

For r = 1 to RowCount;
For c = 1 to Row(r).CountCell;
dsply ( 'Row ' + %Char(r) + ' Cell ' + %Char(c) + ' Data =' );
dsply ( Row(r).Cell(c).Data.Data );
EndFor;
EndFor;

*InLR = *On;
**
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="System i Developer">
<Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="18"
x:FullColumns="1"
x:FullRows="1" ss:StyleID="s16" ss:DefaultColumnWidth="61"
ss:DefaultRowHeight="15">
<Row>
<Cell ss:StyleID="s17"><Data ss:Type="String">
System i Developer Payment History</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s17"><Data ss:Type="String">
Assets Available for MVP</Data></Cell>
</Row>
<Row ss:Index="4">
<Cell ss:StyleID="s17"><Data ss:Type="String">
eLearning terms: 30% revenue + $50/student over 20</Data></Cell>
</Row>
<Row ss:StyleID="s17">
<Cell><Data ss:Type="String">Course Title</Data></Cell>
<Cell ss:StyleID="s20"><Data ss:Type="String">
Instructor</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2010 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2011 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2012 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
2013 Payment</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">
Total Payment</Data></Cell>
<Cell ss:StyleID="s20"><Data ss:Type="String">
Course Offered Live</Data></Cell>
<Cell ss:StyleID="s20"><Data ss:Type="String">
Course Re-recorded</Data></Cell>
<Cell><Data ss:Type="String">Notes</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">An Introduction to SQL</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="Number">2740</Data></Cell>
<Cell><Data ss:Type="Number">4657</Data></Cell>
<Cell><Data ss:Type="Number">3456</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data
ss:Type="Number">10853</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">SQL Stored Procedures</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="Number">1969</Data></Cell>
<Cell><Data ss:Type="Number">1209.78</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data
ss:Type="Number">3178.7799999999997</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Modernize RPG Applications</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=2931+1531"><Data ss:Type="Number">4462</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">4462</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">
Foundations in Modular RPG Applications</Data></Cell>
<Cell><Data ss:Type="String">Susan</Data></Cell>
<Cell><Data ss:Type="Number">2170</Data></Cell>
<Cell><Data ss:Type="Number">3390</Data></Cell>
<Cell><Data ss:Type="Number">1276.57</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">4666.57</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">
Simplify Complex RPG Functions</Data></Cell>
<Cell><Data ss:Type="String">Jon</Data></Cell>
<Cell><Data ss:Type="Number">2000</Data></Cell>
<Cell><Data ss:Type="Number">2291</Data></Cell>
<Cell><Data ss:Type="Number">1711.71</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">4002.71</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">RPG Meets the Web</Data></Cell>
<Cell><Data ss:Type="String">Paul</Data></Cell>
<Cell><Data ss:Type="Number">2000</Data></Cell>
<Cell ss:Formula="=3492+1466">
<Data ss:Type="Number">4958</Data></Cell>
<Cell ss:Formula="=2839.83+1737.45">
<Data ss:Type="Number">4577.28</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">9535.2799999999988</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">XML for RPG Programmers</Data></Cell>
<Cell><Data ss:Type="String">Jon</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=4152+1673">
<Data ss:Type="Number">5825</Data></Cell>
<Cell ss:Formula="=2229.41+3070.01">
<Data ss:Type="Number">5299.42</Data></Cell>
<Cell><Data ss:Type="Number">2430</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">13554.42</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Embedding SQL in RPG Programs</Data></Cell>
<Cell><Data ss:Type="String">Susan</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell ss:Formula="=4385+1287"><Data ss:Type="Number">5672</Data></Cell>
<Cell ss:Formula="=3653.84+1935.87">
<Data ss:Type="Number">5589.71</Data></Cell>
<Cell><Data ss:Type="Number">803.4</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">12065.109999999999</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">No</Data></Cell>
<Cell><Data ss:Type="String">New Assignments/Handouts 2013</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">RSE Jump Start Workshop</Data></Cell>
<Cell><Data ss:Type="String">Susan</Data></Cell>
<Cell><Data ss:Type="Number">2939</Data></Cell>
<Cell ss:Formula="=4730+1879"><Data ss:Type="Number">6609</Data></Cell>
<Cell ss:Formula="=5193.63+3087.84">
<Data ss:Type="Number">8281.4700000000012</Data></Cell>
<Cell><Data ss:Type="Number">5636</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data ss:Type="Number">20526.47
</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">Yes</Data></Cell>
<Cell><Data ss:Type="String">
New recording 2013; Old version offered with MVP</Data></Cell>
</Row>
<Row ss:Index="17">
<Cell ss:StyleID="s17"><Data ss:Type="String">
eBooks - 20% royalty</Data></Cell>
<Cell ss:Index="3" ss:StyleID="s21">
<Data ss:Type="String">2010 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">2011 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">2012 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">2013 Royalty</Data></Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Total Royalty</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">
PHP From an RPG Perspective</Data></Cell>
<Cell><Data ss:Type="String">Jon</Data></Cell>
<Cell><Data ss:Type="String">N/A</Data></Cell>
<Cell><Data ss:Type="Number">290.42</Data></Cell>
<Cell><Data ss:Type="Number">263.47000000000003</Data></Cell>
<Cell><Data ss:Type="Number">89.07</Data></Cell>
<Cell ss:Formula="=SUM(RC[-3]:RC[-1])">
<Data ss:Type="Number">642.96</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
On 2014-05-16, at 5:34 PM, Alan Cassidy <ACassidy@xxxxxxxxxxxxxx<mailto:ACassidy@xxxxxxxxxxxxxx>> wrote:

Ladies and Gentlemen,



Thanks to all of you that provided suggestions for using the POI classes.



Finally I went the route of asking the users to do the "Save As" XML from an .xlsx file.

Now I'm working on parsing out the data. I could do a search on what I know will be there but decided to try the XML-INTO.



Got it to compile successfully, got it to run as if it was all peachy, but I'm getting solid blank spaces. I mean for example, ROW(1).CELL(1).DATA(1) = *ALL' '. All blanks. And I checked others too, just blanks.

Found a couple of things by trial and error as I expected. One is that the parser wants the whole path, everything, not just the atomic elements. I can't figure out why.



Here's the relevant code for the XML-INTO operation:

n Ladies and Gentlemen,



Thanks to all of you that provided suggestions for using the POI classes.



Finally I went the route of asking the users to do the "Save As" XML from an .xlsx file.

Now I'm working on parsing out the data. I could do a search on what I know will be there but decided to try the XML-INTO.



Got it to compile successfully, got it to run as if it was all peachy, but I'm getting solid blank spaces. I mean for example, ROW(1).CELL(1).DATA(1) = *ALL' '. All blanks. And I checked others too, just blanks.

Found a couple of things by trial and error as I expected. One is that the parser wants the whole path, everything, not just the atomic elements. I can't figure out why.



Here's the relevant code for the XML-INTO operation:

options = 'path=Workbook/Worksheet/Table/Row/Cell/Data +

doc=file +

case=any +

trim=all +

allowmissing=yes +

allowextra=yes ' ;

//

xml-into %handler( handler : shared )

%XML( %str(pfpath) : options );

(Where pfpath is the pointer to the null-terminated (trimmed) path
of the XML document in the IFS.)



And the handler procedure:

*
--------------------------------------------------------------------

P Handler B export

D Handler pi 10i 0

D share 1024a

D row likeds( rowdata ) dim( 25 )

D const

D rowcount 10u 0 value

*
--------------------------------------------------------------------

* Data structures for Handler parameters and processing

D RowData ds dim( 25 ) based(p_rowdata)

D qualified

D Cell likeds( CellDS ) dim( 30 )

* ------------------

D CellDS ds based(p_CellDS)

D data 100a dim( 5 )

*
--------------------------------------------------------------------



I'm first testing to make sure I'm getting what I should.

So in the code I'm using a short field to use with DSPLY for the first 25 bytes of a couple of elements in the code.



Like this:

msg = %subst( row(xa).cell(xb).data(xc): 1: 20 ) ;

dsply 'data_is' ' ' msg ;



Using xa=1 then 2 etc.

Blanks. Every time blanks.

What did I miss, guys?



I'll look at the responses over the weekend.



Alan


Alan Cassidy
Senior Developer
Hospital Physician Partners
954-693-0000 ext. 3433 - Direct phone
786-380-9236 - Mobile phone
acassidy@xxxxxxxxxxxxxx<mailto:acassidy@xxxxxxxxxxxxxx<mailto:acassidy@xxxxxxxxxxxxxx<mailto:acassidy@xxxxxxxxxxxxxx>>
www.hppartners.com<http://www.hppartners.com<http://www.hppartners.com<http://www.hppartners.com>>

[HPP Logo FINAL with name-tag-logo-TM]


--------------------------------------------------------------------
------------ Confidentiality Notice: This email may contain
confidential information or information covered under the Privacy Act, 5 USC 552(a), and/or the Health Insurance Portability and Accountability Act (PL 104-191) and its various implementing regulations and must be protected in accordance with those provisions. It contains information that is legally privileged, confidential or otherwise protected from use or disclosure. This e-mail message, including any attachments, is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.
--------------------------------------------------------------------
------------


--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxx> Before posting, please take
a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Jon Paris

www.partner400.com<http://www.partner400.com>
www.SystemiDeveloper.com<http://www.SystemiDeveloper.com>

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxx> Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

Jon Paris

www.partner400.com<http://www.partner400.com>
www.SystemiDeveloper.com<http://www.SystemiDeveloper.com>




--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxx>
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.



--------------------------------------------------------------------------------
Confidentiality Notice: This email may contain confidential information or information covered under the Privacy Act, 5 USC 552(a), and/or the Health Insurance Portability and Accountability Act (PL 104-191) and its various implementing regulations and must be protected in accordance with those provisions. It contains information that is legally privileged, confidential or otherwise protected from use or disclosure. This e-mail message, including any attachments, is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.
--------------------------------------------------------------------------------


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.