How to seperate data in a field

Hello from a rainy Holland,

We are now importing order and client data as TAB delimited text from 5
different sources into our own DBASE files using mister Wingerters program
Rwappend.prg. It works perfect and it saves us loads of time and errors,
compared with retyping the data. In most cases we can import the data without
editing. 
But the sources use all different structures for their dabases. For instances,
our file contains  3 address fields. But some sources deliver the data combined
into one address field. If the originally entered data contains more lines then
one, the following line is separated by two semi-colons (;;) from the previous
line. Like this:
Address1;; adres2;; adres3
After importing, we now edit these lines manully into our own 3 addressfields.

Is there way or a command in dBase Plus to automatically copy the data after the
semi-colons into a different dBase field and then remove all text in the
original line after the semi-colons and remove also the semi-colons?

I hope somebody knows a way to help on the way again.

With kind regards from Holland.

Kees

In article <YEgtS#XlIHA.468@news-server>, info@erarecords.com says...
> 
> Address1;; adres2;; adres3
> Is there way to automatically copy the data after the
> semi-colons into a different dBASE field

In the code below, simply replace "MyTable" with the name of your
table, 
"FirstField", "SecondField" and "ThirdField" with
the appropriate field 
names:

q = new query("select * from MyTable")
f = q.rowset.fields
do
   c = f.["FirstField"].value.rightTrim()
   f["ThirdField"].value = ;
       c.substring(c.lastIndexOf(";")+1,c.length)
   f["SecondField"] = ;
       c.substring(c.IndexOf(";")+2,c.lastIndexOf(";;"))
   f["FirstField"] = c.substring(0,c.IndexOf(";"))
until NOT q.rowset.next()
q.active = false
q = null

Jean-Pierre Martel, editor
The dBASE Developers Bulletin

Jean-Pierre Martel Wrote:
> 
> In the code below, simply replace "MyTable" with the name of your
table, 
> "FirstField", "SecondField" and "ThirdField"
with the appropriate field 
> names:
> 
> q = new query("select * from MyTable")
> f = q.rowset.fields
> do
Bonjour monsieur Martell,

Wow that is fast.
I copied the program, replaced the field and file name and then I get an error
saying:
Error:  Unallowed phrase/keyword in command: 
.["ADRES"].value.rightTrim()

So I removed the dot  between f.["ADRES" ETC. and then i got the 
error Error:  Property is read only.
In line:       
c.substring(c.IndexOf(";")+2,c.lastIndexOf(";;"))

And that is were I got stucked now.
But it looks good.
So, what to do now?
Thanks
Kees
P.S. I loved your film about Holland!!

In article <a2mACYZlIHA.1880@news-server>, info@erarecords.com says...
> 
> So I removed the dot  between f.["ADRES" ...

Sorry, that was my error.

> and then i got the 
> error Error:  Property is read only.
> In line:       
c.substring(c.IndexOf(";")+2,c.lastIndexOf(";;"))

Opps! I forgot to add ".value".

« f["SecondField"] » should have been «
f["SecondField"].VALUE »

This is (hopefully) the correct version:

q = new query("select * from MyTable")
f = q.rowset.fields
do
   c = f["FirstField"].value.rightTrim()
   f["ThirdField"].value = ;
       c.substring(c.lastIndexOf(";")+1,c.length)
   f["SecondField"].value = ;
       c.substring(c.IndexOf(";")+2,c.lastIndexOf(";;"))
   f["FirstField"].value = c.substring(0,c.IndexOf(";"))
until NOT q.rowset.next()
q.active = false
q = null

Sorry for the untested code. This one should be better.

Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Blue Star dBASE Plus Core Concepts Graduate

> P.S. I loved your film about Holland!!

Jean-Pierre Martel Wrote:

>> Opps! I forgot to add ".value".
> 
> « f["SecondField"] » should have been «
f["SecondField"].VALUE »
> 
> This is (hopefully) the correct version:
> 
> q = new query("select * from MyTable")
> f = q.rowset.fields
> do
>    c = f["FirstField"].value.rightTrim()
>    f["ThirdField"].value = ;
>        c.substring(c.lastIndexOf(";")+1,c.length)
>    f["SecondField"].value = ;
>       
c.substring(c.IndexOf(";")+2,c.lastIndexOf(";;"))
>    f["FirstField"].value =
c.substring(0,c.IndexOf(";"))
> until NOT q.rowset.next()
> q.active = false
> q = null

Hello mister Martel,

Thanks for you additions. I have changed it and it works perfect.
There is just one thing I have changed. Your program assumes that there are
always 3 lines, separated by the semi-colons. If there are only 2 lines, the
last address fields ends up with 2 ;; in it.
As about 99 % percent of all the lines only contain 2 addresslines, I have
altered it this way:

q = new query("select * from TMPGEM.DBF")
f = q.rowset.fields
do
   c = f["ADRES"].value.rightTrim()
   f["ADRES2"].value = ;
       c.substring(c.lastIndexOf(";")+1,c.length)
   f["ADRES"].value = c.substring(0,c.IndexOf(";"))
until NOT q.rowset.next()
q.active = false
q = null
And for my purpposes that is great and more then enough.
Thank you again very much for your help.
With kind regards from Holland were it rains for 3 days now.

Kees Blokker
P.S. your name suggests French or French Canadian based roots. Is that so?