Parsing Addresses: Importing Unparsed Addresses Into Dynamics GP - Part 2

In Part 1 of this post, I discussed my challenge of trying to import unparsed addresses into Dynamics GP and the various options that I found to solve this age old problem.  I would have thought that there would be plenty of solutions available by now, but the ones I found had various limitations that prevented them from working for me.

Then, finally, I came across exactly what I was looking for:  An economical tool called RecogniContact, developed by LoquiSoft, that is specifically designed to perform full "contact" parsing.  It can be integrated into a custom application or web site, and allows you to programmatically parse and validate address information.

In addition to parsing addresses, it can also parse contact data, such as prefix (Mr., Mrs.), middle initial, suffix, title, department, phone number, and e-mail address.  It will even tell you the gender of the contact for over 20,000 first names.  But wait, there's more!  More, you say?

It uses international postal coding standards to intelligently parse addresses for 21 countries (currently US and Europe), parse addresses for another 21 countries based on address structure, and, as scary as this sounds, it can even process contact data in 13 different languages.  It can parse international phone numbers based on country-specific standards, and it can even determine if an area code is valid for a given country.  This actually thwarted my lazy test plans when I tried to use a fake phone number of (123) 456-7890.  It detected the invalid number and, sigh, I had to update my data to use a valid area code.

In addition to this exhaustive list, another very valuable feature is its ability to parse the fields that it can successfully recognize, but then output the specific values that could not be recognized.  This is actually a critical feature when parsing addresses, as it allows the user to skip past the contacts that imported fine, and makes it easier for them to specifically identify and manually review only the "partially" parsed addresses. 

I corresponded with Werner Noska, the owner of LoquiSoft, and he explained that the tool started as a research project for an Austrian federal agency, which resulted in ContactCopy, a desktop address parser.  Due to requests by companies looking to incorporate the technology into their software, he developed RecogniContact.

He explained some technical details about RecogniContact that were outside of my realm, such as structural parsing vs. content aware parsing, and its ability to recognize data elements even without separators.  I know that software developers are sometimes "ego enhanced", and therefore occasionally looking to prove something with their L337 development skillZ, but if you have ever tried to write code from scratch to parse addresses, especially international addresses, trust me, you just can't compete with the knowledge, experience, and accuracy that is built into RecogniContact.

RecogniContact is available as a web service subscription, or as a licensed COM component that can be integrated with .NET or other COM aware development tools.  I chose the COM option, since it was simpler for me to license for my client and their desktop customer import.  With the sample .NET code provided by LoquiSoft, I was able to quickly develop a C# prototype, and in a few hours, I had RecogniContact fully integrated into my customer import.

Based on the limited sample data that I was given by the client (only 330 records), I have been able to consistently achieve an 80% "successful parse rate" (i.e. contact is fully parsed) without manipulating the raw data at all, which I think is pretty good.  The majority of those successfully parsed addresses were in the US and Europe, which explains the high parse rate.  But RecogniContact also did a decent job with some messy addresses, and was able to at least partially parse all of the international addresses.  The 20% of the addresses that were not fully parsed were all partially parsed to some extent, and I was able to flag them for client review, so the results are far better than I could possibly hope to achieve writing my own code.

Here is one I thought was impressive.  Even though the data includes the incorrect country, RecogniContact's knowledge of the UK postal codes allow it to recognize the country:

Original Data:

Neal Hutchins/19 Colomberie, St Helier/Jersey/Channel Islands/JE5 7SY/United States/neal.hutchins@telecom-solutions.com, Narissa.Atista@telecom-solutions.com

Parsed Data:

First: Neal
Last: Hutchins
Full Name:  Neal Hutchins
Gender: M
Company: United States
Address1: 19 Colomberie, St Helier
City: Jersey/Channel Islands
Postal: JE5 7SY
Country: United Kingdom
Country ISO: GB
Email: neal.hutchins@telecom-solutions.com

Unrecognized Values: Narissa.Atista@telecom-solutions.com


In this example, there were two e-mail addresses, in which cases it parses the first, and considers the second email address unrecognized.  And having "United States" in the data is odd, so it does its best and puts it in the company field.  This was actually a common issue with the data I received, so I can easily detect the country, and if it is not US, I can quickly scan the other contact fields to see if United States is present and then remove it, or move the value to the Unrecognized Values property.

Even though it can't perform miracles with every address, RecogniContact's ability to return the unrecognized data values allows me to easily flag the customer records that need manual review and corrections, enabling the client to focus on the problems, and reducing the time required to review the imported customers.

One of the limitations of RecogniContact, and any parsing tool for that matter, is the ability to handle non-standard or ambiguous values.  Things like unusual titles, mail stops, building numbers on a campus, or attention lines seemed to give it the most trouble, as these are often not standard postal address values.  Here are a few examples:

A-105 Memphis, Y-11 Shasta Nagar, Lokhandwala, Complex, Andheri/Mumbai/400053/India

Stockton Network Services, Inc c/o First Fidelity Financial ATTN:  Anita Woodman T10/060/110 Riverside Blvd/Jacksonville/FL/32204 904 884-6830 SVP - Finance & Controller Stan Parkman/United States Bob.Henson@fng.org

Both of these are just too random to parse 100%, but it usually gets at least 50% right, and doesn't take long for a human to correct the remaining fields.

Another aspect of my sample data that posed a challenge for RecogniContact was symbols, such as mixtures of colon, semi colon, ampersand, dash, comma and slash, all in the same address.  This is understandable to me, as the tool needs some means of trying to identify field values, and mixed symbols in contact data pose quite a challenge, especially if they are embedded in a field value.

The Stockton example above is a good one.  When I try and parse it, the "c/o", "ATTN:", and "T10/060" mail stop make it difficult to parse. 

First: Bob
Last: Henson
Full Name:  Bob Henson
Gender: M
Company: SVP - Finance & Controller Stan Parkman/United States
Address1: 060/110 Riverside Blvd
City: Jacksonville
State: FL
Postal: 32204
Country: United States
Country ISO: US
Phone1: +1 (904) 884-6830
Email: Bob.Henson@fng.org

Unrecognized Values: Stockton Network Services, Inc c/o First Fidelity Financial ATTN:  Anita Woodman T10


But as you can see, it did a decent job of parsing a pretty difficult pile of data, getting most of it correct.

With all of that said, once you get the contact information in Dynamics GP, it's actually quite convenient to review the contacts that did not parse successfully.  To make the manual review easier, I store both the original source data and the unrecognized values in the Customer Note.  When reviewing the customer records, the user is able to open the Note window side-by-side with the customer window and edit the customer record.  It works very well and is a simple process.

Another thing I do in Dynamics GP is flag customers whose information did not parse fully--I set the Customer User Defined Field 2 to "Needs Review".  We then created a SmartList favorite that looks for this value, and the client then has a single place to see the customers that need manual review, and they can simply double click on each SmartList record to perform their review.

One final interesting issue that I found with two sample records were people's names that resembled street names.  So "Thomas Way" was interpreted as a street instead of a name.  I can't think of any way around that, and since it was very rare, it wasn't an issue so much as an interesting coincidence.

My customer import with the RecogniContact parsing component has been deployed at the client site, and they will begin testing it this week, so I'll soon see how well it works with fresh data and I am earger to find out how well it meets their needs.

Go forth and parse!

Parsing Addresses: Importing Unparsed Addresses Into Dynamics GP - Part 1

Several years ago, I worked on a large Dynamics GP implementation that involved the migration of over 30,000 customer records from an old, custom, AS400 system.  The client was able to export the customer data to text files for us, but even though we had all of the data, we still had a problem. 

The data in the AS400 was stored in free form text fields, such as ADDRESS1, ADDRESS2, ADDRESS3, NOTE1, NOTE2, etc.  The system did not have separate fields for city, state, or zip, and there was no validation on any of the fields.  Contact names and phone numbers were placed in different fields.  And of course, there were alot of data errors, missing information, random notes, inconsistent formatting, and other oddities in the data that made a clean import nearly impossible.

At the time, we looked for tools to try and parse and validate the data, but we couldn't find any good, and economical, options.  The client ultimately chose to develop their own routines to parse the data just enough to import it into Dynamics GP's separate address and contact fields.  It wasn't pretty, but we got through it.

Fast forward to last month, when I was asked to develop an integration that would import customers from Excel.  Pretty basic, except for some fun caveats:  All of the address data will be in one column in Excel, and company names, contacts, and e-mail addresses are stored inconsistently in other columns.  Oh, and did I mention that the customer information was international?  Yup, the data included addresses from Michigan to Mongolia (literally, as in Ulaanbaatar).  Once again, I was in address parsing purgatory.  (Virgil, is that you???)

Here's one example of an actual customer address that I received (details modified slightly to protect the innocent):

Neal Hutchins/19 Colomberie, St Helier/Jersey/Channel Islands/JE5 7SY/United States/neal.hutchins@telecom-solutions.com, Narissa.Atista@telecom-solutions.com

Try parsing that bad boy.  If you try hard enough, you'll find that the address is not in the United States, but is actually somewhere in the UK.

With this fun challenge, I did some fresh research to see if anything had changed in the world of address parsing.  Given the nature of the problem, there is no single magical solution, but I learned of a few new options.

First, of course, were recommendations to try parse the data "manually" through code.  In some cases, this may be feasible (i.e. US addresses only), but given the data that I needed to parse, I knew that I didn't have enough time to write an address parsing routine that would be particularly accurate.

The next recommendation was to utilize mapping web services to "geocode" the address information.  This is a very good article and code sample on SQLServerCentral.com with a nice overview of the process, as well as a great code sample.  While this is a very slick solution that might be a great option for certain situations, there were a few significant limitations for my project. 

The first is that the geocoding option seems to only handle address information, not "contact" information, such as company name, contact, phone numbers, and e-mail addresses.  The second issue is that the geocoding option does not seem to do a very good job of partially parsing an address.  Many of my test addresses simply failed, and returned no results. 

And finally, the deal killer, which is unfortunately not mentioned in the SQL Server Central article, is that the Google Terms of Service heavily restricts the use of the API for separate (non-map related) or commercial applications.  I'm not a lawyer, so reading the terms of service didn't enlighten me much, but this is what others have informed me.

http://groups.google.com/group/google-contacts-api/browse_thread/thread/65fc032082d5e374

http://code.google.com/apis/maps/documentation/geocoding/#Limits

http://code.google.com/apis/maps/faq.html

So with those two options being ruled out, I started to look for other options that would specifically address my requirements.

I then learned that there are services that you can use to manually parse, scrub, and validate addresses.  While potentially effective, these involve paying for a service (presumably expensive) and sending data in batches.  I needed a real-time programmatic parsing and validation solution for my integration.

After more searching, I finally came across a solution.  I'll discuss it in more detail in part two.