regex or programming, splitting entries

[galorin]

I am writing a perl script to import data from one source into another. I am presented with data inconsistencies in the addresses.

In the destination DB, we have fields for 3 address lines, city, county, Postcode, etc. The data I am importing inly has one address field, with commas seperating what should be each line in an address. Splitting this out is mostly trivial, except some addresses have 4 or 5 lines, the data appears as

address1, address2, address3, address4, address5
city
county
postcode

What I want to do is split into 3 groups, but from right to left, so I would get

address1, address2, address3,
address4
address5
city
county
postcode

How would I manage this?

 

[travs69]

You might give

http://search.cpan.org/~sderle/Geo-StreetAddress-US-0.99/US.pm

a try

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[KevinADC]

You may want to inform the forum what type of DB you are importing from and exporting to, and post the code you have currently been trying.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[galorin]

Here is the current code that I am using to split out names which are not as complex, but I expect the solution to be programmatically similar, only difference being the delimiter. I now don't know how to trim it down, and make it re-useable.

#!/usr/local/bin/perl -w

use DBI;

$db_bdb= DBI->connect('dbi:mysql:database=builderdb;host=localhost:3306', $user, $pw, { RaiseError => 1, AutoCommit => 1 });

$rows = $db_bdb->selectall_arrayref("SELECT b.name, bcon.name, title, bcon.telephone, bcon.fax, bcon.mobile, bcon.email, bcon.comments FROM bcon, b WHERE b.builder_id = bcon.builder_id");

foreach $row(@$rows){
        @name = split / /, @$row[1];
        $count = @name;
        if ($count > 2){
                $fn = join(' ', @name[0..($count-2)]);
                $ln = $name[($count-1)];
                #do db insert
        }
        else {
        if ($count == 2){
                $fn = $name[0];
                $ln = $name[1];
                #do db insert
        }else{
                $fn = $name[0];
                $ln = "";
                #do db insert
                }
        }
}

 

[stevexff]

I'm a bit confused by the format in the original post. Are the data held in a single column on the source DB with the address separated by n commas, and the city, county, and postcode tacked on the end but separated by newlines?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[galorin]

In the source DB, the address is all one line, the postcode, etc are stored in their own columns in the DB. I may not have explained it very well, but the select statement in my second post should illustrate the source DB general layout. I think I've cracked it though.

I am now faced with an insert into another database, on the same host though. I can post my code as it stands now if that would help.