Updating a TPS file from CSV file

4

Try the STREAM Broker and IF <> THEN PUT optimizations to see if that is fast enough.

If you are going the Queue route you could try indexing through the queue GET(Q, 1 2 3 4) instead of GET(Q,Q.McNumber) that searches the 500,000. The file and queue are both in Mc Number order so for each Broker record index forward until your queue is >= the McNumber:

EmailQ QUEUE,PRE(EmlQ)
McNumber    LONG        !EmlQ:McNumber
Email       STRING(128) !EmlQ:EMail
       END
EmNdx LONG        

    SORT(EMailQ,EmlQ:McNumber)  !Put in same order as file
    EmNdx=1 ; GET(EMailQ,1)     !load 1st Q record
    STREAM(Broker)
    SET(bro:by_mcnumber)       
L1: LOOP                        !Note L1: is a Label in column 1
        NEXT(Broker)
        IF ERRORCODE() THEN BREAK.

        !Below code could be in Process Template Validate Record
        LOOP WHILE EmlQ:McNumber < BRO:MCNUMBER
             EmNdx += 1 
             GET(EMailQ, EmNdx)  !<-- fix was ,1)
             IF ERRORCODE() THEN    !All Done with Q
                BREAK L1:           !Break Outside L1: LOOP on Broker
                ! Record:OutOfRange in Validate Record
             END 
        END !Loop Q

        IF EmlQ:McNumber <> BRO:MCNUMBER |      !Queue >  our Broker
        OR BRO:Email = EmlQ:EMail        THEN   !Email the same
           ! Record:Filtered  in Validate Record
        ELSE
           ! 	Record:OK
           BRO:Email = EmlQ:EMail
           PUT(Broker) 
        END
    END !Loop L1: on Broker
    FLUSH(Broker)

I would use a Process template to load the CSV File to Queue, then a separate Process to read Broker and update from EmailQ. That way user gets progress window. In that case the Loop While code goes in Validate Record.

2 Likes