|
We use the CPYTOSTMF and the CPYTOIMPF commands to create a Tab delimited file which Excel can read. The CPYTOSTMF command is used to create the IFS file with the right code type. CPYTOIMPF can create an IFS file, but the file will be in EBCDIC. If the IFS file already exists and has the right code page, CPYTOIMPF will convert the data to ASCII, and this is the cool part, convert packed decimal fields into ASCII numbers.
The CPYTOSTMF command will copy a program described file, a source file, or an externally described file with three defined fields. I find it's easiest to use a source file. Any source file will do since the IFS file you create is going to be overridden by the CPYTOIMPF command.
Here's an example of the commands in use. *PCASCII is the parameter that creates the right code page.
CPYTOSTMF FROMMBR('/QSYS.LIB/MURPHY.LIB/QTXTSRC.FILE/DEFAULT.MBR') TOSTMF('/home/murphy/default.xls') STMFOPT(*REPLACE) STMFCODPAG(*PCASCII) ENDLINFMT(*CRLF)
Here CPYTOIMPF creates a file with records delimited by CRLF, string fields delimited with quotes, and all fields delimited with Tabs (X'05'). Although there is a *TAB option for the FLDDLM parameter, it doesn't work.
CPYTOIMPF FROMFILE(MURPHY/ALISTS) TOSTMF('/home/murphy/default.xls') MBROPT(*REPLACE) RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM('"') FLDDLM(X'05')
We're at V4R3. I'm not sure if the CPYTOIMPF command is available in earlier versions.
Guy Murphy - FACTS system
University of Illinois
217-333-8670
-----Original Message-----
From: Leland, David [mailto:dleland@Harter.com]
Sent: Wednesday, May 26, 1999 3:00 PM
To: 'MIDRANGE-L@midrange.com'
Subject: Copy AS/400 file to an Excel file
I'm looking for a program to create an Excel file from an AS/400
database file, and to have the Excel file reside on the IFS - no PC
involved, initiated from the AS/400, and without human intervention. I
believe that Sequel can do this but I've never been a big Sequel fan and
I don't want to pay that much (I'm looking for something for $500 or
less.). I'd like more of a standalone utility that I could easily
incorporate into our nightly batch processing. The people at Millennium
Information Systems (www.millenniumis.com) were working on just such a
product but they've apparently gone out of business (their phone's been
disconnected and they don't respond to e-mails).
Anyone know of such a product, or have anything in their bag of goodies
they'd be willing to share?
Thanks,
Dave
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
As an Amazon Associate we earn from qualifying purchases.
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.