Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN9006.ZIP
Filename : SPLITT.TXT

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