Lin, In reviewing my code there should be and "ENDIF" after the "ENDSW"
Jim Bentley --- Gary Wendike <[EMAIL PROTECTED]> wrote: > Lin, I use RBase for everything at the office. However, I use > Excel to help with viewing data as well, espcecially if it is > a one time shot to look at the data. Sometimes manual > manipulation is easier than getting the code just right for > everything. Since this is going to be cumbersome task at > best, export the data to a spreadsheet. There is a conversion > process that allows the parsing of the data into separate > fields. It is under Data - Text to Columns. You can specify > that the key for separation is a space, coma, tab etc. This > will place the information into as many columns as you have > information to fill. > > I realize this may be more cumbersome, but it will allow you > to more view the data as you are parsing the information and > make changes as you view the data. Once you have completed > the task, the reload RBase with the data. > > You may have to sort a couple of times to separate those with > middle names vs those without middle names. You should be > able to come up with information in very short order. > > Just another way of looking at it... > > Gary > > > ----- Original Message ---- > From: Lin MacDonald <[EMAIL PROTECTED]> > To: RBASE-L Mailing List <[email protected]> > Sent: Monday, January 14, 2008 5:59:53 PM > Subject: [RBASE-L] - Re: pulling a text column apart > > Nice! > > Lin > > On Mon, 14 Jan 2008 08:24:57 -0800 (PST), "James Bentley" > <[EMAIL PROTECTED]> said: > > Lin, > > > > The key to solving your problem is the following function: > > "ITEMCNT > > Top Previous Next > > > > (ITEMCNT('Text String')) > > > > Use to count the number of items in a text string separated > by > > current comma delimiter. > > > > In the following example, the value of vItems is 3. > > > > SET VAR vItems = (ITEMCNT('a,b,c')) " > > > > In essence you load the 8,000 items to a temp table with the > > following fields: > > > > 1 original_field > > 2 first_name > > 3 mid_name > > 4 last_name > > one field for any other items in in original_field > > 5 FieldCnt=(ITEMCNT(original_field)) integer > > > > Define a command file as follows: > > set var voriginal_field TEXT > > SET VAR vfieldcnt integer > > SET VAR iv1 INTEGER -- set up indicator variables since > > original_field could be null > > set var ... for other fields > > DROP CURSOR C1 > > DECLARE C1 CURSOR FOR SELECT original_field, fieldcnt from > > temptable > > OPEN C1 > > FETCH C1 INTO voriginal_field INDICATOR iv1, vFieldcnt iv2 > > WHILE SQLCODE = 0 THEN > > IF iv1 <> -1 THEN -- we have values in original_field > > SWITCH (.vfieldcnt) > > CASE 3 > > SET VAR vfirstname = (ssub(voriginal_field,?)) > > set var other field > > UPDATE temptable set firstname=.vfirstname, ... > > WHERE CURRENT OF C1 > > BREAK > > CASE 2 > > SET VAR .... > > UPDATE > > BREAK > > DEFAULT > > BREAK > > ENDSW > > FETCH C1 INTO voriginal_field INDICATOR iv1, vFieldcnt iv2 > > ENDW > > DROP CURSOR C1 > > RETURN > > You will need to flesh out the above code. It should give > you a > > good start. For the records without a comma to distinguish > > fields you may have to add code to insert commas to separate > > fields. > > > > Jim Bentley > > > > --- Lin MacDonald <[EMAIL PROTECTED]> wrote: > > > > > There are about 8000 records in that table, all different, > so > > > doing it > > > by hand is a bit much. They'd have to cart me off to a > looney > > > bin > > > afterwards! > > > > > > Lin > > > > > > On Sun, 13 Jan 2008 09:55:07 -0500, "MikeB" > > > <[EMAIL PROTECTED]> said: > > > > How many rows of data? If it is small, I would just > write > > > the names to a > > > > text > > > > file and massage it there to normalize it a bit with > > > appropriate > > > > delimiters > > > > then load as ascii. > > > > > > > > If there were only a few offenders to a particular > > > separation then by all > > > > means > > > > put it into another table using the string manipulation > > > functions and > > > > finish > > > > the offenders by hand. Thing is, if you spend a great > deal > > > of time > > > > trying to > > > > figure out a way to do it programmatically for a one > shot > > > deal, sometimes > > > > you > > > > waste time on that instead of just doing the grunt > work... > > > > > > > > > > > > > > > > > > > > ----- Original Message ----- > > > > From: "Lin MacDonald" <[EMAIL PROTECTED]> > > > > To: "RBASE-L Mailing List" <[email protected]> > > > > Sent: Sunday, January 13, 2008 9:36 AM > > > > Subject: [RBASE-L] - pulling a text column apart > > > > > > > > > > > > > Sorry, another Newbie question -- > > > > > As I redesign my Database and application, I want to > > > correct some > > > > > design problems. One of them is that in my Supplier > > > table, I > > > > > have one column for Contacts. I'd like to move that > > > column to > > > > > another table with multiple fields, such as First > Name, > > > Last > > > > > Name, etc. The problem is that the data in that field > is > > > not > > > > > consistant. It could contain the following: > > > > > Null > > > > > FName > > > > > FName LName > > > > > FName1, FName2 > > > > > FName1, FName2, FName3 > > > > > So, what would be the best way to break it apart? > Should > > > I use a > > > > > temporary table? I thought of using SSUB, but that > > > wouldn't work > > > > > for the ones that had a First and Last name in the > field > > > with > > > > > just a space in between. > > > > > thanks, > > > > > Lin MacDonald > > > > > > > > > > > > > > > > > > > > > > > > > > > > Jim Bentley > > American Celiac Society > > [EMAIL PROTECTED] > > tel: 1-504-737-3293 > > > > > > > ____________________________________________________________________________________ > > Be a better friend, newshound, and > > know-it-all with Yahoo! Mobile. Try it now. > === message truncated === Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
