Re: Deleting first record from stream files in IFS.

[Scott Klement <klemscot@xxxxxxxxxxxx>]

Hi Kelly,

> My problem is dealing with the headings when
> transferring data from the CSV to a DB2 not designed
> to handle a record for headings. My only idea at this
> point is to use the IFS APIs in an HLL program to read
> all CSV records except the first one into a temporary
> CSV file, then perform CPYFRMIMPF on the temporary CSV
> file.

As John Jones pointed out, this can be done quite easily with QShell.
But, since I'm tired of the project that I'm working on today, I thought
I'd elaborate :)

Here's a CL program that does just that, uses QShell to remove the first
record of the stream file, and then use CPYFRMSTMF to put it into a PF:

PGM

     DCL VAR(&INPSTMF) TYPE(*CHAR) LEN(255) +
           VALUE('/home/klemscot/myfile.csv')
     DCL VAR(&TEMPSTMF) TYPE(*CHAR) LEN(255) +
           VALUE('/tmp/myfile.csv')
     DCL VAR(&OUTMBR) TYPE(*CHAR) LEN(255) +
           VALUE('/QSYS.LIB/TIPSNL.LIB/MYFILE.FILE/MYMBR.MBR')
     DCL VAR(&CMD) TYPE(*CHAR) LEN(500)

 /* Tell QShell to return errors as *ESCAPE messages if anything +
    goes wrong, and not to display anything on the screen.   */

     RMVENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT)
     MONMSG MSGID(CPFA981)
     RMVENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG)
     MONMSG MSGID(CPFA981)

     ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE)
     ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y)

 /*  Strip the first line from the stream file */

     CHGVAR VAR(&CMD) VALUE('tail -n +2' *BCAT &INPSTMF +
                               *BCAT '>' *BCAT &TEMPSTMF)
     STRQSH CMD(&CMD)

 /*  Convert to DB2 database file */

     CPYFRMSTMF FROMSTMF(&TEMPSTMF) TOMBR(&OUTMBR)


 /*  done! */

     RMVENVVAR  ENVVAR(QIBM_QSH_CMD_OUTPUT)
     RMVENVVAR  ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG)

ENDPGM


But, that's just the tip of the iceberg.  You could change the QShell
command to instead include lines that contain a given string:

 /*  Only include records that contain the word "MILWAUKEE" */

     CHGVAR VAR(&CMD) VALUE('grep -i "MILWAUKEE"' *BCAT &INPSTMF +
                                    *BCAT '>' *BCAT &TEMPSTMF)
     STRQSH CMD(&CMD)


Or, do the opposite and include anything that does not contain that
string:

 /*  Only include records that don't contain the word "Kelly" */

     CHGVAR VAR(&CMD) VALUE('grep -vi "MILWAUKEE"' *BCAT &INPSTMF +
                                     *BCAT '>' *BCAT &TEMPSTMF)
     STRQSH CMD(&CMD)


Or maybe you wanted to sort the CSV before importing it.  For example,
this sorts it by the 4th field in the file.

 /*  Sort by the 4th field in the CSV file */

     CHGVAR VAR(&CMD) VALUE('sort -t , -k 4'   *BCAT &INPSTMF +
                                     *BCAT '>' *BCAT &TEMPSTMF)
     STRQSH CMD(&CMD)


(The "-t ," means "use , as a field separator" and "-k 4" means "field 4
is the key")

>
> Anyone have any better ideas for deleting the first
> line (headings) in a stream file in the IFS?
>

An alternative, if you for some reason would prefer to avoid QShell, would
be to just remove the headings manually:

   EDTF '/home/klemscot/myfile.csv'

Unless it's a one-time shot, I always prefer to put everything into a
program.  Makes training a lot easier ;)