yellow pages address form into separate fields from one field in filemaker

[DAVE/]

Hello

From the internet I have an address like this

Spring Farm Cares

Address: 3364 State Route 12, Clinton, NY 13323

Phone: (315) 737-0829

They are in the yellow pages so they are always this way

I want to take out the word "Address:" and "phone" and put

Spring Farm Cares into a field "Company"

3364 State Route 12 into a field called "Address"

Clinton into a field called "City"

NY into a field called "STATE"

and (315) 737-0829 into a field called phone

They are always in this format.

But my problem is that they have different amount of words before the comma in the address field.

Thanks

Dave

[comment]

Try something like:

Address =

Let ( [

line = GetValue ( text ; 2 ) ;

end = Position ( line ; "," ; 1 ; 1 )

] ;

Middle ( line ; 10 ; end - 10 )

)

City =

Let ( [

line = GetValue ( text ; 2 ) ;

start = Position ( line ; "," ; 1 ; 1 ) + 2 ;

end = Position ( line ; "," ; 1 ; 2 )

] ;

Middle ( line ; start ; end - start )

)

State =

Let ( [

line = GetValue ( text ; 2 ) ;

countWords = WordCount ( line )

] ;

MiddleWords ( line ; countWords - 1 ; 1 )

)

Edited June 4, 2007 by Guest
Oops

[Raybaudi]

Company:

GetValue ( textFromInternet ; 1 )

Address:

Trim ( GetValue ( Substitute ( GetValue ( textFromInternet ; 2 ) ; [ "," ; ¶ ] ; [ ":" ; ¶ ] ) ; 2 ) )

City:

Trim ( GetValue ( Substitute ( GetValue ( textFromInternet ; 2 ) ; [ "," ; ¶ ] ; [ ":" ; ¶ ] ) ; 3 ) )

State:

LeftWords ( Trim ( GetValue ( Substitute ( GetValue ( textFromInternet ; 2 ) ; [ "," ; ¶ ] ; [ ":" ; ¶ ] ) ; 4 ) ) ; 1 )

Phone:

Trim ( GetValue ( Substitute ( GetValue ( textFromInternet ; 3 ) ; ":" ; ¶ ) ; 2 ) )

[DAVE/]

Thank you both very much .

I still have black space I need to get rid of to the left of the address

and I need to take the "(" and ")" out of the phone number and put in a "-" after the area code.

Thanks

Dave

[Raybaudi]

The Trim function has to eliminate those spaces !

Phone:

TrimAll ( GetValue ( Substitute ( GetValue ( textFromInternet ; 3 ) ; [ ":" ; ¶ ] ; [ "(" ; "" ] ; [ ")" ; "-" ] ) ; 2 ) ; 3 ; 3 )

[DAVE/]

I need to write a script to get a different configuration.

This is for the AOL yellowpages

Oneida Suzuki

315-363-3872 • 434 Lenox Ave, Oneida,NY

There is no zip in this one but

the company is the first line

and the phone is the second line.

How do I get the phone number from the second line.

Thanks

Dave

[Raybaudi]

LeftWords ( GetValue ( textFromInternet ; 2 ) ; 1 )

[hartmut]

if you have an address like in the yellow pages of AOL how do you break it appart

Melodic Revolution

315-853-2320 34 College St # 101, Clinton, NY 13323

Melodic Revolution needs to go to the field "Company"

(there is a space in the second line which is empty)

the third line is the "Phone" then the "address" "city" "State" "Zip"

this is different from the first example there is a space between lines.

[Raybaudi]

If this is the situation

LeftWords ( GetValue ( textFromInternet ; 3 ) ; 1 )

returns the phone number

[hartmut]

I don't know how to do the rest of the above address.I wish I could find some in depth tutorials about text functions because I really don't understand the scripting.

Melodic Revolution

315-853-2320 34 College St # 101, Clinton, NY 13323

In this address I need to separate the address into separate fields.

This in an AOL yellow page address.

There is a missing line in between the company and the address.

I basically have these questions. about text ( along with figuring this thing out.

1. how does one remove a specific word from a sentence.

2.how do I deal with a missing line as above.

3. How do I address a specific line. ( how do I scropt to get to it.

4. how do I replace one word with another word.

But I need training in these areas

I very much need to know how to break apart the above address and send it to the appropriate fields.

Thanks

Dave

[comment]

I don't think it's possible to give a general answer to your questions, other than "it depends". I would suggest you start by asking yourself another question: what is permanent here (and can therefore be used by the extracting algorithm as an orientation point), and what is variable?

IOW, imagine a very stupid, but reliable person having to do this. You need to give this person a set of rigid rules to follow, rules that will safely identify the correct part in ANY possible record. For example, in the original post the city is ALWAYS in the second line, it is ALWAYS surrounded by commas, and there are NEVER any other commas in the second line.

You should also get familiar with Filemaker's functions - there aren't that many of them.

[hartmut]

Thanks for the advice but I still need to send the components of this yellowpage from AOL to their respective fields.

I dont know how to do it.

Melodic Revolution

315-853-2320 34 College St # 101, Clinton, NY 13323

There is a space in between.

I appreciate everyones help.

After seeing it , I can compare the two calculations and maybe teach myself something with your help

Thanks

Dave

[hartmut]

Hello

In address below- how do I pull out the street address only

and send it to an address field. It is an AOL yellow page entry so the format is always the same.

Daves Market

315-853-2320 81 Maple St # 101, Canastota, NY 13032

Thanks

[mr_vodka]

Try something like this:

Let ( f = GetValue ( Substitute ( gtest; "¶¶"; "¶" ); 2 );

Middle ( f; Position ( f; " "; 1; 1 ) +1; Position ( f; ","; 1; 1 ) - Position ( f; " "; 1; 1 ) -1)

)

Edited June 7, 2007 by Guest
Typo

[hartmut]

Thank you

It works except I am getting the last integer from the phone number. How do you remove a word or number from a field with a text function.

Daves Market

315-853-2320 81 Maple St # 101, Canastota, NY 13032

From the address example I get

0 81 Maple St # 101

I would need to remove the 1st character

Thanks

Dave

[Genx]

Hartmut, would you please not double and triple post? These three topics are all significantly similar:

- Get Address to new field

- breaking up a text field into an address

- need to extract an address into parts

There are very few functions that you need for this:

Substitute, PatternCount, Position, Middle, MiddleWords, Left, LeftWords, Right, RightWords, GetValue. Look them up in the help manual and actually study the solutions posted to you!!

I'd merge the topics but the forums are being quite laggy atm, John could you do it if you get a chance?

[mr_vodka]

Ok Merged.

I didnt realize that it was already posted. Hartmut, this is exactly why double and triple posting is discouraged. It creates confusion and people can post the same answers to the threads.

[hartmut]

Your point is well taken

And I do study

But these questions are different

and

Please don't shame people for trying to get a question answered.

Other people who are afraid to ask questions get answers here because some people ask questions until they get answers.

Thanks :

Edited June 7, 2007 by Guest

[mr_vodka]

There was a small typo that I had. Try it now.

[Genx]

Im not deliberatly "shaming you" and definitley not because you want to get your questions answered... But don't start additional topics that are essentially the same for no reason (its called a "topic" for a reason). Your post is automatically bumped when you add to a previous topic so there is no real point... Plus your previous questions were answered in any case so "trying to get your question answered" isn't a real excuse.

The reason this is so discouraged is primarily because this forum serves 2 purposes. 1 is answering question, the other is allowing users to search previous answer to questions so that we answer new questions.

The main point here is that you've now been given essentially three solutions to pulling apart different blocks of text -- as well as different examples of different functions. What happens when you need to parse out 5 more formats, are you going to post 5 more topics?

Anyway to answer your previous question, you need two functions for this. 1: RightWords, 2: WordCount (note that this is ONE way to do this).

RightWords( "315-853-2320 81 Maple St # 101, Canastota, NY 13032" ; WordCount("315-853-2320 81 Maple St # 101, Canastota, NY 13032") - 1)

i.e.

RightWords( Address ; WordCount(Address) - 1 )

i.e.

Count the words in the sentence: "315-853-2320 81 Maple St # 101, Canastota, NY 13032" , and return all the words except for one:

RightWords( Sentence ; NumberOfWordsToReturn )

[hartmut]

Thank you It works fine

I can study it too

Much Appreciated

Dave

[hartmut]

Thank you

I appreciate your help and your concerns

I think I am sometimes confused because there are several ways of doing things. I wanted to remove a word or a letter. I still am not sure how to do that.

In the case of the sentence

"The Cow jumped over the moon"

I can find the word "over " with a script but how do I remove the 4th word. for example.

This is somethng I don't understand.

Thanks

Dave

[Genx]

The fourth Word?

LeftWords( Sentence ; 3 ) //Takes the first three words

& " " & // adds a space

RightWords( Sentence ; WordCount(Sentence) - 4 ) // Takes every word from the right except for the first 4.

[Genx]

If the word was always over you could also use:

Substitute( Sentence ; " over" ) ;

Or you could use Position to establish the character position of the word over and then use Left() and Right() which both refer to characters rather than words.

Or you could use middle.. or you could substitute all the spaces out for a carriage return and use getvalue and middle values -- There are a million ways to do it, but start with the basics.

Also whenever you see a complex statement break it out into its functions to make sense of it.

ie. with Johns suggestion:

Let ( f =

GetValue ( Substitute ( gtest; "¶¶"; "¶" ); 2 )//End Get Value

;//End Let Variable Declaration

Middle ( f;

Position ( f; " "; 1; 1 ) +1;

Position ( f; ","; 1; 1 ) - Position ( f; " "; 1; 1 ) -1)//End Middle Statement

)// End the Let Statement