I've attached the source in a text file. Cmd, Cl, Rpg.
/dick
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pat Barber
Sent: Tuesday, May 18, 2010 12:43 PM
To: Midrange Systems Technical Discussion
Subject: Re: CSV file with a header
I'll take a peek at that also...
Richard wrote:
I've been using DB2EXL. I downloaded it from one of the midrange sites It
copies a B file to IFS, with/without col. Headers.
/dick
Copy DB file to Excel format
/* */
/* \\\\\\\ */
/* ( o o ) */
/*------------------------oOO----(_)----OOo----------------------*/
/* */
/* Command : CPYTOXLS version 2.00 */
/* System : iSeries */
/* Author : Herman Van der Staey August 12, 2002 */
/* */
/* Copy database file to EXCEL format */
/* and include (ALIAS) field names. */
/* */
/*---------------------ooooO--------------Ooooo------------------*/
/* ( ) ( ) */
/* ( ) ( ) */
/* (_) (_) */
/* */
/* To compile : */
/* */
/* CRTCMD CMD(XXX/CPYTOXLS) PGM(XXX/CPYTOXLS) + */
/* SRCFILE(XXX/QCMDSRC) */
/* */
CPYTOXLS: CMD PROMPT('Copy to EXCEL format')
PARM KWD(FILE) TYPE(FILENAME) PROMPT('File name')
PARM KWD(MBR) TYPE(*NAME) LEN(10) DFT(*FIRST) +
SPCVAL((*FIRST)) PROMPT('Member name')
PARM KWD(TOFILE) TYPE(*CHAR) LEN(64) +
DFT(MYFILE.CSV) MIN(0) EXPR(*YES) +
CASE(*MIXED) PROMPT('IFS filename + +
extension CSV')
PARM KWD(TODIR) TYPE(*PNAME) LEN(128) +
DFT('/mydir') CASE(*MIXED) PROMPT('To IFS +
directory')
PARM KWD(FIELDNAMES) TYPE(*LGL) DFT(*YES) +
SPCVAL((*YES '1') (*NO '0')) MIN(0) +
EXPR(*YES) CHOICE('*YES, *NO') +
PROMPT('Include Fieldnames')
PARM KWD(ALIAS) TYPE(*LGL) DFT(*YES) SPCVAL((*YES +
'1') (*NO '0')) MIN(0) EXPR(*YES) +
CHOICE('*YES, *NO') PROMPT('Use ALIAS +
fieldnames')
FILENAME: QUAL TYPE(*NAME) LEN(10) MIN(1)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*CURLIB) (*LIBL)) PROMPT('Library')
/* Program : CPYTOXLS version 2.00 */
/* System : iSeries V5Rx */
/* Author : Herman Van der Staey August 12, 2002 */
/* */
/* Copy database file to EXCEL format */
/* and include (ALIAS) field names. */
/* */
/* The file will be copied to CSV format (comma separated */
/* values), which can directly be imported in EXCEL if */
/* you give the filename the extension ".CSV" */
/* */
/* To compile : */
/* */
/* CRTCLPGM PGM(XXX/CPYTOXLS) SRCFILE(XXX/QCLSRC) */
CPYTOXLS: PGM PARM(&FILE &FROMMBR &TOFILE &TODIR +
&FIELDNAMES &ALIAS)
DCLF FILE(QSYS/QADSPFFD) /* File field reference +
file */
DCL VAR(&FILE) TYPE(*CHAR) LEN(20)
DCL VAR(&FROMFILE) TYPE(*CHAR) LEN(10)
DCL VAR(&FROMLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&FROMMBR) TYPE(*CHAR) LEN(10)
DCL VAR(&TOFILE) TYPE(*CHAR) LEN(64)
DCL VAR(&TODIR) TYPE(*CHAR) LEN(128)
DCL VAR(&TOSTMF) TYPE(*CHAR) LEN(193)
DCL VAR(&MBROPT) TYPE(*CHAR) LEN(10)
DCL VAR(&NOT_FIRST) TYPE(*LGL) LEN(1) VALUE('0')
DCL VAR(&FIELDNAMES) TYPE(*LGL)
DCL VAR(&ALIAS) TYPE(*LGL)
DCL VAR(&ST) TYPE(*CHAR) LEN(1024)
DCL VAR(&COMMA) TYPE(*CHAR) LEN(1) VALUE(',')
DCL VAR(&DBLQUOTE) TYPE(*CHAR) LEN(1) VALUE('"') +
/* Double quote */
CHGVAR VAR(&FROMFILE) VALUE(%SST(&FILE 1 10))
CHGVAR VAR(&FROMLIB) VALUE(%SST(&FILE 11 10))
CHGVAR VAR(&TOSTMF) VALUE(&TODIR *TCAT '/' *CAT +
&TOFILE)
IF COND(&FIELDNAMES) THEN(CHGVAR VAR(&MBROPT) +
VALUE(*ADD))
ELSE CMD(CHGVAR VAR(&MBROPT) VALUE(*REPLACE))
IF COND(&FIELDNAMES) THEN(DO) /* Fieldnames */
DSPFFD FILE(&FROMLIB/&FROMFILE) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/FIELDNAMES)
OVRDBF FILE(QADSPFFD) TOFILE(QTEMP/FIELDNAMES)
NEXT: RCVF
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EOF))
IF COND(&NOT_FIRST) THEN(CHGVAR VAR(&ST) +
VALUE(&ST *TCAT &COMMA))
CHGVAR VAR(&NOT_FIRST) VALUE('1')
IF COND(&WHALIS *NE ' ' *AND &ALIAS) THEN(DO)
CHGVAR VAR(&ST) VALUE(&ST *TCAT &DBLQUOTE *CAT +
&WHALIS *TCAT &DBLQUOTE)
ENDDO
ELSE CMD(DO)
CHGVAR VAR(&ST) VALUE(&ST *TCAT &DBLQUOTE *CAT +
&WHFLDI *TCAT &DBLQUOTE)
ENDDO
GOTO CMDLBL(NEXT)
EOF: DLTF FILE(QTEMP/FIELDNAMES)
CRTPF FILE(QTEMP/PF1024) RCDLEN(1024)
OVRDBF FILE(PF1024) TOFILE(QTEMP/PF1024)
CALL PGM(WRTPF1024) PARM(&ST) /* Call the RPG +
program */
CPYTOSTMF +
FROMMBR('/qsys.lib/qtemp.lib/pf1024.file/pf+
1024.mbr') TOSTMF(&TOSTMF) +
STMFOPT(*REPLACE) STMFCODPAG(*PCASCII) +
ENDLINFMT(*CRLF)
DLTF FILE(QTEMP/PF1024)
ENDDO /* Field names */
CPYTOIMPF FROMFILE(&FROMLIB/&FROMFILE &FROMMBR) +
TOSTMF(&TOSTMF) MBROPT(&MBROPT) +
STMFCODPAG(*PCASCII) RCDDLM(*CRLF) +
DTAFMT(*DLM) STRDLM(&DBLQUOTE) +
FLDDLM(&COMMA) DECPNT(*PERIOD)
END: ENDPGM
/* The parameter STMFCODPAG(*PCASCII) can be added */
/* on the CPYTOIMPF command starting from release V5R1. */
/* The file is useless for EXCEL if not in ASCII format. */
/* The FLDDLM (field delimiter) and DECPNT (decimal point) */
/* parameters must correspond with the settings on your PC. */
/* Check via : */
/* Start, Control Panel, Regional Settings, Number */
/* and verify the "decimal symbol" and "list separator" */
/* settings. */
/* f.e. in Belgium you must code : */
/* FLDDLM(';') DECPNT(*COMMA) */
/* To make the EXCEL file available to your PC : */
/* */
/* 1) You can FTP the file in the IFS to your PC */
/* */
/* 2) You can share the directory in the IFS via */
/* Operations Navigator. */
/* (Check that the Netserver is started and configured.) */
/* On the PC you can map the shared directory to a drive */
/* letter. Example : */
/* net use x: \\as400netservername\sharename */
H*****************************************************************
H*
H* Program : WRTPF1024
H*
H* Add a record to file PF1024
H*
H*
H* To compile :
H*
H* CRTRPGPGM PGM(XXX/WRTPF1024) SRCFILE(XXX/QRPGSRC)
H*
H*****************************************************************
FPF1024 O F 1024 DISK A
IOUTREC DS
I 1 256 PART1
I 257 512 PART2
I 513 768 PART3
I 7691024 PART4
C *ENTRY PLIST
C PARM OUTREC
C WRITEPF1024 OUTREC
C MOVE *ON *INLR
Thanks to Herman Van der Staey
midrange.com
