Creating An Excel File From An AS400 Batch Job

Tony Payne

unread,

Oct 10, 2002, 2:22:51 PM10/10/02

to

We are looking for a way to take a file on the AS400 and to create an
Excel spreadsheet automatically.

I know this can be done using Client Access, however the job that is
creating the file to be downloaded runs from the jobq in batch, not
online, therefore using the Client Access file transfer programs is
not an option.

Additionally, for security reasons, users are not being given access
to the file transfer programs, and we would like to see the creation
of an Excel file without the need for user intervention.

Currently, we are creating a .csv file and ftp'ing this to the
network, however not only does the user then have to open that in
Excel, they have to run a macro to format the columns since the Excel
import wizard looks at the data in the first row to determine the
format for each column.

In summary, our requirements are:

Create Excel file in batch
Format columns correctly according to the AS400 file specs.

Is there a product out there that we can use?

Thanks in advance.
Tony Payne - Senior Systems Analyst
Selmer-UMI Inc., Elkhart, Indiana 46516
Email: tpa...@selmer-umi.com

Shane Mingins

unread,

Oct 10, 2002, 4:09:02 PM10/10/02

to

Not sure about any product but this is what I did recently ....

I have a CL program that runs some existing queries that creates a file of
the data.
I then create a PF in QTEMP.
I insert the column headings
I copy the data from the file created by the queries.
FTP it to the LAN.

And all this in batch :-)

Hope this helps.
Shane

/*-------------------------------------------------------------------*/
/* Start of program */
/*-------------------------------------------------------------------*/
/* */
PGM

DCL VAR(&LIBNAM) TYPE(*CHAR) LEN(10)
DCL VAR(&SRCLIB) TYPE(*CHAR) LEN(10)

/*-------------------------------------------------------------------*/
/* Run the queries (note: QRYFILE use so that program will run over */
/* test environment) */
/*-------------------------------------------------------------------*/

RUNQRY QRY(SIS_QUERY/NRQ268_N1) QRYFILE((NRP076))
RUNQRY QRY(SIS_QUERY/NRQ268_N2)
RUNQRY QRY(SIS_QUERY/NRQ268_N3) QRYFILE((NRP032) +
(NRP097))
RUNQRY QRY(SIS_QUERY/NRQ268_N4) QRYFILE((NRP041) +
(NRP097))
RUNQRY QRY(SIS_QUERY/NRQ268_N5) QRYFILE((NRP041))
RUNQRY QRY(SIS_QUERY/NRQ268_N6) QRYFILE((NRP032))
RUNQRY QRY(SIS_QUERY/NRQ268_N7)
RUNQRY QRY(SIS_QUERY/NRQ268_N8) QRYFILE((*SAME) +
(NRP022) (*SAME) (NRP010) (NRP001) (NRP027))

/*-------------------------------------------------------------------*/
/* Create a file to hold the CSV data and copy DB file into it */
/*-------------------------------------------------------------------*/

RTVOBJD OBJ(NRP097) OBJTYPE(*FILE) RTNLIB(&LIBNAM)

CHGVAR VAR(&SRCLIB) VALUE(%SST(&LIBNAM 1 4) *CAT +
'SRC')

CRTPF FILE(QTEMP/NRZ268) RCDLEN(255)

/* run stored sql statment to insert file headings */
RUNSQLSTM SRCFILE(&SRCLIB/QTXTSRC) SRCMBR(SQLNRZ268) +
COMMIT(*NONE)
CPYTOIMPF FROMFILE(QTEMP/NRZ268_N8) +
TOFILE(QTEMP/NRZ268)

/*-------------------------------------------------------------------*/
/* Copy the CSV file to the LAN */
/*-------------------------------------------------------------------*/

OVRDBF FILE(INPUT) TOFILE(&SRCLIB/QREXSRC) +
MBR(FTPNRZ268)
OVRDBF FILE(OUTPUT) TOFILE(QTEMP/FTPDATA) +
MBR(FTPOUTPUT)
FTP RMTSYS(FILE)

/*-------------------------------------------------------------------*/
/* Delete the CSV file in QTEMP and advise user file is ready */
/*-------------------------------------------------------------------*/

DLTF FILE(QTEMP/NRZ268)
SNDMSG MSG('File di_aut_nzp.csv is now available on +
the LAN. The location should be +
J:\shared\clients\NZFP') TOUSR(*REQUESTER)

ENDPGM
/*-------------------------------------------------------------------*/

I insert the column heading into the file first with the CL code:

/* run stored sql statment to insert file headings */
RUNSQLSTM SRCFILE(&SRCLIB/QTXTSRC) SRCMBR(SQLNRZ268) +
COMMIT(*NONE)

The contents of SQLNRZ268 is

INSERT INTO QTEMP/NRZ268
VALUES('"Date","Advisor Code","Advisor Name","Portfolio","Name","AUT
Amount"')

--
shanem...@yahoo.com.clothes

remove clothes before replying

"Tony Payne" <tpa...@selmer-umi.com> wrote in message
news:ccc8c5cc.02101...@posting.google.com...

Bill Z Bubb

unread,

Oct 10, 2002, 5:46:06 PM10/10/02

to

Tony,

One way of tackling this would be to take a different tack. Create an MS
Query inside the Excel spreadsheet that queries the appropriate file(s) on
the 400. MSQ is an optional install in Office. Once installed, it can be
accessed via Data/External Data/Create New Query. You will have to create
an ODBC data name source to access the information on the 400.

Bill

Andi Goering

unread,

Oct 11, 2002, 1:27:50 AM10/11/02

to

Tony,

why not give **iExcelGen** a try?

With it's help you create real Excel .XLS sheets (not only .CSV)
native on the iSeries!

Any input is welcome: PF, LF, QRYDFN, Spool.

With the built-in eMail Client you can send the created excel sheet by
eMail
to anybody with an eMail account worldwide!
No AS400-configuration is needed for that!

See http://www.goering.us for more informations.

Kind Regards

Andreas Goering

"Bill Z Bubb" <billzb...@netscape.net> wrote in message news:<ao4set$jhu8i$1...@ID-91878.news.dfncis.de>...

Doug Marquess

unread,

Oct 11, 2002, 7:07:02 AM10/11/02

to

If you initiate the batch job from the PC where the file is to be
downloaded the Client Acess transfer request can be initiated from the
CL using the STRPCCMD.

In addition you can use the CPYTORMTF command from News/400 to convert
the file to a CSV file with column headings in the first record prior
to downloading.

tpa...@selmer-umi.com (Tony Payne) wrote in message news:<ccc8c5cc.02101...@posting.google.com>...

Phil

unread,

Oct 11, 2002, 2:06:39 PM10/11/02

to

Yes. This is the solution I generally would opt for.
Phil

"Bill Z Bubb" <billzb...@netscape.net> a écrit dans le message de news:
ao4set$jhu8i$1...@ID-91878.news.dfncis.de...

James Durr

unread,

Oct 11, 2002, 3:23:32 PM10/11/02

to

Hello,

Take a look at our CVTDBFXLS(Convert Database File to Excel) command at
http://www.kemetech.com

The CVTDBFXLS command has the following features:

* Column widths generated directly from the data description
specifications
* Create multiple sheet spreadsheets based on field value or members in
a file
* Custom headings or use the field name, or column text
* Row and column formulas(@AVG, @COUNT, @MAX, @MIN & @SUM)
* Translates AS/400-iSeries date and time formatted data to Excel date
and time formats
* Specify the Fixed Columns/Fixed Rows option to freeze rows and columns
in place for better visibility while scrolling

We also convert Excel files to AS/400-iSeries database files and
AS/400-iSeries reports to Excel files.

--
James Durr
Technical Consultant
KemeTECH Systems Inc.
103 Vassar Avenue, Newark, NJ 07112-2249
Phone( 973) 923-2328 e-fax (978)231-5825
jd...@kemetech.com

Richard Schoen

unread,

Oct 12, 2002, 2:17:12 AM10/12/02

to

Hello Tony,

We will be releasing a new product within the next month or so called the
iSeries RPGSQL integrator. You will be able to create RPG programs that can
write directly to MS Excel, Access, SQL Server, Oracle or any other ADO data
source. Since you can write directly to network based data sources, users
don't need access to any file transfer software.

For more info, contact me directly or keep an eye on our web site.

Regards,
Richard Schoen
RJS Software Systems Inc.
"The AS/400 Report and Data Delivery Experts"
Email: ric...@rjssoft.com
Web Site: http://www.rjssoftware.com

"Tony Payne" <tpa...@selmer-umi.com> wrote in message
news:ccc8c5cc.02101...@posting.google.com...

Vincent

unread,

Oct 13, 2002, 9:24:37 PM10/13/02

to

Try creating a SLK (Aka SYLK - Symbolic Link) file instead of a CSV
file.
With a SLK file you can control just about anything you like (eg.
column & row widths, fonts, number format etc). But you cannot place
formulas within cells.

SLK files load into Excel in the same manner as CSV files (just click
on em), but the result is a whole heap prettier.

The creation of a SLK file is a little more involved than creating a
CSV file but still quite simple. It's just a text file (like CSV
files) with stuff in it defining the content & layout of the
spreadsheet. To create your own example of a SLK file, create a
spreadsheet manually (using MS Excel) and save it as *.slk format.

I long ago changed my AS/400 report programs to generate SLK files
instead of CSV files & they work well.

Vincent

unread,

Oct 16, 2002, 9:48:13 PM10/16/02

to

In my last post I said that you cannot place formulas within cells via
a SLK file. Well I just realised that is not true. You can put
(almost) anything you like in any cell, including formulas. Sorry for
the mistake.