ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need help: Read CSV file and validate the date record

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help: Read CSV file and validate the date record

    I am noob to CL just started to dig deep into getting record from a text file.

    How can I read the date field from a csv file and validate it if the date is equal to current date then insert the csv record into a table.

    ABC_Rate.csv file contains the following information:

    RIC,Ask Price,Bid Price,Universal Close Price Date
    ABC,.1234,.123,20200107

    If the Price Date (20200107) is equal to current date the record will be inserted into a table if not it will exit.

    Here is my code:

    START: PGM


    /* Declare required variables */

    DCL VAR(&DAY) TYPE(*CHAR) LEN(2)
    DCL VAR(&MONTH) TYPE(*CHAR) LEN(2)
    DCL VAR(&YEAR) TYPE(*CHAR) LEN(2)
    DCL VAR(&TRADEDATE) TYPE(*CHAR) LEN(8)


    /* Declare required files */

    DCLF FILE(QTEMP/FTPOUTPUT)
    DCLF FILE(QTEMP/ABC) OPNID(A)


    /* Check for any existing FTPINPUT file in QTEMP then ignore */

    CHKOBJ OBJ(QTEMP/FTPINPUT) OBJTYPE(*FILE)

    MONMSG MSGID(CPF0000) EXEC(CRTPF FILE(QTEMP/FTPINPUT) RCDLEN(112) OPTION(*NOSRC +
    *NOLIST))

    CLRPFM FILE(QTEMP/FTPINPUT)

    CPYF FROMFILE(TSTSRCXX/S19031A) TOFILE(QTEMP/FTPINPUT) FROMMBR(ABCKINFILE) +
    MBROPT(*REPLACE) FMTOPT(*CVTSRC)

    OVRDBF FILE(INPUT) TOFILE(QTEMP/FTPINPUT) OVRSCOPE(*CALLLVL)

    /* Create output file for monitoring the FTP status */

    CHKOBJ OBJ(QTEMP/FTPOUTPUT) OBJTYPE(*FILE)

    MONMSG MSGID(CPF0000) EXEC(CRTPF FILE(QTEMP/FTPOUTPUT) RCDLEN(132) OPTION(*NOSRC +
    *NOLIST))

    CLRPFM FILE(QTEMP/FTPOUTPUT)

    OVRDBF FILE(OUTPUT) TOFILE(QTEMP/FTPOUTPUT) OVRSCOPE(*CALLLVL)

    /* Connect to ABC FTP */

    FTP RMTSYS(*INTNETADR) INTNETADR(HOSTED.DATASCOPE.REUTERS.COM) PORT(21)

    /* Copy the output file */

    CPYF FROMFILE(QTEMP/FTPOUTPUT) TOFILE(TSTSRCXX/S19031A) TOMBR(ABCOUTFILE) +
    MBROPT(*REPLACE) FMTOPT(*CVTSRC)

    /* Scan FTPLOG, if SUCCESSFUL TRANSFER (226) found, clear FILE1; */
    /* else send operator message to check FTP status */

    RCVF: RCVF
    MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
    IF COND(%SST(&FTPOUTPUT 1 3) *EQ '226') THEN(DO)

    GOTO CMDLBL(STEP1)
    ENDDO
    ELSE CMD(DO)
    GOTO CMDLBL(RCVF)
    ENDDO

    ERROR: SNDSMTPEMM RCP((me@test.com *PRI)) SUBJECT('ABC ftp failed') NOTE('<p>Dear +
    Support, <br /> <br />The daily ABC file has failed to be downloaded from +
    ABC ftp site.<br/><br/><br/>Please do not reply.<br/> This is +
    an automated mailing system </p>') CONTENT(*HTML)

    GOTO CMDLBL(EXIT)

    /* Check is content date match with current date */

    STEP1: RTVSYSVAL SYSVAL(QDAY) RTNVAR(&DAY)
    RTVSYSVAL SYSVAL(QMONTH) RTNVAR(&MONTH)
    RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&YEAR)

    CHGVAR VAR(&TRADEDATE) VALUE('20' *CAT &YEAR *CAT &MONTH *CAT &DAY)

    /* Creat a temp table to insert the record into QTEMP/ABC THE BELOW PART CONTAIN THE ISSUE */

    RUNSQLSTM SRCFILE(TSTSRCXX/S19031A) SRCMBR(CBKKQTEMP)

    CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(QTEMP/ABC) RCDDLM(*CRLF) +
    FROMRCD(2) RPLNULLVAL(*FLDDFT)

    RCVF OPNID(A)
    MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EXIT))

    STEP2: IF COND(%SST(&A_ABC 21 8) *EQ &TRADEDATE) THEN(GOTO CMDLBL(STEP3))
    ELSE CMD(GOTO CMDLBL(EXIT))


    /* Clear after input/output files being processed */

    STEP3: DLTOVR FILE(*ALL)

    MONMSG MSGID(CPF0000)

    /* Insert ABC Rate into permanent table ABC */

    CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(TSTDTAXX/ABC) RCDDLM(*CRLF) +
    FROMRCD(2) RPLNULLVAL(*FLDDFT)

    EXIT: ENDPGM
    I appreciate any help.

  • #2
    You asked how to do it, but you also presented a program that does it? So I'm not sure what you're asking. Does your program not work?
    I'm not too familiar with doing file IO in CLLE, but I suspect your program will not compile, because it will expect both DCLF files to exist at compile time and they're both in QTEMP.

    Does it have to be CL? Some SQLRPGLE would make it easier.

    Comment


    • #3
      The program compiles fine after creating the 2 temporary files in QTEMP, the only problem is that I cannot get the csv 2nd row content into the temporary file using the command CPYFRMIMPF only partial data gets through , I tried the SRC parameter instead of DATA from the command CPYFRMIMPF in order to read the content but only the beginning part of the data is captured.

      Would like to know how to do the whole thing using SQLRPGLE but I am not familiar with the language.

      Comment


      • #4
        So you are saying that /home/ABC/ABC_Rate.csv contains multiple records (header + 2 or more data records), but this
        Code:
        CPYFRMIMPF FROMSTMF('/home/ABC/ABC_Rate.csv') TOFILE(QTEMP/ABC) RCDDLM(*CRLF) FROMRCD(2) RPLNULLVAL(*FLDDFT)
        is only retrieving the first data record into QTEMP/ABC?

        Or do you mean that you are getting all the records into QTEMP/ABC, but you cannot work out how to make your program read in more than just the first record?

        RCVF reads one record at a time. You have to code a loop to repeat the RCVF to read each record in turn.

        Comment


        • #5
          Does the QTEMP/ABC file have only one field in it? If so, try using the CPYFRMSTMF command instead: CPYFRMSTMF FROMSTMF('/home/ABC/ABC_Rate.csv') TOMBR('/qsys.lib/qtemp.lib/abc.file/abc.mbr') MBROPT(*REPLACE)

          Also, the test to see if the price date is equal to the current date [IF COND(%SST(&A_ABC 21 8) *EQ &TRADEDATE)] won't work with the example data (ABC,.1234,.123,20200107) because the price date starts in position 16, not position 21.

          Comment


          • #6
            Thank you all,

            I found a different approach for this problem, I had created a temporary table in QTEMP and then inserted the single record from the stream file, and then used SQL to insert the record from the temporary table into the production table where the date is equal to current date.

            As long as the date is equal to current date the record will be inserted into the production table.

            Comment

            Working...
            X