parsing and restructuring a field in a table

[drummer721]

I have a data file I want to use that has Name field with the data in the format of

LastName/ Firstname

I need it in the format

FirstName Lastname.

I have not been able to find any examples to write a short Objectpal script to do this. Can someone please help?

Thanks

 

[TonyMcGuire]

The below will NOT work if the field you are working on is the primary key field. You must first remove the key if you wish to do this, unless assigning the data to a new field.

var
  arField  array[] string
  tc       tcursor
endvar
tc.open(":alias:tablename.db")
tc.edit()
scan tc :
  breakapart(tc."fieldname",arField,"/")
  tc."fieldname=arField[2]+" "+arField[1]
  if not tc.unlockrecord() then
    ; handle errors
  endif
endscan
tc.endedit()
tc.close()

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[drummer721]

Tony,

Thanks for the quick reply. I took what you showed me and modified as follows:

var
arField Array[] string
tc tcursor
endvar



tc.open("Mail2tmp.dbf")
tc.edit()

scan tc:
tc."FIRSTNAME".breakapart(arField,"/")
tc."FIRSTNAME"=arField[2]+" "+arField[1]


endscan
tc.endedit()
tc.close()

However I now get an error when I try to run it:

"The specified array index is out of bounds. The index is 2 and the array limit is 1.

Do you know what I'm missing here?


Thanks

Dennis

 

[TonyMcGuire]

"Do you know what I'm missing here?"

Yes, a slash mark in the data. At LEAST one record, if not more, doesn't have a slash "/" separating the last and first names.

The data must either be ABSOLUTELY consistent, or you must adjust the code to the data.

You might want to put a test for

if arField.size()<2 then
msgstop("Error",tc.recno())
quitloop
endif

This will display records that don't have a slash for the breakapart() to operate on.


Also, you'll get your error if you try to run the routine more than once, since [at least part of] the data will already have had the slash removed.




Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[drummer721]

Tony,

My deepest thanks. I took what you wrote and again modified it as follows:


tc."FIRSTNAME".breakapart(arField,"/")
if arField.size()>1 then
tc."LASTNAME"=arField[1]
tc."FIRSTNAME"=arField[2]
else

endif

It ran just fine. Thank you, thank you.!!

 

[TonyMcGuire]

OK. But you won't have 'fixed' the fields with

lastname firstname

structure!

You COULD do

tc."FIRSTNAME".breakapart(arField,"/ "); note space added, field will be broken up on slash '/' as well as space ' '.

But then you'd likely have a mismash since fields with a space would also be parsed at the same time.

Check your data carefully! I'm betting you don't have 100% of what you need.



Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[drummer721]

Your statement is basically true.
AND
When I looked through the data, I do have a number of records where there is no "/". However, all those records have the info in the format of FirstName LastName. For purposes of printing labels via Paradox, that works out ok. I formatted the labels as

Firstname LastName
Address1
Address2
City, State Zip

It printed out just the way I needed it.

This is for a mass mailing ad campaign, so if a few are a little messy, NBD. Thanks for the follow up though.

Dennis