Re: Trying to copy an excel spreadsheet

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi David,

I'm moving this thread to MIDRANGE-L since it isn't related to RPG.

> I'm trying to copy an excel spreadsheet from the IFS to an externally
> described file whose fields allow nulls using CPYFRMIMPF as follows:
>
> CPYFRMIMPF FROMSTMF('/QDLS/BW/BW/XFER/EDIPACER.XLS')
> TOFILE(WLIBWH/MATPICK) MBROPT(*REPLACE) RCDDLM(*CRLF)
[SNIP]
> Cause . . . . . :   An end-of-file character was found in the stream file
>   before the end of the stream file was reached.

The problem is that CPYFRMIMPF doesn't understand files that are in
Excel format. It's trying to treat the file as a plain ASCII text file,
which it is not, so that won't work.

Actually it's kinda good that it happened to have an end-of-file
character, because if it didn't you'd just get a file that's full of
garbage characters.

There are two solutions to the problem:

a) You could tell Excel to export the file into a text file that
CPYFRMIMPF will understand.

b) You could write a program that does understand Excel's file format.

Most likely, you'll want option (A).  In Excel, under File -> Save As
you can tell it the format of the file.  Have Excel output the file as a
tab delimited file (or, if you prefer, CSV... though IMHO, Tab Delimited
works better!)

Then you can get CPYFRMIMPF to read the file properly.

If, instead of having Excel do the conversion you want to write a program
that understand's Excel's format, you can do that with the HSSF Java
classes.  You can write a Java program that interprets the file and
converts it to your database format, or you can write an RPG program that
talks to the Java classes (RPG has the ability to call Java methods in
V5R1 and up)

I wrote an article in my newsletter that includes a demonstration of
reading an Excel file in RPG.  If you're interested, you can read it at
the following link (requires a membership to the iSeries Network, but a
free "Associate" membership should work.)

http://www.iseriesnetwork.com/article.cfm?ID=17840