Updating a TPS file from CSV file

1

It has been several years since I did this so know I am missing something. Below works but takes hours instead of minutes. Was recently sent a csv
file of emails to add to the tps file. Approximately 500,000 records.
Thanks for any advice. Clarion9.1

Stream(vemail)
Stream(temail)

!Activity for each record

BRO:MCNUMBER=tem1:mcnumber
get(broker,bro:by_mcnumber)
if ~error()
BRO:Email=tem1:email
put(broker)
END

Flush(temail)
Flush(vemail)

Don Harvey
The Trucking Register

2

You should Stream(broker) the file you are writing.

You can save some time only doing the PUT if the email changed. Testing If ErrorCode() is a Long so faster than Error() that returns a String.

IF ~ErrorCode() and BRO:Email <> tem1:email THEN
  BRO:Email=tem1:email
  put(broker)
END

A bigger change would be to first read all the CSV into a Queue. Then process the Broker file using Set(Broker) ; Loop ; Next(Broker) and Get from the CSV in a queue. Doing 500,000 Gets on a memory queue should be faster than Gets on a file.

3

Thanks Carl,

Will give this a try. I think using a Queue probably the best. The emails are now put in a separate csv file from monthly data received monthly, a real pain to now have to process 2 files.

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
5

Thanks Carl,

The help is very much appreciated.

Making the changes on the first method of just the put processing cut the time down to around 15 minutes. Big change from a couple of hours.

This weekend going to work on using queue from your posting. Thanks
again for your help and advice. I haven’t had to do anything on this program
for five or six years and realize I have forgot a lot.

Will let you know how it goes.

Don Harvey
www.truckingregister.com