× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jon Paris

Thanks John and Jon!

Jon is right, I am the one that asked for help with the XML-INTO, as I thought it would be a quick way to get to the data I needed. As I explained, the politics where I am right now are such that there were "political" barriers right now to setting up for POI. (Seems like the other guy thought he would have to support a Java program; what happens if I get hit by a bus or leave; but even just naming and calling Java classes and threads might be too much)

The XML-INTO is fascinating and what I've learned (and anybody listening) is 100% not wasted, nor the POI bits. I'll use it in the future if I come across an appropriate assignment.

Jon, Python is also definitely on my list, along with PHP and Java, and more. (I still might be able to push PHP through soon here but on the "other guy's" schedule.)

But IT at this subsidiary will soon be absorbed into the parent company IT. I have a deadline for this project, and an executive audience, so....

I am not going to re-write a generic XML scanner but in this time I could have gotten halfway through getting at the data with just the IFS API's, since it's just some of this.

I think my approach at this stage is going to be to read through and get the <Row>...</Row> strings and feed that into XML-INTO. I'll look at Jon's SAX examples but after this business with XML-INTO, I'm thinking of postponing all the learning exercises. (To be continued...) I'm still ahead of my finish-by time for this step..

To Scott's comments, it looks like there is no SST issue here, the XML looks complete with what I'm looking for.

Depending on what's done before deadline, I'll see what to do to automate it later. It was the minimal user intervention solution under the circumstances. The XLSX-XML converter looks good. I'll look at Expat later on too.

Thanks lots,
Alan



Unless I'm misremembering John, Alan reached this point of having the users save off in XML format (his idea not mine) _after_ having tried the POI classes to access the data directly. I would have recommended that approach (or PHP or ...) had he not already gone down that route.


On 2014-05-20, at 6:26 PM, John Yeung <gallium.arsenide@xxxxxxxxx<mailto:gallium.arsenide@xxxxxxxxx>> wrote:

Alan,

As I read this saga of you and Jon going deeper and deeper down the
XML rabbit hole, I have to say: Are you sure you really want to go
through this learning exercise right now?

Please let me be clear: I think it is great that you want to learn
RPG's XML features. I have absolutely nothing against learning them
(or learning pretty much anything, for that matter). So please
understand that I am not trying to discourage you, I'm not trying to
undermine your decision, and I am not trying to be difficult.

But it all just sounds like it would be way, way easier and quicker
(for you and your users and your bosses) if you instead invested your
time learning a bona fide Excel-handling package. Especially once Jon
said "could you ask your users to save the specific worksheets you
need in XML format?".

Putting more onus on the user is only going to introduce another
source of user error, making both their experience and your experience
worse.

Now, admittedly I can only speak to using Python, because that's my
expertise. It's free, and easy enough to install that I can install
it in just a few minutes (and I basically have no admin skills at all)
but as far as I can tell, it's not officially supported or endorsed by
IBM, so if that means it's out of the question, then fine, but there
are other choices, like PHP.

For now though, I think it may be helpful to at least show you a few
Python snippets so you can see the flavor of how working with Excel
files typically goes in a modern, dynamic language. It should be
quite similar with PHP or a number of other languages.

First, you have to load an Excel-reading module (there are two major
ones for Python; I use xlrd):

import xlrd

To open a workbook (can be either .xls or .xlsx; the user doesn't have
to do any extra "Save As..." step) is simply

wb = xlrd.open_workbook('/my_excel_dir/my_workbook.xlsx')

At that point, wb is a "workbook object". You can access any of the
sheets of the workbook as follows:

ws = wb.sheet_by_index(0)

or

ws = wb.sheet_by_name('Sheet1')

Assuming a "normal" workbook created by Excel, where the sheets have
not been renamed, either of the above lines of Python will get you the
first sheet (indices start at zero). But of course you can pick any
index or any name. At that point, ws is a "worksheet object".

To get the value of a particular cell (including a cell which contains
a formula) in that sheet:

x = ws.cell_value(3, 5)

The above sets x to the value of the cell at the 4th row, 6th column
(F4 in default Excel notation). Or, to get a bunch of values at once:

list1 = ws.row_values(3)
list2 = ws.col_values(5)

The above creates two Python lists (somewhat like arrays, but can
change size freely and contain objects of any type, including mixed
types). list1 will contain all values in the 4th row; list2 will
contain all values in the 6th column.

Suppose the values in column A are state abbreviations and column B
are sales dollars. You are interested in getting the sales for NJ,
NY, and CT. One way to do that is

total_sales = 0
for rx in range(ws.nrows):
if ws.cell_value(rx, 0) in ('NJ', 'NY', 'CT'):
total_sales += ws.cell_value(rx, 1)

The Python that I use on the i comes with easy ways to read and write
physical files (both RLA and SQL flavors), so the interoperability
with native i stuff is really first-rate.

By now, I'm sure I've babbled on way too long, especially as this is
the RPG list. But I felt it was worth trying to show how easy it can
be to work with Excel. Alan, if you are intrigued enough by this
taste to learn more, I'm happy to answer any questions. Whether you
try Python or something else or continue forging ahead with RPG, I
sincerely wish you success.

John Y.
--
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.
--------------------------------------------------------------------------------



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.