On Thu, May 17, 2012 at 1:23 PM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:
Thanks John...what would you suggest to make the transition to Excel
easier? HSSF would probably be more than could be handled.
The tools suggested by Luis are probably pretty good. I have never
used them, but I would expect them to be robust. They wrap the HSSF
stuff, to provide an easier-to-use interface for typical RPG
programmers.
If you (or whoever is responsible for such things) are willing to
install iSeries Python and xlwt, I have a program which converts
physical files to Excel .xls files. No Python knowledge is required,
though I would strongly encourage the learning and use of Python
anyway.
If these options are too scary, but your RPG programmer is comfortable
manipulating the potentially problematic columns, there is still one
trick that might serve your need, which is to *use* Excel's
hyperaggressive interpretation of CSV instead of fighting it. (A bit
of jujutsu, so to speak.)
Besides trying to interpret things as numbers, Excel also tries to
evaluate formulas. So you can use an *Excel string literal* in an
Excel formula to give you 5E72 as text:
="5E72"
Note the leading equal sign, which activates formulas in Excel. The
quotes here are not "string delimiter" quotes, as you would instruct
CPYTOIMPF to use, but actually the syntax for string literals in Excel
formulas. If the above is sitting in some field, which still needs to
be processed by CPYTOIMPF, and CPYTOIMPF is set to delimit strings
with double-quotes, then the resulting CSV data, when viewed with
Notepad, should be
"=""5E72"""
Also note that ="5E72" will be seen in the formula bar in Excel when
using this method, which may or may not be undesirable.
John
midrange.com
