Splitting Character Fields
Dan Madoni

As a support technician for our database products, I have frequently
come across a problem that is particularly tough for some users to
correct.  The problem typically appears in the form of one Name field
containing a first and last name being split into two separate field
s.  It is not easy to accomplish this because the length of a name is
always variable.  You can't simply take a SUBSTR() of the first x
amount of characters and the second y amount of characters and store
them to their respective fields.

Further,  a compunding factor can make this task even more complex:
that of having to split up more than two character strings.  For
example, a customer who was working at a community college told me
that she had a field that had a teacher's first and last name, an ID
number, class title, and two or three other data items; and that these
items had to be contained in their own separate fields.

Since there is no easy way to solve this problem, I always ended up
writing a small program from a basic engine that I knew would work.  I
would then customize it to meet the customer's specific scenario. 
Needless to say, this became a time consuming affair for all
concerned.

As a result of all this, I have written an all-purpose data-splitting
program that will work in all the above scenarios in addition to
providing some flexibility as to how data should be divided.

Enter in the program which begins on the following page using MODIFY
COMMAND from within dBASE IV at the dot prompt or use your own text
editor.  Compile the program, then, whenever you have a need to divide
a field into two or more smaller fields as described in the previous
section, you can use this program to accomplish the task.

How the Program Works

As an example of how to use dSPLIT, assume that you have a file called
Clients with a field called Name.  Let's say that the contents of Name
consists of a last name, first name, and a middle initial; and that
the field is to be divided into FirstName, Middle, and LastName.

Before you can actually use the program, you must first modify your
database structure to contain the new field names, (which in this case
are FirstName, Middle, and LastName).  At the dot prompt, enter DO
DSPLIT.  At the top of the screen, a box will appear, requesting a
filename.  Notice that the entry area leaves enough room for an
optional pathname and extension.  At the prompt, enter Clients.

The next box that appears prompts for a Master Field.  This is the
field which is intended to be split.  At this prompt, enter Name.

Underneath the Master Field box, another box appears requesting Field
#1.  This is where the field names that will receive the parsed data
are to be entered.  At the Field #1 prompt, enter FirstName.  Notice
that the Field #1 prompt becomes Field #2 after you press Enter.  Type
in  Middle at Field #2 and LastName at Field #3.
Now that all the split fields have been entered, press Escape.  The
Field # box disappears and another box containing the names of the
fields you entered takes its place.
Press the DnArrow and UpArrow keys to scroll the fields up and down. 
Notice also that the top field is always highlighted.  This is where
we are going to decide which occurrence of a space in the Name field
corresponds to the highlighted field.  Position the FirstName field in
the highlighted area and press Enter.  At the bottom, a prompt appears
requesting an occurrence value.  Remember that the Name field begins
with a last name, and then is followed by a first name and middle
initial.  Since the first name is the second item, a 2 is entered at
the prompt below.  Next, move Middle to the highlight, press Enter,
and enter a 3 at the prompt since MIiddle is the third data item in
the Name field.  Finally, position LastName in the highlight, press
Enter, and enter the number 1.

Once you have selected the occurrence specifications, dSPLIT is ready
to do its thing.  Press Escape to continue.  dSPLIT will display how
many records need to be processed on the lower left-hand side of the
screen along with how many records are completed on the lower
right-hand side.

Once the program has completed processing, BROWSE through your
database and note that the new fields have values in them.  At this
point, you can MODIFY STRUCTURE to delete the master field.     

* Program ...: DSPLIT.PRG
* Author ....: Dan Madoni
* Date ......: June 1990
* Versions ..: dBASE IV  
* Notes .....: Use this program to divide character strings within   
*              a field into other fields.
         
SET ESCAPE OFF 
SET BELL OFF   
SET STATUS OFF 
SET CONFIRM ON   
SET TALK OFF
SET ECHO OFF
SET CLOCK OFF

looping = .T.
IF ISCOLOR() 
        SET COLOR TO W/,B/W,N/N
ENDIF
CLEAR
         
DO WHILE looping 
        CLEAR
        SET COLOR TO BG/ 
        @ 1,1 TO 3,79
         
        *--- Allow user to input a File Name with Path and Extension.
        *--- Check for valid file.
        DO WHILE .T. 
                SET COLOR TO W+/
                usefile = SPACE(40) 
                @ 2,3 SAY "Filename:" GET usefile PICTURE "@!40"   
                READ 
         
                IF usefile = SPACE(40) .OR. READKEY() = 12 
                        looping = .F.  
                        EXIT   
                ENDIF
           
                usefile = RTRIM(usefile)   
                IF AT(".",usefile) = 0 
                        usefile = usefile + ".DBF"   
                ENDIF  
           
                IF .NOT. FILE(usefile) 
                        SET COLOR TO R+/ 
                        ?? CHR(7)
                        @ 23,1 SAY "The file you entered does not
exist" 
                        DO WHILE INKEY() = 0 
                        ENDDO
                        @ 23,1 SAY SPACE(70) 
                ELSE   
                        EXIT 
                ENDIF  
        ENDDO  
           
        IF .NOT. looping   
                LOOP   
        ENDIF  
        usefile = RTRIM(usefile)   
        USE &usefile   
           
        SET COLOR TO BG/   
        @ 4,1 TO 6,79  
         
        *--- Allow user to input Master Field.
        *--- Check for valid field.
        master = SPACE(12) 
        DO WHILE .T.   
                SET COLOR TO W+/   
                @ 5,3 SAY "Master Field:" GET master PICTURE "@!12"
                READ   
           
                IF master = SPACE(12)  
                        looping = .F.
                        EXIT 
                ENDIF  
           
                IF TYPE(RTRIM(master)) <> "C"  
                        SET COLOR TO R+/ 
                        ?? CHR(7)
                        @ 23,1 SAY "The field you entered is not in
the file or not a character field"
                        DO WHILE INKEY() = 0 
                        ENDDO
                        @ 23,1 SAY SPACE(78) 
                ELSE   
                        EXIT 
                ENDIF  
        ENDDO  
                                           
        IF .NOT. looping   
                LOOP   
        ENDIF  
        master = RTRIM(master) 
                                           
        SET COLOR TO BG/   
        @ 7,1 TO 9,79  

        *--- Allow user to enter up to 50 fields.
        *--- Validate each field.
        fieldcnt = 0   
        getflds = .T.  
        DECLARE gofield[50]
        DECLARE numspac[50]
        DO WHILE getflds   
                fieldcnt = fieldcnt + 1   
                STORE SPACE(12) TO gofield[fieldcnt]
                STORE fieldcnt TO numspac[fieldcnt] 
           
                DO WHILE .T.   
                        SET COLOR TO W+/ 
                        @ 8,3 SAY SPACE(50)  
                        @ 8,3 SAY "Enter Field #" +
LTRIM(STR(fieldcnt)) + ":" ;
                                GET gofield[fieldcnt] PICTURE "@!12"
                        READ 
           
                        IF gofield[fieldcnt] = SPACE(12)  
                                getflds = .F. 
                                EXIT  
                        ENDIF
           
                        chk = RTRIM(gofield[fieldcnt])
           
                        IF TYPE(chk) <> "C"  
                                SET COLOR TO R+/  
                                ?? CHR(7) 
                                @ 23,1 SAY "The field you entered is
not in the file or not a character field"
                                DO WHILE INKEY() = 0  
                                ENDDO 
                                @ 23,1 SAY SPACE(78)  
                        ELSE 
                                chk2 = 0  
                                okay = .T.
                                DO WHILE chk2 < (fieldcnt - 1)
                                        chk2 = chk2 + 1
                                        IF RTRIM(gofield[chk2]) = chk
                                           okay = .F. 
                                        ENDIF  
                                ENDDO 
        
   
                                IF .NOT. okay 
                                        SET COLOR TO R+/
                                        ?? CHR(7)  
                                        @ 23,1 SAY "You already
entered that field"
                                        DO WHILE INKEY() = 0   
                                        ENDDO  
                                        @ 23,1 SAY SPACE(70)   
                                ELSE  
                                        EXIT   
                                ENDIF 
                        ENDIF
                ENDDO  

                STORE RTRIM(gofield[fieldcnt]) TO gofield[fieldcnt]  
        ENDDO  
           
        *--- Allow user the ability to scroll through Fields entered.
        *--- Assign an Order of Occurrence for each field selected.
        @ 7,1 CLEAR TO 9,79
        getflds = .T.  
        atfield = 1
        DO WHILE getflds   
                SET COLOR TO BG/   
                @ 8,32 TO 14,48 DOUBLE 
                scroll = 0 
                DO WHILE scroll < 5
                        scroll = scroll + 1  
                        IF scroll = 1
                                SET COLOR TO GR+/ 
                        ELSE 
                                SET COLOR TO W/   
                        ENDIF
           
                        IF atfield + scroll <= fieldcnt  
                                @ (8 + scroll),34 SAY
gofield[((atfield + scroll) - 1)] +;
                                 SPACE(12 - LEN(gofield[((atfield +
scroll) - 1)]))
                        ELSE 
                                @ (8 + scroll),34 SAY SPACE(12)   
                        ENDIF
                ENDDO  

                waiting = 0
                DO WHILE waiting = 0   
                        waiting = INKEY()
                ENDDO  
           
                DO CASE
                        CASE waiting = 24
                                atfield = atfield + 1 
                                IF atfield > fieldcnt 
                                        atfield = fieldcnt 
                                ENDIF 
                        CASE waiting = 5 
                                atfield = atfield - 1 
                                IF atfield < 1
                                        atfield = 1
                                ENDIF 
                        CASE waiting = 13
                                SET COLOR TO W+/
                                @ 23,1 SAY "Replace with string
number" GET numspac[atfield] PICTURE "999" 
                                READ  
                                @ 23,1 SAY SPACE(70)  
                        CASE waiting = 27
                                yesno = " "   
                                DO WHILE .NOT. yesno $ "GQ"   
                                        yesno = " "
                                        SET COLOR TO W/
                                        @ 23,1 SAY "Do you want to Go
on or Quit?" GET yesno PICTURE "!"   
                                        SET COLOR TO W+/   
                                        @ 23,16 SAY "G"
                                        @ 23,25 SAY "Q"  
                                        SET CONFIRM OFF
                                        READ   
                                        SET CONFIRM ON 
                                ENDDO 
           
                                @ 23,1 SAY SPACE(70)  
                                IF yesno = "Q"
                                        looping = .F.  
                                ENDIF 
                                EXIT  
                ENDCASE
        ENDDO  
                                          
        IF .NOT. looping   
                LOOP   
        ENDIF  
          
        recs = RECCOUNT()  
        @ 7,1 CLEAR TO 23,79   
        SET COLOR TO R/
        @ 23,1 SAY "Records to Process: "  
        @ 23,53 SAY "Current Record:"  
        SET COLOR TO R+/   
        @ 23,21 SAY LTRIM(STR(recs))   
        GO TOP 
                                          
        *--- Determine each occurrence of a Space in each record.
        *--- Store the character string preceding the Space into its
assigned field.
        fieldcnt = fieldcnt - 1
        DO WHILE .NOT. EOF()   
                SET COLOR TO R+/   
                @ 23,69 SAY LTRIM(STR(RECNO()))
                mast = RTRIM(&master) + " "
                poscnt = LEN(mast) 
                                          
                chk = 0
                DO WHILE chk < fieldcnt
                        chk = chk + 1
                                        
                        STORE 0 TO pos,pos2,chk2,chk3
                        DO WHILE .T. 
                                IF (chk2 >= numspac[chk]) 
                                        EXIT   
                                ENDIF 
                                chk2 = chk2 + 1   
                                this len = 0  
                                DO WHILE chk3 < poscnt
                                        chk3 = chk3 + 1
                                        this len = this len + 1
                                        IF SUBSTR(mast,chk3,1) = "
"   
                                           pos = pos2 + 1 
                                           pos2 = chk3
                                           EXIT   
                                        ENDIF  
                                ENDDO 
                        ENDDO
                                         
                        nowfield = gofield[chk]  
                        REPLACE &nowfield WITH SUBSTR(mast,pos,this
len) 
                ENDDO  
                SKIP 1 
        ENDDO  
ENDDO  
                                          
CLOSE ALL  
SET COLOR TO   
SET ESCAPE ON
SET BELL ON
SET STATUS ON  
SET CONFIRM OFF
SET TALK ON
RETURN 
           
* EOF: dSPLIT.PRG

