Parsing an Address

[Sam Laundon]

Parsing an Address

This problem is beyond my abilities. Any help will be much appreciated.

I would like to parse a field which contains an address into separate fields.

Example:

LastName

FirstName

Title

CompanyName

Address

City, State, ZipCode

Phone

email

WebAddress

If you do not have time to answer this question, can you point me to where I can find the solution.

Thanks in advance for your help - Sam

[Raybaudi]

... but you have to give us an example ( or some ) of the field to parse !

[Sam Laundon]

Thanks Raybaudi,

Each name needs to be parsed to a separate field:

LastName to a lastname field

FirstName to a firstname field

CompanyName to a companyname field

ect.

Each field name needs to go into a separate field.

[mr_vodka]

Sam, you still havent given us enough info to work with. How is the raw data setup? Is it one big entry? How is the data delineated? Tabs? Spaces?

The more info yu can give, the better we can assist you.

[Sam Laundon]

Sorry all. Did not know what was required.

The addresses are copied from a text file and pasted into a text field in FM. Each line has a return (¶) after it.

LastName¶

FirstName¶

Title¶

CompanyName¶

Address¶

City, State, ZipCode¶

Phone¶

email¶

WebAddress¶

Do you need any more info? Thanks for taking the time.

[mr_vodka]

Since you are on FM7, you can make use of the MiddleValues function. You can use this in a script or for each field, a calculated auto entry.

Substitute ( MiddleValues ( PARSEFIELD ; x ; 1 ); "¶"; "" )

Where x is the starting location for the parse value. for example, x = 1 for LastName, x=2 for FirstName, etc.

For the City, State, & Zip, you could parse out the line first, then use a similar method to parse each one.

City

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); ","; "¶"); 1 ; 1 ); "¶"; "" ) )

State

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); ","; "¶"); 2 ; 1 ); "¶"; "" ) )

Zipcode

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); ","; "¶"); 3 ; 1 ); "¶"; "" ) )

[Sam Laundon]

Thank you so much mr-vodka, I will give it a try.

Sam

[Sam Laundon]

mr_vodka

I tried your solution and it worked fine until I tried to break state and zip code into separate lines. Because there is no period or comma after state the script sees them as one. How can I fix this so that all three (city, state & zip) break into three lines.

Here is what you gave me for state and zip code:

State

Trim ( Substitute ( MiddleValues ( Substitute ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); "¶"; "" ); ","; "¶"); 1 ; 2 ); "¶"; "" ) )

Zipcode

Trim ( Substitute ( MiddleValues ( Substitute ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); "¶"; "" ); ","; "¶"); 1 ; 3 ); "¶"; "" ) )

Thanks again - Sam

[Sam Laundon]

Never mind, I figured it out

[Sam Laundon]

Actually, I am not quite there yet. I am getting carrage returns in the individual fields and I don't want them. How do I get the script not to put carrage returns in each field? Does "¶" have something to do with returns. I don't know what "Â" means.

Thanks in advance - Sam

[mr_vodka]

Your text has actual "¶" signs in it? I thought you meant that "¶" represented a carriage return as it should.

LastName¶

FirstName¶

Title¶

CompanyName¶

Address¶

City, State, ZipCode¶

Phone¶

email¶

WebAddress¶

[mr_vodka]

Once you get rid of the pilcrow "¶" from your raw data, then you can just use these as long as it is ALWAYS in the format 'City, State Zipcode'. In your earlier example, you had it as 'City, State, Zipcode'

State

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); " "; "¶"); 2 ; 1 ); "¶"; "" ) )

Zipcode

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); " "; "¶"); 3 ; 1 ); "¶"; "" ) )

[Sam Laundon]

John - you have been so helpful, I was hoping you can help me with one last issue. How do i deal with the difference between a city that has one word verses on that has two.

Here is what I have for parsing city, state and zip code:

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( leads::import; 3 ; 1 ); " "; "¶"); 1; 1 ); "¶"; " " ) ) & ¶ &

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( leads::import; 3 ; 1 ); " "; "¶"); 2; 1 ); "¶"; "" ) ) & ¶ &

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( leads::import; 3 ; 1 ); " "; "¶"); 3; 1 ); "¶"; "" ) )

This returns:

Boston

MA

02453

But if the city has two words than I get this:

San

Francisco,

CA

Thanks again for your help - Sam

[mr_vodka]

Sam, notice that my last post was only for state and zipcode. The city parse step was not changed. It seems as if you changed that one as well.

[Sam Laundon]

John - as you can see you are dealing with someone who is a novice at all this. I have corrected the lines you mentioned, but I still have the same problem. Here is the script for city, state and zipcode:

Trim ( Substitute ( MiddleValues ( Substitute ( Substitute ( MiddleValues ( leads::import; 3 ; 1 ); "¶"; "" ); ","; "¶"); 1 ; 1 ); "¶"; "" ) ) & ¶ &

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( leads::import; 3 ; 1 ); " "; "¶"); 2 ; 1 ); "¶"; "" ) ) & ¶ &

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( leads::import; 3 ; 1 ); " "; "¶"); 3 ; 1 ); "¶"; "" ) )

This works fine for: Vernon, NJ 07462 which gives me:

Vernon

NJ

07462

But when I try it on this: San Francisco, CA 94122 I get this:

San Francisco

Francisco,

CA

Any ideas as to what I am doing wrong?

As always, thanks for your help - Sam

[mr_vodka]

City

Trim ( Substitute ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); [color:red]","; "¶"); 1 ; 1 ); "¶"; "" ) )

State

Trim ( Substitute ( LeftWords ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); ","; "¶"); 2 ; 1 ); 1 ); "¶"; "" ) )

Zipcode

Trim ( Substitute ( RightWords ( MiddleValues ( Substitute ( MiddleValues ( PARSEFIELD; 6 ; 1 ); ","; "¶"); 2 ; 1 ); 1 ); "¶"; "" ) )

[Sam Laundon]

Thanks John, but your last post solution created empty entries in the import2 field. Don't worry though because I decided to create two scripts, one for one name city and the other for a two name city.

thanks for all you help - Sam