Adressen prüfen und ergänzen

Fast jede Datenbankanwendung speichert Adressen in irgendeiner Form – seien es Kunden-, Mitarbeiter-, Privat- oder Firmenadressen. Diese gelangen über irgendeine Schnittstelle in die Datenbank, beispielsweise über den Import von Daten aus externen Dateien oder auch durch manuelle Eingabe. Aber prüfen Sie diese Adressen auch Oder lassen Sie es darauf ankommen, bis die Post einen Brief zu Ihnen zurückschickt, weil sie mit der Adresse nichts anfangen kann. In diesem Beitrag zeigen wir Ihnen, wie Sie fehlerhafte und unvollständige Adressen aufspüren und sie gegebenenfalls sogar automatisch korrigieren und ergänzen.

Damit Sie später nicht enttäuscht sind: Die hier vorgestellte Lösung kümmert sich tatsächlich nur um Adressen, also um Straßen, Postleitzahlen, Orte, Bundesländer und Länder. Sie prüft nicht, ob eine bestimmte Person einer Adresse zugeordnet werden kann oder nicht – für einen Datenbestand, der dies erledigt, müssten Sie mit Sicherheit eine Menge Geld hinblättern.

Unsere Lösung ist kostenlos und erledigt immerhin die Kontrolle und gegebenenfalls die Ergänzung von Adressen wie etwa Borkhofer Str. 17, Duisburg.

Wenn sie eine passende Adresse findet, liefert sie eine eventuell korrigierte Version zurück (Str. wird beispielsweise zu Straße) und ergänzt die Adresse um fehlende Elemente, hier etwa Postleitzahl, Land und Bundesland. Und falls Sie Bedarf haben, erhalten Sie gleichzeitig noch die Geodaten der angegebenen Adresse.

Wenn tatsächlich keine passende Adresse zu finden ist, soll die Lösung dies melden – so können Sie sich, soweit vorhanden, via E-Mail oder Telefon an den Adressaten wenden und die korrekte Adresse ermitteln. Vielleicht stellt sich ja dabei heraus, dass es die angegebene Adresse tatsächlich nicht gibt und sich jemand einen schlechten Scherz erlaubt hat.

Google als Datenlieferant

Der Kern dieser Anwendung ist ein Dienst von Google, nämlich die Google Geocoding API (http://code.google.com/intl/de/apis/maps/documentation/geocoding/).

Wenn Sie diesem unter einer bestimmten Adresse die richtigen Parameter unterjubeln, liefert er ein XML-Dokument mit den gewünschten Informationen zurück. Um beim Beispiel Borkhofer Str. 17, Duisburg zu bleiben, das immerhin die Postleitzahl vermissen lässt, geben Sie einfach einmal die folgende URL im Webbrowser Ihrer Wahl ein:

http://maps.google.com/maps/api/geocode/xmladdress=Borkhofer+Str.,Duisburg&sensor=false&language=de

Die Bestandteile sind schnell erklärt: http://maps.google.com/maps/api/geocode/ ist die eigentliche Webadresse des Services. xml gibt an, dass Google das Ergebnis im XML-Format liefern soll – alternativ können Sie hier json für die JavaScript Object Notation angeben. Schließlich folgen noch weitere Parameter, die durch ein Fragezeichen eingeleitet werden: address erwartet den eigentlichen Adress-Ausdruck.

Dessen Elemente trennen Sie durch Kommata voneinander. Leerzeichen ersetzen Sie durch das Plus-Zeichen:

adress=Borkhofer+Str.,Duisburg

Weitere Parameter werden durch das Kaufmanns-Und eingeleitet. Der folgende Ausdruck gibt an, dass die Anfrage von einem Gerät ohne Positionsermittlung kommt:

&sensor=false

Der letzte Ausdruck gibt schließlich die Sprache an, in der Google die Antwort liefern soll:

&language=de

Beim Absetzen der Abfrage im Browser kann dieser Parameter wegfallen, da er normalerweise automatisch die Länderkennung auswirft, später beim Aufruf per VBA müssen Sie die Länderkennung jedoch mitschicken.

Als Antwort erhalten Sie eine XML-Datei wie in Abb. 1.

pic001.png

Abb. 1: Diese Antwort liefert eine Adress-Anfrage via Browser.

XML-Adressdaten per Code holen

Natürlich sollen Sie Ihre Adressdaten nicht alle per Webbrowser abfragen, sondern dies automatisiert mit einer geeigneten VBA-Prozedur erledigen. Die einfachste Variante dieser Prozedur finden Sie in Listing 1. Die dortige Prozedur verwendet ein Objekt auf Basis der Klasse DOMDocument aus der XML-Objektbibliothek von Microsoft. Um diese Bibliothek zu verwenden, legen Sie vorab einen Verweis darauf an. Aktivieren Sie den VBA-Editor (am schnellsten mit Alt + F11) und öffnen Sie mit dem Menüeintrag Extras|Verweise den Verweise-Dialog. Fügen Sie dort wie in Abb. 2 einen Verweis auf die Bibliothek Microsoft XML 3.0 oder höher hinzu.

Listing 1: Einfache Funktion zum Aufrufen des Webservices und zum Ausgeben des Ergebnisses

Public Function AdressePruefen()
    Dim objDocument As New MSXML2.DOMDocument
    Dim strRequest As String
    strRequest = "http://maps.google.com/maps/api/geocode/xmladdress=Borkhofer+Str.,Duisburg&sensor=false"
    With objDocument
        .async = False
        .preserveWhiteSpace = False
        .validateOnParse = True
        .resolveExternals = False
    End With
    If objDocument.Load(strRequest) = True Then
        Debug.Print objDocument.XML
    Else
        Debug.Print "#Fehler beim Aufruf"
    End If
End Function

pic002.png

Abb. 2: Hinzufügen eines Verweises auf die XML-Bibliothek von Microsoft

Die Prozedur AdressePruefen deklariert zunächst das Objekt objDocument, das nach einem Aufruf des Google-Webservices mit dessen Antwort gefüllt werden soll.

Die Variable strRequest nimmt die URL mit der Anfrage auf. Nach dem Einstellen einiger Eigenschaften von objDocument, auf die wir hier nicht im Detail eingehen, wird die Anfrage als Parameter der Load-Funktion von objDocument ausgewertet. Die Load-Funktion liefert, je nachdem, ob der Aufruf erfolgreich war oder nicht, entweder den Wert True oder False zurück. War der Aufruf erfolgreich, können Sie anschließend über die Eigenschaft XML des Objekts objDocument den Inhalt der Antwort im XML-Format betrachten – diese wird in dieser ersten Version im Direktfenster ausgegeben (s. Abb. 3).

pic003.png

Abb. 3: Das Ergebnis einer Adress-Abfrage im Direktfenster des VBA-Editors

Einsatzmöglichkeiten

Sie wissen nun, wie Sie eine Anfrage an den Google-Webservice zum Prüfen von Adressen formulieren müssen, und erhalten die Antwort im XML-Format. Nun machen wir uns um die weitere Vorgehensweise Gedanken, was vor allem drei Fragen betrifft:

  • Wie stellen wir die Anfrage zusammen, und zwar speziell den Ausdruck mit der zu prüfenden Adresse
  • Wie ermitteln wir aus dem Ergebnis im XML-Format die gewünschten Informationen
  • Und wie verarbeiten wir diese Informationen weiter

Zusammenstellen der HTTP-Anfrage an Google

Der Aufbau der Anfrage an Google ist bis auf den Wert des Parameters address immer gleich. Wir müssen lediglich die zu prüfenden Adressdaten in eine geeignete Form bringen. Das bedeutet, dass die einzelnen Bestandteile (Straße und Hausnummer, PLZ und Ort sowie gegebenenfalls Land und Bundesland) durch Kommata voneinander getrennt aneinandergereiht werden. Leerzeichen innerhalb dieser Einträge ersetzen wir durch Plus-Zeichen.

XML-Antwort auswerten

Nachdem wir die Anfrage etwa wie in der Prozedur aus Listing 1 abgeschickt haben, erhalten wir ein XML-Dokument zurück. Dieses enthält entweder einen Hinweis darauf, dass die Adresse nicht gefunden oder der Aufruf aus irgendeinem anderen Grund gescheitert ist, oder es liefert die auf Basis der übermittelten Informationen ermittelten Adressdaten. In beiden Fällen müssen wir den Inhalt des XML-Dokuments untersuchen, was wir mit den Mitteln der Microsoft XML 3.0-Bibliothek erledigen.

Adressinformationen weiterverarbeiten

Schließlich müssen wir die so ermittelten Informationen weiterverarbeiten – beispielsweise, indem wir diese über die zuvor ermittelten Adressdaten schreiben und eventuell noch nicht ausgefüllte Felder des Adressbestandes mit den gewonnenen Daten füllen.

Adressen prüfen per Klasse

Da diese Funktionen recht umfangreich sind und möglicherweise in mehr als nur einer Anwendung zum Einsatz kommen könnten, lagern wir diese in eine eigene Klasse aus. Die Beschreibung dieser Klasse folgt im Anschluss, zunächst schauen wir uns ihren praktischen Einsatz an.

Um die Klasse einzusetzen, benötigen Sie zuerst eine Objektvariable zum Speichern der Referenz auf die Klasse:

Dim objAddressChecker As clsAddressChecker

Anschließend erstellen Sie ein Objekt auf Basis der Klasse:

Set objAddressChecker = New clsAddressChecker

Dann weisen Sie einigen Eigenschaften der Klasse die Werte der zu untersuchenden Adresse zu:

With objAddressChecker
    .Street = "Borkhofer Str."
    .Streetnumber = "17"
    .PostalCode = "47137"
    .City = "Duisburg"

Schließlich rufen Sie die Funktion CheckAddress auf, die entweder den Wert True oder False zurückliefert. Im Falle des Wertes True möchten wir die gefundenen Informationen im Direktfenster betrachten:

If .CheckAddress = True Then
     Debug.Print .Street, .Streetnumber, .PostalCode, .City, .Country, .State, .Latitude, .Longitude

Anderenfalls soll das Direktfenster einfach eine Meldung mit einer Erklärung liefern, warum die Adressprüfung nicht funktioniert hat:

    Else
         Debug.Print .Message
    End If
End With

Dies alles können Sie leicht in eine Do While-Schleife packen, die alle Datensätze einer Adressdatenbank durchläuft. Sie müssen dann statt der hier konkret zugewiesenen Zeichenketten die Inhalte der entsprechenden Felder der Adresstabelle übergeben, damit die Adressen geprüft werden können. Sollte die Methode CheckAddress eine passende Adresse gefunden haben, können Sie die vorhandene Adresse gleich durch die geprüfte und gegebenenfalls ergänzte Adresse ersetzen.

Erstellen der Klasse clsAddressChecker

Wechseln Sie zum VBA-Editor und legen Sie eine neue Klasse namens clsAddressChecker an (Menüeintrag Einfügen|Klassenmodul).

Dieser Klasse wollen wir sowohl unsere bestehenden Adressdaten übergeben als auch die geprüften und überarbeiteten Daten entnehmen. Wir benötigen also in jedem Falle einige private Variablen zum Zwischenspeichern dieser Informationen. Diese Variablen sehen wie folgt aus und speichern Informationen wie Straße, Hausnummer et cetera:

Private m_Street As String
Private m_Streetnumber As String
Private m_PostalCode As String
Private m_Country As String
Private m_City As String
Private m_State As String
Private m_Message As String
Private m_Latitude As String
Private m_Longitude As String

Damit Sie dem Objekt die Daten der zu prüfenden Adressen nach dem Instanzieren übermitteln können, legen Sie eine Reihe von Property Let-Prozeduren an. Jede dieser Prozeduren tritt nach außen als Eigenschaft der Klasse clsAddressChecker in Erscheinung. Im laufenden Betrieb nehmen diese die Werte entgegen, die den Eigenschaften des Objekts zugewiesen werden, und speichern diese in den entsprechenden privaten Variablen:

Public Property Let Street(str As String)
    m_Street = str
End Property
Public Property Let Streetnumber(str As String)
    m_Streetnumber = str
End Property
Public Property Let PostalCode(str As String)
    m_PostalCode = str
End Property
Public Property Let Country(str As String)
    m_Country = str
End Property
Public Property Let City(str As String)
    m_City = str
End Property
Public Property Let State(str As String)
    m_State = str
End Property

Sie haben das Ende des frei verfügbaren Textes erreicht. Möchten Sie ...

Workplace

Jahresabonnement TestzugangOder haben Sie bereits Zugangsdaten? Dann loggen Sie sich gleich hier ein:

Schreibe einen Kommentar