AS/400 CPYTOIMPF Help Needed

[SWTpaul]

I am trying to output a file, but have ran into a few things.

The command I am using is as follows:
===> CPYTOIMPF FROMFILE(BRANDNEW/CARCODE) TOSTMF('QIBM/CARCODE.TXT') MBROPT(*REPLACE) RCDDLM(*CRLF) FLDDLM('|')

Problems:
1) The output file that is created is in ASCII characters. I noticed if I created a blank file from my PC in the folder and then ran that command, it came through as plain text... how can I get it to do that while creating the file (so I don't have to create blank TXT files before running this command.

2) It does not put the header row in the output file. Is there a way to have the first line be the header row from the query?

Any help is appreciated, I am new to AS/400 commands.

Thanks

 

[Mercury2]

1/ Try

CPYTOIMPF FROMFILE(BRANDNEW/CARCODE)
TOSTMF('QIBM/CARCODE.TXT')
MBROPT(*REPLACE)
STMFCODPAG(*PCASCII)
RCDDLM(*CRLF)
FLDDLM('|')

Are you sure you want to create a stream file in the QIBM folder i.e. the system directory provided with the system ? Maybe you'd have better to use the /TMP or /Home/MyUsrprf/ directory instead ?

2/ There is no header record in an AS/400 physical file.

hth

 

[SWTpaul]

Thanks Talkturkey...

I did try to use the STMFCODPAG(*PCASCII) command but it returned the following statement: Keyword STMFCODPAG not valid for this command

Is there a way to get the header into the output file from the table field names?

 

[Mercury2]

You likely get an old OS version since STMFCODPAG(*PCASCII) not supported.
From the top of my head, like you I had to create a blank file in the IFS folder.

I don't understand why you want to get the fields names as first row in the ASCII file. What's your target ? Do you plan to show the BRANDNEW/CARCODE file in Excel ? If this is the case, well, the fields names can be transfered automatically with the data to the excelsheet.

 

[SWTpaul]

I need the header row because these files will be FTPed to another companies server where they need the header row to relate the fields to their program.

I can import files into Excel with the header using the builtin Client Access Excel plugin, but this needs to be an automated process.

Thanks for your help!

 

[flapeyre]

If the headers are always going to be the same, create a text file (CRTPF, prompt it, choose a file name, use RCDLEN(same_as_your_other_file) parameter) (use UPDDTA or another file utility to populate it with the headers in quoted comma-separated format), copy that file into the stream file with MBROPT(*REPLACE), then copy your data to the stream file with MBROPT(*ADD) (so the header isn't wiped out).


De mortuis nihil nisi bonum.

 

[mikrom]

You can solve the header row problem best with any programming language. I solved this problem using Python for iSeries.
The objekt File400 has method fieldList, that extract the field names from database.
Here is the necessary Python function, which use CPYTOIMPF command. I wrote the native python function first, but I had problems with encoding (I use characters from Codepage 1250) so at end I used better CPYTOIMPF.
I don't know if you know Python for iSeries? It's great tool. You can obtain it from

http://www.iseriespython.com/

And here is my mentioned function:
def file400_to_csv2(bibliothek,datei400,ifsdir,csvname='datei400',codepage='1250'):
f = File400(datei400,'r',lib=bibliothek)
fields = f.fieldList()
f.close()
header = string.join(fields,';')+'\n'

if csvname == 'datei400':
sfpath = os.path.join(ifsdir,datei400)
else:
sfpath = os.path.join(ifsdir,csvname)
sfpath = sfpath + '.csv'

aufruf = "CPYTOIMPF FROMFILE("+bibliothek+"/"+datei400+")"+\
" TOSTMF('"+sfpath+"') MBROPT(*REPLACE)"+\
" STMFCODPAG("+codepage+") RCDDLM(*CRLF) FLDDLM(';') DECPNT(*COMMA)"

result=os.system(aufruf)

sf = open(sfpath,"r")
lines= sf.readlines()
sf.close()

lines.insert(0,header)

sf = open(sfpath,"w")
sf.writelines(lines)
sf.close()

If you need more advice concerning this problem, mail me.

 

[HitechUser]

I believe this will handle everything you need..

 /*                                                               */
 /*                             \\\\\\\                           */
 /*                            ( 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

Good Luck...

 

[JimGE]

For a tab delimited file, first you have to copy the physical file to a stream file in the document library. Notice no STMFCODPAG(*PCASCII).

CPYTOIMPF
FROMFILE(BRANDNEW/CARCODE)
TOSTMF('QIBM/CARCODE')
RCDDLM(*CRLF)
DTAFMT(*DLM)
STRDLM('"')
FLDDLM(X'05')
DECPNT(*PERIOD)
MBROPT(*ADD)
DATFMT(*USA)
TIMFMT(*USA)

Here is where the stream file is converted to PC file.

COPY OBJ('QIBM/CARCODE')
TOOBJ('QIBM/CARCODE.TXT')
TOCODEPAGE(*PCASCII)
DTAFMT(*TEXT)

Jim