Help! STRTRN() and Array question...

[Geoffrey Hollander]

Hi,

This one has me stumped. I am trying to set up a script that will perform a STRTRAN() on a target field in the master table (WorkFile) using a set of values contained in a two-field transaction table called Replace. The target field in the master table is, ADD2, and the two fields in the transaction table are, FIND (the search string) and REPLACE (the replacement string).

Although I have gotten it to work using WHILE� END WHILE statements which cycle through the transaction table using the sets of values on a record by record basis, applying a STRTRN() statement for each, then replacing the ADD2 field�s content with the result, it takes a long time. If you have 200-500 records in the transaction table�

So, it seemed to me that by copying all of the transaction table�s values into two arrays (Finds[1000] and Replaces[1000]) that could be used in the STRTRAN() function over and over again, the operation would run much faster using these memory variables as the operation wouldn�t have to take extra time to re-read all of the transaction table�s records every time a master table record is reviewed/updated.

I wrote a script that runs (see below), but it only searches and replaces the last value in each of the arrays when it updates the master table�s records.
The problem seems to arise from the FOR� NEXT statement, but I can�t tell what it is. INSIGHT, is what�s needed here, but I am apparently fresh out, and would greatly appreciate any that might be offered�

Thanks,

Geoff Hollander

CODE:

''XBasic

DIM tbl as P
DIM srch as P
DIM counter as N
DIM i as N

DIM number_srch_recs as N
DIM global Finds[1000] as C
DIM global Replaces[1000] as C

counter = 0

tbl = table.open(WorkFile)
srch = table.open(Replace)

number_srch_recs = srch.records_get()

srch.batch_begin()
srch.fetch_first()

WHILE .not. srch.fetch_eof()

counter = counter + 1
find_str = srch.find
replace_str = srch.replace

Finds[counter] = alltrim(find_str)
Replaces[counter] = alltrim(replace_str)

srch.fetch_next()

END WHILE

srch.batch_end()


tbl.batch_begin()
tbl.fetch_first()

WHILE .not. tbl.fetch_eof()

FOR i = 1 TO number_srch_recs

field_old = tbl.add2
replacement = strtran(field_old, Finds[i], Replaces[i])

NEXT i

tbl.change_begin()
tbl.add2 = replacement
tbl.change_end(.t.)

tbl.fetch_next()

END WHILE

tbl.batch_end()

END

[Stan Mathews]

RE: Help! STRTRN() and Array question...

Geoffrey,

I think that

FOR i = 1 TO number_srch_recs

field_old = tbl.add2
replacement = strtran(field_old, Finds[i], Replaces[i])

NEXT i

should be


field_old = tbl.add2

FOR i = 1 TO number_srch_recs

replacement = strtran(field_old, Finds[i], Replaces[i])

NEXT i

otherwise you are resetting field_old to tbl.add2 each time before you strtran().

Stan

[Geoffrey Hollander]

RE: Help! STRTRN() and Array question...

Hi Stan,

Thanks for the suggestion. It certainly makes sense but, it does the same thing; only searching and replacing the last set of values in each array.

It just seems as though it should work. Oh well, I guess it's time to meet with my friend Jose Cuervo once again...

Best regards,

Geoff Hollander

[csda1]

RE: Help! STRTRN() and Array question...

Geoffrey,

As I said in my e-mail to you, it would be alot more efficient to search for each word in the source table than for each word in the replacement table in the source. Imagine a text of 5 words. And a replace list of 1000 words. One way you need to check 5 items in the list, the other way you need to check 1000 times for the item in the source. Using sort_array() you can sort the array, then do a binary search for the item. Because sort_array() only sorts 1 array at a time, you need to keep the find and replace strings together. I do that by placing them both into the same array, separated by a tilde character. Then I take everything before the tilde for the find and everything after for the replace.

Your code fails because it never updates field_old. It should be something like

field_old= tbl.add2
replacement = field_old

FOR i = 1 TO number_srch_recs
replacement = strtran(replacement, Finds[i], Replaces[i])
NEXT i

' this saves changing when nothing needs to be done.
IF .not.(replacement==field_old)
tbl.change_begin()
tbl.add2 = replacement
tbl.change_end(.t.)
END IF

tbl.fetch_next()

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Stan Mathews]

RE: Help! STRTRN() and Array question...

Ten more days and you ought to spend the night with him going over old times.

[Peter.Wayne]

RE: Help! STRTRN() and Array question...

try

replacement=tbl.add2
FOR i = 1 TO number_srch_recs
replacement = strtran(replacement, Finds[i], Replaces[i])
NEXT i

etc.

[Geoffrey Hollander]

RE: Help! STRTRN() and Array question...

Hi,

Thanks, everyone, for your help on this. The various scripts that you sent got me going pretty well. The only problem is they�re slow when there are a lot of records in the Replacement Table. Because the lists on which I work are so varied in their field content and because a script like this has to correct most of them to really be of value, the Replacement Table is just going to keep growing (mine is at 800-900 recs now).

Factor in that not only the strings have to be exchanged in most records, but also that they have to often be rearranged. For example, changing "3rd Floor" to 3rd Fl" is only half of the problem. The final string needs to be "Fl 3". As a result, I've added many strings into the Replace Table like, "3rd Floor" and "Fl 3". Mail Stop Codes are another "cute" example. My point is that this should be able to work on large replacement tables.

I took your code example and tried loading the Replacement Table's values into arrays - so to not have to repeatedly read the Replace Table's values -- and while it worked, it didn't improve things much. In fact, it often consumed all my system memory and forced Win 2K to enlarge the swap file! Just let an amateur try and pull THAT one off with a little XBasic code!!

After working through the different ways of doing this I finally came up with an idea late last night. It's not elegant, but it's pretty fast, can be applied to any field/replacement table setup; for instance the using the A_Case.dbf file when casing from within XBasic. What was taking 40 minutes+ with a master table of 10,000 records and a transaction (replacement) table of 800-900 records now takes 3 minutes with the script below. I'm going to keep refining it, so that blank fields won't be processed, etc. but I think this will solve it. You can see the code below. Anyway, I just wanted to let you know and thank all of you once more for all your help.

Geoff Hollander

CODE:

DIM tbl as P
DIM srch as P
DIM counter as N
DIM i as N

DIM number_srch_recs as N
DIM global replacement as C
DIM global Finds[2500] as C
DIM global Replaces[2500] as C


srch = table.open(Replace)

number_srch_recs = srch.records_get()

counter = 0
srch.batch_begin()
srch.fetch_first()

WHILE .not. srch.fetch_eof()

counter = counter + 1
find_str = srch.find
replace_str = srch.replace

Finds[counter] = alltrim(find_str) + " "
Replaces[counter] = alltrim(replace_str) + " "

srch.fetch_next()

END WHILE

srch.batch_end()


tbl = table.open(WorkFile)

tbl.batch_begin()

FOR i = 1 TO number_srch_recs

statusbar.clear()

update.fields = 1

update.field1 = "ADD2"
update.expr1 = "strtran(working->ADD2, '"+Finds[i]+"', '"+Replaces[i]+"')"

tbl.update()

NEXT i

tbl.batch_end()

END