Repeat whiles and text processing

[Rustywinger]

For the life of me, I cannot grok text processing.

 

I capture a reference number from a record in a program, and then try to match it to a name in an Excel file saved as a comma delimited CSV. The Excel file has two columns, one with a match for the aforementioned number and the name associated with it in the next. It has 10800 rows in it, and the file size is 223kb.

 

 

The crude way I have this set up to work right now is:

 

    Variable Set Integer %N60% to 0
   Repeat Until %T57% Contains "%T58%"
     Text File Begin Process: "referring_lookup.txt"
     Text File End Process
     Variable Modify Integer: %N60% = %N60% + 1 
   Repeat End

<IVAR2:60:01:0><REP3:08:000007:000001:0057:0:01:%T58%>
<BTFBEG:057:N00060:000001:\\nevermindthis\referring_lookup.txt>
<BTFEND><NMVAR:01:60:1:0000060:2:0000001>
<TBOX4:T:1:CenterCenter000278000200:000:T58=%T58%-T57=%T57%><ENDREP>

 

%T58% is the captured reference number, which is the condition for the repeat loop to end.

%T57% is the destination variable for the result from the Text File Begin Process (TFBP) command.

%N60% is an incremented integer within the loop that is used as the starting line value within the TFBP. The TFBP is processing one line at a time.

 

I have no doubt I am doing this wrong, it takes forever to process the file line by line, but I can't see how to just load the whole thing into a variable and get the information I need, which is a single row matching the criteria being put into another variable.

[floyd]

Post the code in its native format (copy, paste) along with the "Copy Command Text" posting. I t makes it easier to see what is happening.

 

The reason it is taking so long is there is a lot of unnecessary looping going on.

[Rustywinger]

Post the code in its native format (copy, paste) along with the "Copy Command Text" posting. I t makes it easier to see what is happening.

 

The reason it is taking so long is there is a lot of unnecessary looping going on.

 

Ok, I edited the original post to include what I think it is you're asking for... Thanks!

[kevin]

I don't think you want to use a Repeat loop around the process commands. Both the Text File Begin Process and Text File End Process commands and ASCII File Begin Process and ASCII File Begin Process sets of commands loop through the file. If you want to examine each line from the input file as one item then you use the Text File Begin/End Process commands. However, if you want different parts of each line then you want to use the ASCII File Begin/End Process commands.

 

This example is from the help file:

ASCII File Begin Process: "test.txt" (Tab Delimited Text )
  Text Box Display: T1 T2 T3 T4 T5 T6 T7 T8 T9
ASCII File End Process

You can terminate the process loop when a condition is met using the Break command. Something like this:

ASCII File Begin Process: "test.txt" (Tab Delimited Text )
 Text Box Display: T1 T2 T3 T4 T5 T6 T7 T8 T9
 If Variable %T5% Contains "%T8%"
Break
 End if
ASCII File End Process

[Rustywinger]

Thanks!!! That makes the lookup nearly instant!

 

One last question: The data is in two columns, I want the results of this lookup to display the full row in a multiple choice menu.

 

When I put the resulting Variable %T57% in a dialog box for testing, the data from the entrie row is visible, but when I put the variable into a mutltiple choice dialog box, it only shows the contents of the first row of the result.

 

1- Original Excel tab-delimited text File looks like this:

 

555555 (col) firstname,lastname

 

 

2- When displaying this result in an ME dialog box I get:

 

555555 (tab) "firstname,lastname" (that's right, somewhere in this process, quotes get added to the name)

 

 

3- When displaying it in the multiple choice dialog box I get:

 

555555

[kevin]

You could put %T57%%T58% in the Multiple Choice Menu.

[Rustywinger]

You could put %T57%%T58% in the Multiple Choice Menu.

 

 

Hi, Kevin! Thanks for sharing your brain activity! :-)

 

What I don't understand is all the information I need has been put into %T57%, yet it is being treated differently when being displayed in different ME dialog boxes, which are an itegral part of making this macro work.

 

When I put %T57% in a Text Box Display, all the info is there, but there are quotes around the name that mysteriously appeared, and when I put %T57% in a Multiple Choice Menu, part of the string disappears. (the part in quotes).

 

I'm curious mainly because I want to know if I am up against a known issue or simply that my programming sucks! :-)

[kevin]

Sorry, I missed a detail in your post. The <TAB> is probably messing up the Multiple Choice Menu command. Strip the <TAB> and replace it with a space or comma or whatever. This will replace the <TAB> with 4 spaces:

// Put the <TAB> character in T9
Variable Set %T9% to ASCII Char of 9
// Replace all tabs (T9) with 4 spaces
Replace "%T9%" with "	" in %T57%

To find these commands search for Variable Set to ASCII Char and Variable Modify String Replace Substring option.