Create arrItems from csv dynamically from the date field

[weklica]

TEMP0000005437,wakley,jesse,M,11/09/1956,Active,OUT

See the example above,

Sometimes the line may have a middle initial included, and other times it does not. Sometimes it has the last field 'OUT' and other times it does not. That throws the whole arrItems numbering out the door. Last name and First name never change, but there is sometimes a middle initial between first name and gender (M,F,O), is there a way to have it search the string for the date to dynamically assign arrItems? It would also allow me to let the script understand that the Gender is ALWAYS one field before the date. Therefore, the constants for creating the UID would be %arrItems_2%, %arrItems_3%, %1 field before date%, %date%.

So far, arrItems are numbered from :

Code: Select all

Let>k=1
Label>start
ReadLn>c:\testwrite.txt,k,line
If>line=##EOF##,finish
//Message>line
Let>data=%line%
Let>comma=,
Separate>data,comma,arrItems

MidStr>arrItems_2,1,4,mylast
MidStr>arrItems_3,1,3,myfirst
Let>UID=%mylast%%myfirst%%arrItems_4%%arrItems_5%
WriteLn>c:\testwrite.csv,nWLNRes,%UID%%comma%%data%
Let>k=k+1
Goto>start
Label>finish

So, the part that is killing me is that arrItems_4 and arrItems_5 are often times %arrItems_5% and %arrItems_6%. The thing that is always the same though is that these two fields are always right by eachother and the last of the two is ALWAYS a date as indicated in the example line at the top of this message.

Many Thanks in advance!!!!

Jesse

Thanks!

[/code]

[Marcus Tettmar]

Take a look at this:

Code: Select all

//Let>orig_line=123456,WAKLEY,JESSE,M,01/01/2000,Active,OUT OR
Let>orig_line=123456,WAKLEY,JESSE,K,M,01/01/2000,Active,OUT

//surname always part 2, forename always part 3
//then could be any number of middle initial fields
//then we have a date

//So surname and forename easy:
Let>comma=,
Separate>orig_line,comma,parts
Let>surname=parts_2
Let>forename=parts_3

//initialise middle initials to empty string
Let>middle_initials=

//now we could poll forward, concatinating initials until we reach a field which is a date. (looks like nn/nn/nnnn)
Let>loop_k=3
Repeat>loop_k
  Let>loop_k=loop_k+1
  Let>this_part=parts_%loop_k%
  //is it a date?
  RegEx>\d\d/\d\d/\d\d\d\d,this_part,0,matches,nm,0
  If>nm=1
    //must be a date
    Let>the_date=this_part
    //we can stop looping
    Goto>done_date_loop
  Else
    //must be a middle initial
    Let>middle_initials=%middle_initials%%this_part%
  Endif
Until>loop_k=parts_count
Label>done_date_loop

/*
so now we should have all our values we need in:
 surname, forename, middle_initials, the_date
*/

//we want 1st 4 chars of surname
MidStr>surname,1,4,surname
//1st 3 of forename
MidStr>forename,1,3,forename

Let>new_line=%surname%%forename%%middle_initials%%the_date%,%orig_line%
MessageModal>new_line

//I've not added checks for surname being 4 or more chars, and forename 3 or more - will need those to avoid errors
//I've also assumed you want all middle initials in the UID.  If not you can strip off the first or last 1 or whatever