Address Seperation Question

[Tim Morrison]

I have a database with a field that contains:

<Company Name>
<Address>
<City> <State> <Zip>
<Phone>

in one field.

I also have individual fields for each of those entities.

What is the best way to get data from the one field to the individual fields.

TIA

--
Tim Morrison

--------------------------------------------------------------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
[url]http://www.vehiclewebstudio.com[/url]

[Lee Smith]

In reply to Xogdb.597542$YN5.437079@sccrnsc01 on 9/27/03 6:37 AM by Tim
Morrison [email]sales@kjmsoftware.com[/email]:

Hi Tim,

What you are trying to do is called Parse or Parsing text, sometimes
referred to as extracting text. There are sample files on how to do this
available at [url]www.fmfiles.com[/url] and [url]www.databasepros.com[/url]

Basically you have to look at the data and look for a pattern that will
allow you to do this consistently.

Some questions:

Are each name/address exactly as you have shown them? In other words, do the
really have the "<" and ">" preceding and following each line?

<Company Name>
<Address>
<City> <State> <Zip>
<Phone>

Or maybe they have Field Identifiers such as:

Name: Joe Doe
Company: XYZ Co.
Address: 123 A Street
etc.

How about consistency. Are there only four lines for each address? No second
address such as Street and Post Office Box. How about Contact Name for some,
second phone numbers, etc.

You might get a better answer if you pasted in a few examples

HTH

Lee

> I have a database with a field that contains:
>
> <Company Name>
> <Address>
> <City> <State> <Zip>
> <Phone>
>
> in one field.
>
> I also have individual fields for each of those entities.
>
> What is the best way to get data from the one field to the individual fields.
>
> TIA

[Tim Morrison]

Thank you for the response.

1. Addresses do not have < >.
2. Company is ALWAYS on first line.
3. Addreess is ALWAYS on second line.
4. Address is ALWAYS 1 line.
5. City State Zip is ALWAYS on third line.
6. Phone Number is NOT always present, but if it is present, it is always on
the 4th line.

An example is:

KJM Software
P.O. Box 1234
Independence IA 50644
(319) 334-3667

regards,

Tim Morrison


"Lee Smith" <lee@pacific.net> wrote in message
news:BB9AE798.2E5C8%lee@pacific.net...

> In reply to Xogdb.597542$YN5.437079@sccrnsc01 on 9/27/03 6:37 AM by Tim
> Morrison [email]sales@kjmsoftware.com[/email]:
>
> Hi Tim,
>
> What you are trying to do is called Parse or Parsing text, sometimes
> referred to as extracting text. There are sample files on how to do this
> available at [url]www.fmfiles.com[/url] and [url]www.databasepros.com[/url]
>
> Basically you have to look at the data and look for a pattern that will
> allow you to do this consistently.
>
> Some questions:
>
> Are each name/address exactly as you have shown them? In other words, do

the

> really have the "<" and ">" preceding and following each line?
>
> <Company Name>
> <Address>
> <City> <State> <Zip>
> <Phone>
>
> Or maybe they have Field Identifiers such as:
>
> Name: Joe Doe
> Company: XYZ Co.
> Address: 123 A Street
> etc.
>
> How about consistency. Are there only four lines for each address? No

second

> address such as Street and Post Office Box. How about Contact Name for

some,

> second phone numbers, etc.
>
> You might get a better answer if you pasted in a few examples
>
> HTH
>
> Lee
>
>
>

> > I have a database with a field that contains:
> >
> > <Company Name>
> > <Address>
> > <City> <State> <Zip>
> > <Phone>
> >
> > in one field.
> >
> > I also have individual fields for each of those entities.
> >
> > What is the best way to get data from the one field to the individual

fields.

> >
> > TIA

>

[Lee Smith]

In reply to Jbhdb.597847$YN5.437849@sccrnsc01 on 9/27/03 7:32 AM by Tim
Morrison [email]sales@kjmsoftware.com[/email]:

> An example is:
>
> KJM Software
> P.O. Box 1234
> Independence IA 50644
> (319) 334-3667
>

I want to verify that there are no commas after the city and no period after
the state?

Lee

[Howard Schlossberg]

Calcs below assume the starting field is called FullInfo. The "P"
represents a return character within quotes. Calcs are approximate;
they've been tested a little, but should give you a good start even if
I've missed a parenthesis or something.

Company = Left(FullInfo, Position(FullInfo, "P", 1, 1)-1)

Address = Middle(FullInfo, Position(FullInfo, "P", 1, 1)+1,
Position(FullInfo, "P", 1, 2)-Position(FullInfo, "P", 1, 1)-1)

City = LeftWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-2)

State = MiddleWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-1, 1)

Zip = RightWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1), 1)

Phone = Middle(FullInfo, Position(FullInfo, "P", 1, 3)+1,
Position(FullInfo & "P", "P", 1, 4)-Position(FullInfo, "P", 1, 3)-1)

Tim Morrison wrote:

> 1. Addresses do not have < >.
> 2. Company is ALWAYS on first line.
> 3. Addreess is ALWAYS on second line.
> 4. Address is ALWAYS 1 line.
> 5. City State Zip is ALWAYS on third line.
> 6. Phone Number is NOT always present, but if it is present, it is always on
> the 4th line.
>
> An example is:
>
> KJM Software
> P.O. Box 1234
> Independence IA 50644
> (319) 334-3667

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California
Associate Member, FileMaker Solutions Alliance

[Lee Smith]

In reply to [email]vnbeh82mpc4a15@corp.supernews.com[/email] on 9/27/03 9:26 AM by Howard
Schlossberg [email]howard@antispahm.fmprosolutions.com[/email]:

Very Good Howard,

Two replacements as you stated and they can be pasted directly into their
respective fields.

Lee

> Calcs below assume the starting field is called FullInfo. The "P"
> represents a return character within quotes. Calcs are approximate;
> they've been tested a little, but should give you a good start even if
> I've missed a parenthesis or something.
>
> Company = Left(FullInfo, Position(FullInfo, "P", 1, 1)-1)
>
> Address = Middle(FullInfo, Position(FullInfo, "P", 1, 1)+1,
> Position(FullInfo, "P", 1, 2)-Position(FullInfo, "P", 1, 1)-1)
>
> City = LeftWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-2)
>
> State = MiddleWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-1, 1)
>
> Zip = RightWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1), 1)
>
> Phone = Middle(FullInfo, Position(FullInfo, "P", 1, 3)+1,
> Position(FullInfo & "P", "P", 1, 4)-Position(FullInfo, "P", 1, 3)-1)
>
> Tim Morrison wrote:

>> 1. Addresses do not have < >.
>> 2. Company is ALWAYS on first line.
>> 3. Addreess is ALWAYS on second line.
>> 4. Address is ALWAYS 1 line.
>> 5. City State Zip is ALWAYS on third line.
>> 6. Phone Number is NOT always present, but if it is present, it is always on
>> the 4th line.
>>
>> An example is:
>>
>> KJM Software
>> P.O. Box 1234
>> Independence IA 50644
>> (319) 334-3667

>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg (818) 883-2846
> FM Pro Solutions Los Angeles, California
> Associate Member, FileMaker Solutions Alliance
>

[Tim Morrison]

Verified.....

Tim

"Lee Smith" <lee@pacific.net> wrote in message
news:BB9B0506.2E5D3%lee@pacific.net...

> In reply to Jbhdb.597847$YN5.437849@sccrnsc01 on 9/27/03 7:32 AM by Tim
> Morrison [email]sales@kjmsoftware.com[/email]:
>

> > An example is:
> >
> > KJM Software
> > P.O. Box 1234
> > Independence IA 50644
> > (319) 334-3667
> >

>
> I want to verify that there are no commas after the city and no period

after

> the state?
>
> Lee
>

[Tim Morrison]

Thank you very much.... I will work on this.

Tim

"Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in message
news:vnbeh82mpc4a15@corp.supernews.com...

> Calcs below assume the starting field is called FullInfo. The "P"
> represents a return character within quotes. Calcs are approximate;
> they've been tested a little, but should give you a good start even if
> I've missed a parenthesis or something.
>
> Company = Left(FullInfo, Position(FullInfo, "P", 1, 1)-1)
>
> Address = Middle(FullInfo, Position(FullInfo, "P", 1, 1)+1,
> Position(FullInfo, "P", 1, 2)-Position(FullInfo, "P", 1, 1)-1)
>
> City = LeftWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-2)
>
> State = MiddleWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-1,

1)

>
> Zip = RightWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1), 1)
>
> Phone = Middle(FullInfo, Position(FullInfo, "P", 1, 3)+1,
> Position(FullInfo & "P", "P", 1, 4)-Position(FullInfo, "P", 1, 3)-1)
>
> Tim Morrison wrote:

> > 1. Addresses do not have < >.
> > 2. Company is ALWAYS on first line.
> > 3. Addreess is ALWAYS on second line.
> > 4. Address is ALWAYS 1 line.
> > 5. City State Zip is ALWAYS on third line.
> > 6. Phone Number is NOT always present, but if it is present, it is

always on

> > the 4th line.
> >
> > An example is:
> >
> > KJM Software
> > P.O. Box 1234
> > Independence IA 50644
> > (319) 334-3667

>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg (818) 883-2846
> FM Pro Solutions Los Angeles, California
> Associate Member, FileMaker Solutions Alliance
>

[Tim Morrison]

The only problem I have come across so far is when the zip is formatted as
ZIP+4. I will work on this.

Tim

"Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in message
news:vnbeh82mpc4a15@corp.supernews.com...

> Calcs below assume the starting field is called FullInfo. The "P"
> represents a return character within quotes. Calcs are approximate;
> they've been tested a little, but should give you a good start even if
> I've missed a parenthesis or something.
>
> Company = Left(FullInfo, Position(FullInfo, "P", 1, 1)-1)
>
> Address = Middle(FullInfo, Position(FullInfo, "P", 1, 1)+1,
> Position(FullInfo, "P", 1, 2)-Position(FullInfo, "P", 1, 1)-1)
>
> City = LeftWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-2)
>
> State = MiddleWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-1,

1)

>
> Zip = RightWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1), 1)
>
> Phone = Middle(FullInfo, Position(FullInfo, "P", 1, 3)+1,
> Position(FullInfo & "P", "P", 1, 4)-Position(FullInfo, "P", 1, 3)-1)
>
> Tim Morrison wrote:

> > 1. Addresses do not have < >.
> > 2. Company is ALWAYS on first line.
> > 3. Addreess is ALWAYS on second line.
> > 4. Address is ALWAYS 1 line.
> > 5. City State Zip is ALWAYS on third line.
> > 6. Phone Number is NOT always present, but if it is present, it is

always on

> > the 4th line.
> >
> > An example is:
> >
> > KJM Software
> > P.O. Box 1234
> > Independence IA 50644
> > (319) 334-3667

>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg (818) 883-2846
> FM Pro Solutions Los Angeles, California
> Associate Member, FileMaker Solutions Alliance
>

[Howard Schlossberg]

You hadn't mentioned the possibility of zip+4. I'll let you work on it
a little...but let us know if you have troubles with it.

Tim Morrison wrote:

> The only problem I have come across so far is when the zip is formatted as
> ZIP+4. I will work on this.
>
> Tim
>
> "Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in message
> news:vnbeh82mpc4a15@corp.supernews.com...
>

>>Calcs below assume the starting field is called FullInfo. The "P"
>>represents a return character within quotes. Calcs are approximate;
>>they've been tested a little, but should give you a good start even if
>>I've missed a parenthesis or something.
>>
>>Company = Left(FullInfo, Position(FullInfo, "P", 1, 1)-1)
>>
>>Address = Middle(FullInfo, Position(FullInfo, "P", 1, 1)+1,
>>Position(FullInfo, "P", 1, 2)-Position(FullInfo, "P", 1, 1)-1)
>>
>>City = LeftWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
>>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
>>WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
>>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-2)
>>
>>State = MiddleWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
>>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
>>WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
>>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-1,

>
> 1)
>

>>Zip = RightWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
>>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1), 1)
>>
>>Phone = Middle(FullInfo, Position(FullInfo, "P", 1, 3)+1,
>>Position(FullInfo & "P", "P", 1, 4)-Position(FullInfo, "P", 1, 3)-1)
>>
>>Tim Morrison wrote:
>>

>>>1. Addresses do not have < >.
>>>2. Company is ALWAYS on first line.
>>>3. Addreess is ALWAYS on second line.
>>>4. Address is ALWAYS 1 line.
>>>5. City State Zip is ALWAYS on third line.
>>>6. Phone Number is NOT always present, but if it is present, it is

>
> always on
>

>>>the 4th line.
>>>
>>>An example is:
>>>
>>>KJM Software
>>>P.O. Box 1234
>>>Independence IA 50644
>>>(319) 334-3667

>>
>>
>>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>Howard Schlossberg (818) 883-2846
>>FM Pro Solutions Los Angeles, California
>>Associate Member, FileMaker Solutions Alliance
>>

>
>
>

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California
Associate Member, FileMaker Solutions Alliance

[Tim Morrison]

I appreciate the help.... Its been awhile since ive worked with FileMaker...
I do mostly Clarion development now...

Tim


"Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in message
news:vnbhpjo5892213@corp.supernews.com...

> You hadn't mentioned the possibility of zip+4. I'll let you work on it
> a little...but let us know if you have troubles with it.
>
> Tim Morrison wrote:

> > The only problem I have come across so far is when the zip is formatted

as

> > ZIP+4. I will work on this.
> >
> > Tim
> >
> > "Howard Schlossberg" <howard@antispahm.fmprosolutions.com> wrote in

message

> > news:vnbeh82mpc4a15@corp.supernews.com...
> >

> >>Calcs below assume the starting field is called FullInfo. The "P"
> >>represents a return character within quotes. Calcs are approximate;
> >>they've been tested a little, but should give you a good start even if
> >>I've missed a parenthesis or something.
> >>
> >>Company = Left(FullInfo, Position(FullInfo, "P", 1, 1)-1)
> >>
> >>Address = Middle(FullInfo, Position(FullInfo, "P", 1, 1)+1,
> >>Position(FullInfo, "P", 1, 2)-Position(FullInfo, "P", 1, 1)-1)
> >>
> >>City = LeftWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> >>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> >>WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> >>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-2)
> >>
> >>State = MiddleWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> >>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1),
> >>WordCount(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> >>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1))-1,

> >
> > 1)
> >

> >>Zip = RightWords(Middle(FullInfo, Position(FullInfo, "P", 1, 2)+1,
> >>Position(FullInfo & "P", "P", 1, 3)-Position(FullInfo, "P", 1, 2)-1), 1)
> >>
> >>Phone = Middle(FullInfo, Position(FullInfo, "P", 1, 3)+1,
> >>Position(FullInfo & "P", "P", 1, 4)-Position(FullInfo, "P", 1, 3)-1)
> >>
> >>Tim Morrison wrote:
> >>
> >>>1. Addresses do not have < >.
> >>>2. Company is ALWAYS on first line.
> >>>3. Addreess is ALWAYS on second line.
> >>>4. Address is ALWAYS 1 line.
> >>>5. City State Zip is ALWAYS on third line.
> >>>6. Phone Number is NOT always present, but if it is present, it is

> >
> > always on
> >

> >>>the 4th line.
> >>>
> >>>An example is:
> >>>
> >>>KJM Software
> >>>P.O. Box 1234
> >>>Independence IA 50644
> >>>(319) 334-3667
> >>
> >>
> >>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >>Howard Schlossberg (818) 883-2846
> >>FM Pro Solutions Los Angeles, California
> >>Associate Member, FileMaker Solutions Alliance
> >>

> >
> >
> >

>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg (818) 883-2846
> FM Pro Solutions Los Angeles, California
> Associate Member, FileMaker Solutions Alliance
>