Quantcast

Splitting address data

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Splitting address data

FionaRM
Hi again,
I have a database that, for historical reasons, had all lines of the
address in one field (apart from the postcode).
I now wish to split those addresses into separate fields (add1, add2,
add3, town/city, county, postcode and country).
Is there a nice easy peasy script solution to this? I've had a quick look
but can't figure it and it is nearly end of play on a Friday so I thought
I'd send the question out.
Any takers?
Thanks
Fiona :-)


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Splitting address data

Rick Shepherd
Hi Fiona,
End of day Friday means Australia ?
I'm at start of day Thursday (Canada), so lets see if we can come up
with something...
What version of Filemaker ?
What does your address field look like ?
Does it have carriage returns between values ?
What is 'common' to all addresses that we could use to parse against ?

Rick

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Fiona Rhodes
Sent: Thursday, December 07, 2006 7:22 AM
To: Filemaker Experts
Subject: Splitting address data

Hi again,
I have a database that, for historical reasons, had all lines of the
address in one field (apart from the postcode).
I now wish to split those addresses into separate fields (add1, add2,
add3, town/city, county, postcode and country).
Is there a nice easy peasy script solution to this? I've had a quick
look but can't figure it and it is nearly end of play on a Friday so I
thought I'd send the question out.
Any takers?
Thanks
Fiona :-)


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

mfwills
In reply to this post by FionaRM
(FileMaker version not specified; answer assumes and may include
features specific to 8.5)

Simple on the surface, but it can get real complicated. This
explanation uses just functions as would comprise calc fields, but
they could be turned into script steps easily enough.

Parse your return-separated values using the GetValue function

Assuming one record contains the (field) Address as:

123 Some Street
Anytown, NY

then

add1 = GetValue ( Address ; 1 ) returns 123 Some Street
CityState = GetValue ( Address ; 2 ) Anytown, NY

An issue will arise when there are more than 2 lines. You would then
have to use the ValueCount function to determine how many lines there
are in the original address and modify your calculations with If or
Case functions to accommodate that possibility.

A further issue would exist in parsing the City, State line. The
simplest would be

City = LeftWords ( CityState ; 1 ) = Anytown
State = RightWords ( CityState ; 1 ) = NY

But, you will likely have cities of more than one word, such as two
in St. Petersburg, FL, or perhaps three in King of Prussia, PA. More
If or Case functions using WordCount.

Since this is ostensibly a one-time effort, you could isolate
specific circumstances (number of lines in address, number of words
in line) and process each found set separately with simple
adjustments to the functions, which would eliminate all of the If or
Case considerations.

Matt

At 12:22 PM +0000 12/07/06, Fiona Rhodes wrote:

>Hi again,
>I have a database that, for historical reasons, had all lines of the
>address in one field (apart from the postcode).
>I now wish to split those addresses into separate fields (add1, add2,
>add3, town/city, county, postcode and country).
>Is there a nice easy peasy script solution to this? I've had a quick look
>but can't figure it and it is nearly end of play on a Friday so I thought
>I'd send the question out.
>Any takers?
>Thanks
>Fiona :-)
>
>
>--
>To unsubscribe: send an email to <[hidden email]>
>Archives: http://www.nabble.com/FMPExperts-f735.html
>FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

Patrick Neame
I hesitate to suggest this and it may be that you have too many records for my suggestion to be practicable, but I am in the same boat with about 400 records and will probably adopt the solution I've set out myself at some point in the future. 

Given the permutations into which an address can be broken down you might find the manual approach is best. I grant you it's tedious but if you can do it a bit at a time then it is safe. It will also give you the opportunity to weed out superfluous lines from the addresses. For example, I often come across the following:-

123 Some Street
Some Suburb
Birmingham
West Midlands

and then the Post Code which, as with your solution has a separate field. In this case the West Midlands line is mere verbiage and it's a moot point whether Birmingham is as well. In point of fact if is sufficient to address a letter:-

123, Some Street,
B12 3CD

What I plan to do is rename the existing address field as "address~line~one" and then create two more called "line~two" and "line~three". Make them calculation fields that both equal address~line~one and then click OK in the field definition box. Once you've done that you can reopen the definition box and change them to text fields. The information will still be there and you can begin the joyous task of editing everything manually. 

As I said a lot depends on how many records you have but hey, do you really want to watch the cricket? I mean they're going to win so why bother? More seriously, computers may be quick but they aren't that clever and a calculation may well delete some vital part of the address in 10% of your records. Then you'll have a fair number of people complaining to you that they haven't had their renewal form, newsletter, repeat prescription or whatever it is you do. In the first two examples it's just unpleasant as people can get quite shirty about this sort of thing. But I wouldn't want to be in your shoes if you haven't sent out a load of repeat prescriptions. 

Good luck and make a back up or three before you do it.



Patrick Neame, www.incamera.co.uk
07 957 463 933 or 01952 433 056



On Dec 7, 2006, at 12:55 pm, Matt Wills wrote:




--
To unsubscribe: send an email to <[hidden email]>
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

FionaRM
In reply to this post by Rick Shepherd
Hi Rick,
My posting arrived late - I'm in the UK :-)
So, questions...
Version 7
Address field is one field with a continuous line. Each 'line' of the
address "should" be separated by a comma.
No carriage returns.
The addresses are mostly UK but quite a number of internationals. Some are
two lines long and some will be five. Not a lot I would say could be called
'common' to all of them.
Sorry for late reply - I work part time and don't get to look at the
postings very often!
Ta in advance.
Fiona :-)

> From: "Rick Shepherd" <[hidden email]>
> Reply-To: "Filemaker Experts" <[hidden email]>
> Date: Thu, 7 Dec 2006 07:49:07 -0500
> To: "Filemaker Experts" <[hidden email]>
> Subject: RE: Splitting address data
>
> Hi Fiona,
> End of day Friday means Australia ?
> I'm at start of day Thursday (Canada), so lets see if we can come up
> with something...
> What version of Filemaker ?
> What does your address field look like ?
> Does it have carriage returns between values ?
> What is 'common' to all addresses that we could use to parse against ?
>
> Rick
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Fiona Rhodes
> Sent: Thursday, December 07, 2006 7:22 AM
> To: Filemaker Experts
> Subject: Splitting address data
>
> Hi again,
> I have a database that, for historical reasons, had all lines of the
> address in one field (apart from the postcode).
> I now wish to split those addresses into separate fields (add1, add2,
> add3, town/city, county, postcode and country).
> Is there a nice easy peasy script solution to this? I've had a quick
> look but can't figure it and it is nearly end of play on a Friday so I
> thought I'd send the question out.
> Any takers?
> Thanks
> Fiona :-)
>
>
> --
> To unsubscribe: send an email to <[hidden email]>
> Archives: http://www.nabble.com/FMPExperts-f735.html
> FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
>
> --
> To unsubscribe: send an email to <[hidden email]>
> Archives: http://www.nabble.com/FMPExperts-f735.html
> FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

FionaRM
In reply to this post by mfwills
Thanks Matt but as you'll see from my post to Rick there are no carriage
returns. Will your idea work with commas?
Fiona :-)

> From: Matt Wills <[hidden email]>
> Reply-To: "Filemaker Experts" <[hidden email]>
> Date: Thu, 7 Dec 2006 07:55:27 -0500
> To: "Filemaker Experts" <[hidden email]>
> Subject: Re: Splitting address data
>
> (FileMaker version not specified; answer assumes and may include
> features specific to 8.5)
>
> Simple on the surface, but it can get real complicated. This
> explanation uses just functions as would comprise calc fields, but
> they could be turned into script steps easily enough.
>
> Parse your return-separated values using the GetValue function
>
> Assuming one record contains the (field) Address as:
>
> 123 Some Street
> Anytown, NY
>
> then
>
> add1 = GetValue ( Address ; 1 ) returns 123 Some Street
> CityState = GetValue ( Address ; 2 ) Anytown, NY
>
> An issue will arise when there are more than 2 lines. You would then
> have to use the ValueCount function to determine how many lines there
> are in the original address and modify your calculations with If or
> Case functions to accommodate that possibility.
>
> A further issue would exist in parsing the City, State line. The
> simplest would be
>
> City = LeftWords ( CityState ; 1 ) = Anytown
> State = RightWords ( CityState ; 1 ) = NY
>
> But, you will likely have cities of more than one word, such as two
> in St. Petersburg, FL, or perhaps three in King of Prussia, PA. More
> If or Case functions using WordCount.
>
> Since this is ostensibly a one-time effort, you could isolate
> specific circumstances (number of lines in address, number of words
> in line) and process each found set separately with simple
> adjustments to the functions, which would eliminate all of the If or
> Case considerations.
>
> Matt
>
> At 12:22 PM +0000 12/07/06, Fiona Rhodes wrote:
>> Hi again,
>> I have a database that, for historical reasons, had all lines of the
>> address in one field (apart from the postcode).
>> I now wish to split those addresses into separate fields (add1, add2,
>> add3, town/city, county, postcode and country).
>> Is there a nice easy peasy script solution to this? I've had a quick look
>> but can't figure it and it is nearly end of play on a Friday so I thought
>> I'd send the question out.
>> Any takers?
>> Thanks
>> Fiona :-)
>>
>>
>> --
>> To unsubscribe: send an email to <[hidden email]>
>> Archives: http://www.nabble.com/FMPExperts-f735.html
>> FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
>
>
> --
> To unsubscribe: send an email to <[hidden email]>
> Archives: http://www.nabble.com/FMPExperts-f735.html
> FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

FionaRM
In reply to this post by Patrick Neame
Re: Splitting address data Hmm, good point Patrick. I don’t want to lose any data as that would get me in some (potentially) big trouble... There are nearly 2000 records though so I would like to try a calculation/script method if possible. I can dummy run it. If I have to run a script for each record it’ll be loads simpler than the editing route (and faster).
Fiona :-)


From: Patrick Neame <[hidden email]>
Reply-To: "Filemaker Experts" <[hidden email]>
Date: Thu, 7 Dec 2006 14:18:43 +0000
To: "Filemaker Experts" <[hidden email]>
Subject: Re: Splitting address data

I hesitate to suggest this and it may be that you have too many records for my suggestion to be practicable, but I am in the same boat with about 400 records and will probably adopt the solution I've set out myself at some point in the future. 

Given the permutations into which an address can be broken down you might find the manual approach is best. I grant you it's tedious but if you can do it a bit at a time then it is safe. It will also give you the opportunity to weed out superfluous lines from the addresses. For example, I often come across the following:-

123 Some Street
Some Suburb
Birmingham
West Midlands

and then the Post Code which, as with your solution has a separate field. In this case the West Midlands line is mere verbiage and it's a moot point whether Birmingham is as well. In point of fact if is sufficient to address a letter:-

123, Some Street,
B12 3CD

What I plan to do is rename the existing address field as "address~line~one" and then create two more called "line~two" and "line~three". Make them calculation fields that both equal address~line~one and then click OK in the field definition box. Once you've done that you can reopen the definition box and change them to text fields. The information will still be there and you can begin the joyous task of editing everything manually. 

As I said a lot depends on how many records you have but hey, do you really want to watch the cricket? I mean they're going to win so why bother? More seriously, computers may be quick but they aren't that clever and a calculation may well delete some vital part of the address in 10% of your records. Then you'll have a fair number of people complaining to you that they haven't had their renewal form, newsletter, repeat prescription or whatever it is you do. In the first two examples it's just unpleasant as people can get quite shirty about this sort of thing. But I wouldn't want to be in your shoes if you haven't sent out a load of repeat prescriptions. 

Good luck and make a back up or three before you do it.



Patrick Neame, www.incamera.co.uk <http://www.incamera.co.uk>
07 957 463 933 or 01952 433 056


 

On Dec 7, 2006, at 12:55 pm, Matt Wills wrote:





--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

 


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

mfwills
In reply to this post by FionaRM
It would work, but the calculation gets somewhat messier if you try
to specify the length of each segment using the Position function to
identify the location of each comma. Further messiness results for
the same reasons as in the given example, if you have differing
pieces of data.

Again, assuming this is a one-time effort, I think I would do a
manual cleanup rather than try to come up with an all-encompassing
script or function. It is really simple to save copies of your file
at each step, and you can use a little trial and error until you get
the process correct. Once you get it cleaned up, you have no further
use for complicated scripts or calcs.

Try using the Substitute function to replace every comma with a
pilcrow, then use the technique described earlier to isolate the
individual segments one by one.

Matt

At 4:47 PM +0000 12/13/06, Fiona Rhodes wrote:

>Thanks Matt but as you'll see from my post to Rick there are no carriage
>returns. Will your idea work with commas?
>Fiona :-)
>
>>  From: Matt Wills <[hidden email]>
>>  Reply-To: "Filemaker Experts" <[hidden email]>
>>  Date: Thu, 7 Dec 2006 07:55:27 -0500
>>  To: "Filemaker Experts" <[hidden email]>
>>  Subject: Re: Splitting address data
>>
>>  (FileMaker version not specified; answer assumes and may include
>>  features specific to 8.5)
>>
>>  Simple on the surface, but it can get real complicated. This
>>  explanation uses just functions as would comprise calc fields, but
>>  they could be turned into script steps easily enough.
>>
>>  Parse your return-separated values using the GetValue function
>>
>>  Assuming one record contains the (field) Address as:
>>
>>  123 Some Street
>>  Anytown, NY
>>
>>  then
>>
>>  add1 = GetValue ( Address ; 1 ) returns 123 Some Street
>>  CityState = GetValue ( Address ; 2 ) Anytown, NY
>>
>>  An issue will arise when there are more than 2 lines. You would then
>>  have to use the ValueCount function to determine how many lines there
>>  are in the original address and modify your calculations with If or
>>  Case functions to accommodate that possibility.
>>
>>  A further issue would exist in parsing the City, State line. The
>>  simplest would be
>>
>>  City = LeftWords ( CityState ; 1 ) = Anytown
>>  State = RightWords ( CityState ; 1 ) = NY
>>
>>  But, you will likely have cities of more than one word, such as two
>>  in St. Petersburg, FL, or perhaps three in King of Prussia, PA. More
>>  If or Case functions using WordCount.
>>
>>  Since this is ostensibly a one-time effort, you could isolate
>>  specific circumstances (number of lines in address, number of words
>>  in line) and process each found set separately with simple
>>  adjustments to the functions, which would eliminate all of the If or
>>  Case considerations.
>>
>>  Matt
>>
>>  At 12:22 PM +0000 12/07/06, Fiona Rhodes wrote:
>>>  Hi again,
>>>  I have a database that, for historical reasons, had all lines of the
>>>  address in one field (apart from the postcode).
>>>  I now wish to split those addresses into separate fields (add1, add2,
>>>  add3, town/city, county, postcode and country).
>>>  Is there a nice easy peasy script solution to this? I've had a quick look
>>>  but can't figure it and it is nearly end of play on a Friday so I thought
>>>  I'd send the question out.
>>>  Any takers?
>>>  Thanks
>>>  Fiona :-)
>>>
>>>
>>>  --
>>>  To unsubscribe: send an email to <[hidden email]>
>>>  Archives: http://www.nabble.com/FMPExperts-f735.html
>>>  FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
>>
>>
>>  --
>>  To unsubscribe: send an email to <[hidden email]>
>>  Archives: http://www.nabble.com/FMPExperts-f735.html
>>  FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
>>
>
>
>--
>To unsubscribe: send an email to <[hidden email]>
>Archives: http://www.nabble.com/FMPExperts-f735.html
>FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Splitting address data

White Craig
In reply to this post by FionaRM
I often find that data manipulation is better done outside that
database.  Can you export your data to Excel and use the tools there?  I
have copied an Excel file into Word creating a table, then done find and
replace to make changes.  Have even replace spaces with commas to create
delimited files.  Copy the reformatted table back to Excel and import
back to a clean FMP file.

Craig
 
 
 
 
 
 

-----Original Message-----
From: Matt Wills [mailto:[hidden email]]
Sent: Wednesday, December 13, 2006 1:36 PM
To: Filemaker Experts
Subject: Re: Splitting address data

It would work, but the calculation gets somewhat messier if you try
to specify the length of each segment using the Position function to
identify the location of each comma. Further messiness results for
the same reasons as in the given example, if you have differing
pieces of data.

Again, assuming this is a one-time effort, I think I would do a
manual cleanup rather than try to come up with an all-encompassing
script or function. It is really simple to save copies of your file
at each step, and you can use a little trial and error until you get
the process correct. Once you get it cleaned up, you have no further
use for complicated scripts or calcs.

Try using the Substitute function to replace every comma with a
pilcrow, then use the technique described earlier to isolate the
individual segments one by one.

Matt

At 4:47 PM +0000 12/13/06, Fiona Rhodes wrote:
>Thanks Matt but as you'll see from my post to Rick there are no
carriage

>returns. Will your idea work with commas?
>Fiona :-)
>
>>  From: Matt Wills <[hidden email]>
>>  Reply-To: "Filemaker Experts" <[hidden email]>
>>  Date: Thu, 7 Dec 2006 07:55:27 -0500
>>  To: "Filemaker Experts" <[hidden email]>
>>  Subject: Re: Splitting address data
>>
>>  (FileMaker version not specified; answer assumes and may include
>>  features specific to 8.5)
>>
>>  Simple on the surface, but it can get real complicated. This
>>  explanation uses just functions as would comprise calc fields, but
>>  they could be turned into script steps easily enough.
>>
>>  Parse your return-separated values using the GetValue function
>>
>>  Assuming one record contains the (field) Address as:
>>
>>  123 Some Street
>>  Anytown, NY
>>
>>  then
>>
>>  add1 = GetValue ( Address ; 1 ) returns 123 Some Street
>>  CityState = GetValue ( Address ; 2 ) Anytown, NY
>>
>>  An issue will arise when there are more than 2 lines. You would then
>>  have to use the ValueCount function to determine how many lines
there

>>  are in the original address and modify your calculations with If or
>>  Case functions to accommodate that possibility.
>>
>>  A further issue would exist in parsing the City, State line. The
>>  simplest would be
>>
>>  City = LeftWords ( CityState ; 1 ) = Anytown
>>  State = RightWords ( CityState ; 1 ) = NY
>>
>>  But, you will likely have cities of more than one word, such as two
>>  in St. Petersburg, FL, or perhaps three in King of Prussia, PA. More
>>  If or Case functions using WordCount.
>>
>>  Since this is ostensibly a one-time effort, you could isolate
>>  specific circumstances (number of lines in address, number of words
>>  in line) and process each found set separately with simple
>>  adjustments to the functions, which would eliminate all of the If or
>>  Case considerations.
>>
>>  Matt
>>
>>  At 12:22 PM +0000 12/07/06, Fiona Rhodes wrote:
>>>  Hi again,
>>>  I have a database that, for historical reasons, had all lines of
the
>>>  address in one field (apart from the postcode).
>>>  I now wish to split those addresses into separate fields (add1,
add2,
>>>  add3, town/city, county, postcode and country).
>>>  Is there a nice easy peasy script solution to this? I've had a
quick look
>>>  but can't figure it and it is nearly end of play on a Friday so I
thought
>>>  I'd send the question out.
>>>  Any takers?
>>>  Thanks
>>>  Fiona :-)
>>>
>>>
>>>  --
>>>  To unsubscribe: send an email to
<[hidden email]>
>>>  Archives: http://www.nabble.com/FMPExperts-f735.html
>>>  FMPexperts is hosted by Ironclad Networks
<http://www.ironclad.net.au/>
>>
>>
>>  --
>>  To unsubscribe: send an email to
<[hidden email]>
>>  Archives: http://www.nabble.com/FMPExperts-f735.html
>>  FMPexperts is hosted by Ironclad Networks
<http://www.ironclad.net.au/>
>>
>
>
>--
>To unsubscribe: send an email to <[hidden email]>
>Archives: http://www.nabble.com/FMPExperts-f735.html
>FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>



--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

Beverly Voth-2
I use BBEdit and grep (regular expression parsing), sometimes when
necessary.
Beverly

On 12/13/06 2:04 PM, "White Craig" <[hidden email]> wrote in
whole or in part:

> I often find that data manipulation is better done outside that
> database.  Can you export your data to Excel and use the tools there?  I
> have copied an Excel file into Word creating a table, then done find and
> replace to make changes.  Have even replace spaces with commas to create
> delimited files.  Copy the reformatted table back to Excel and import
> back to a clean FMP file.
>
> Craig
>  
> -----Original Message-----
> From: Matt Wills [mailto:[hidden email]]
>
> It would work, but the calculation gets somewhat messier if you try
> to specify the length of each segment using the Position function to
> identify the location of each comma. Further messiness results for
> the same reasons as in the given example, if you have differing
> pieces of data.
>
> Again, assuming this is a one-time effort, I think I would do a
> manual cleanup rather than try to come up with an all-encompassing
> script or function. It is really simple to save copies of your file
> at each step, and you can use a little trial and error until you get
> the process correct. Once you get it cleaned up, you have no further
> use for complicated scripts or calcs.
>
> Try using the Substitute function to replace every comma with a
> pilcrow, then use the technique described earlier to isolate the
> individual segments one by one.
>
> Matt



--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Splitting address data

Robertson, Forbes-2
In reply to this post by mfwills
I would strongly second the idea to do this as multiple manual stages --
if it is a one-time transfer.

Try to isolate sets of records with the same format -- then use a
replace calculation to fix them.

Then move to another set of records, fix those. At the end you will
probably have a few odd records left, it is often quicker to fix those
manually than try to work out a calculation to fix them.

It is worth noting that Filemaker now has a built in Find/Replace
function (very similar to that in Word or Excel) -- which for simple
tasks is easier than using the replace function with a calculation.

Forbes


Forbes Robertson
Unit Manager
Prospects Sales & Marketing

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Matt Wills
Sent: Wednesday, December 13, 2006 6:36 PM
To: Filemaker Experts
Subject: Re: Splitting address data

It would work, but the calculation gets somewhat messier if you try
to specify the length of each segment using the Position function to
identify the location of each comma. Further messiness results for
the same reasons as in the given example, if you have differing
pieces of data.

Again, assuming this is a one-time effort, I think I would do a
manual cleanup rather than try to come up with an all-encompassing
script or function. It is really simple to save copies of your file
at each step, and you can use a little trial and error until you get
the process correct. Once you get it cleaned up, you have no further
use for complicated scripts or calcs.

Try using the Substitute function to replace every comma with a
pilcrow, then use the technique described earlier to isolate the
individual segments one by one.

Matt

At 4:47 PM +0000 12/13/06, Fiona Rhodes wrote:
>Thanks Matt but as you'll see from my post to Rick there are no
carriage

>returns. Will your idea work with commas?
>Fiona :-)
>
>>  From: Matt Wills <[hidden email]>
>>  Reply-To: "Filemaker Experts" <[hidden email]>
>>  Date: Thu, 7 Dec 2006 07:55:27 -0500
>>  To: "Filemaker Experts" <[hidden email]>
>>  Subject: Re: Splitting address data
>>
>>  (FileMaker version not specified; answer assumes and may include
>>  features specific to 8.5)
>>
>>  Simple on the surface, but it can get real complicated. This
>>  explanation uses just functions as would comprise calc fields, but
>>  they could be turned into script steps easily enough.
>>
>>  Parse your return-separated values using the GetValue function
>>
>>  Assuming one record contains the (field) Address as:
>>
>>  123 Some Street
>>  Anytown, NY
>>
>>  then
>>
>>  add1 = GetValue ( Address ; 1 ) returns 123 Some Street
>>  CityState = GetValue ( Address ; 2 ) Anytown, NY
>>
>>  An issue will arise when there are more than 2 lines. You would then
>>  have to use the ValueCount function to determine how many lines
there

>>  are in the original address and modify your calculations with If or
>>  Case functions to accommodate that possibility.
>>
>>  A further issue would exist in parsing the City, State line. The
>>  simplest would be
>>
>>  City = LeftWords ( CityState ; 1 ) = Anytown
>>  State = RightWords ( CityState ; 1 ) = NY
>>
>>  But, you will likely have cities of more than one word, such as two
>>  in St. Petersburg, FL, or perhaps three in King of Prussia, PA. More
>>  If or Case functions using WordCount.
>>
>>  Since this is ostensibly a one-time effort, you could isolate
>>  specific circumstances (number of lines in address, number of words
>>  in line) and process each found set separately with simple
>>  adjustments to the functions, which would eliminate all of the If or
>>  Case considerations.
>>
>>  Matt
>>
>>  At 12:22 PM +0000 12/07/06, Fiona Rhodes wrote:
>>>  Hi again,
>>>  I have a database that, for historical reasons, had all lines of
the
>>>  address in one field (apart from the postcode).
>>>  I now wish to split those addresses into separate fields (add1,
add2,
>>>  add3, town/city, county, postcode and country).
>>>  Is there a nice easy peasy script solution to this? I've had a
quick look
>>>  but can't figure it and it is nearly end of play on a Friday so I
thought
>>>  I'd send the question out.
>>>  Any takers?
>>>  Thanks
>>>  Fiona :-)
>>>
>>>
>>>  --
>>>  To unsubscribe: send an email to
<[hidden email]>
>>>  Archives: http://www.nabble.com/FMPExperts-f735.html
>>>  FMPexperts is hosted by Ironclad Networks
<http://www.ironclad.net.au/>
>>
>>
>>  --
>>  To unsubscribe: send an email to
<[hidden email]>
>>  Archives: http://www.nabble.com/FMPExperts-f735.html
>>  FMPexperts is hosted by Ironclad Networks
<http://www.ironclad.net.au/>
>>
>
>
>--
>To unsubscribe: send an email to <[hidden email]>
>Archives: http://www.nabble.com/FMPExperts-f735.html
>FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged.  It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

[hidden email]

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

********************************************************************************

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Splitting address data

Rick Shepherd
In reply to this post by FionaRM
Hi Fiona,
Here is a sample I have used in the past (ver 6 but it will work the
same in ver 7), in this sample I have first substituted commas with a
pipe  "|", then use position to split out data to appropriate fields.
If your data has more values than this calc was made for (3) you can add
to it...
All address info in this sample was coming into shipAddr_1, but needed
to be split out to other fields.
If I were going to test this I would probably run it on a backup copy of
my database, peruse the records to verify results are as expected, then
run it on real data, saving a Non-Altered version first, just in case...

If [ PatternCount( shipAddr_1 , "|" ) >= 1 ]
# "Put middle section in Addr_2"
Set Field [ shipAddr_2, Middle( shipAddr_1 , Position(shipAddr_1, "|" ,
1 , 1 ) + 1 ,
Position(shipAddr_1, "|" , 1 , 2 ) -Position(
shipAddr_1, "|" , 1 , 1 ) -1 ) ]
# "Put right section in Addr_3"
Set Field [ shipAddr_3, Right( shipAddr_1 , Length(shipAddr_1) -
Position( shipAddr_1 , "|" , Length(shipAddr_1) , -1 ) ) ]
# "Keep left section ONLY in Addr_1"
Set Field [ shipAddr_1, Left( shipAddr_1 ,
Position( shipAddr_1 , "|" , 1 , 1) -1 ) ]
End If


HTH
Rick

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Fiona Rhodes
Sent: Wednesday, December 13, 2006 11:45 AM
To: Filemaker Experts
Subject: Re: Splitting address data

Hi Rick,
My posting arrived late - I'm in the UK :-) So, questions...
Version 7
Address field is one field with a continuous line. Each 'line' of the
address "should" be separated by a comma.
No carriage returns.
The addresses are mostly UK but quite a number of internationals. Some
are two lines long and some will be five. Not a lot I would say could be
called 'common' to all of them.
Sorry for late reply - I work part time and don't get to look at the
postings very often!
Ta in advance.
Fiona :-)

> From: "Rick Shepherd" <[hidden email]>
> Reply-To: "Filemaker Experts" <[hidden email]>
> Date: Thu, 7 Dec 2006 07:49:07 -0500
> To: "Filemaker Experts" <[hidden email]>
> Subject: RE: Splitting address data
>
> Hi Fiona,
> End of day Friday means Australia ?
> I'm at start of day Thursday (Canada), so lets see if we can come up
> with something...
> What version of Filemaker ?
> What does your address field look like ?
> Does it have carriage returns between values ?
> What is 'common' to all addresses that we could use to parse against ?
>
> Rick
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Fiona Rhodes
> Sent: Thursday, December 07, 2006 7:22 AM
> To: Filemaker Experts
> Subject: Splitting address data
>
> Hi again,
> I have a database that, for historical reasons, had all lines of the
> address in one field (apart from the postcode).
> I now wish to split those addresses into separate fields (add1, add2,
> add3, town/city, county, postcode and country).
> Is there a nice easy peasy script solution to this? I've had a quick
> look but can't figure it and it is nearly end of play on a Friday so I

> thought I'd send the question out.
> Any takers?
> Thanks
> Fiona :-)
>
>
> --
> To unsubscribe: send an email to
> <[hidden email]>
> Archives: http://www.nabble.com/FMPExperts-f735.html
> FMPexperts is hosted by Ironclad Networks
> <http://www.ironclad.net.au/>
>
> --
> To unsubscribe: send an email to
> <[hidden email]>
> Archives: http://www.nabble.com/FMPExperts-f735.html
> FMPexperts is hosted by Ironclad Networks
> <http://www.ironclad.net.au/>
>


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Splitting address data

Andy Knasinski
In reply to this post by FionaRM
If you can get distinct lines of address data, our address validation  
tool will split the city-state-zip into separate fields.

<a href="http://www.nrgsoft.com/products/index.lasso?id=7&name=NRG%20Address%">http://www.nrgsoft.com/products/index.lasso?id=7&name=NRG%20Address% 
20Validation

Above and beyond the splitting it also "standardizes" the address  
according to the USPS and tells you if it's deliverable or not, and  
if it's a business or residence.

You can download the demo and cleanup 50 records to see if it meets  
your needs.

On Dec 14, 2006, at 3:00 AM, Filemaker Experts wrote:

> It would work, but the calculation gets somewhat messier if you try
> to specify the length of each segment using the Position function to
> identify the location of each comma. Further messiness results for
> the same reasons as in the given example, if you have differing
> pieces of data.


--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>
Loading...