JSON-Dokumente mit VBA parsen

2013, 4/2013, VBA und Programmiertechniken

Webservices bedienen sich verschiedener Formate, um ihre Ergebnisse zu liefern. Meist wird zumindest XML angeboten, manchmal gibt es die Daten zusätzlich im JSON-Format. Beides kein Problem, da wir den Umgang mit XML-Dokumenten schon in verschiedenen Beiträgen beschrieben haben. Wenn allerdings nur JSON zurückgeliefert wird, müssen wir uns etwas einfallen lassen – es gibt nämlich kein Pendant zur MSXML-Bibliothek, mit der sich die Inhalte leicht auslesen lassen. Aber es gibt ja VBA – und damit bauen wir uns unseren eigenen Parser.

Bei JSON handelt es sich um die JavaScript Object Notation. Der Aufbau ähnelt ein wenig dem Aufbau von XML-Dateien, unterscheidet sich aber in manchen Punkten. Schauen wir uns zunächst an, wie solch ein Dokument aufgebaut ist und welche Elemente es enthalten kann.

Ein einfaches JSON-Dokument sieht so aus:

{
     "Vorname":"André",
     "Nachname":"Minhorst"
}

Es besteht in diesem Fall aus zwei Objekten, wobei der Ausdruck vor dem Doppelpunkt dem Objektnamen entspricht und der hinter dem Doppelpunkt dem Wert.

Etwas komplexer wird es, wenn ein Element ein Unterelement besitzt. In diesem Fall werden die Informationen zum Autor eines Artikels in einem Unterelement gespeichert.

Das Unterelement ist wie das Hauptelement aufgebaut und dementsprechend in geschweifte Klammern eingefasst:

{
     "Titel":"JSON parsen mit VBA",
     "Ausgabe":"4/2013",
     "Autor":{
         "Vorname":"André",
         "Nachname":"Minhorst"
     }
}

Schließlich gibt es noch Aufzählungen, also Elemente, die einen Objektnamen und mehrere Elemente besitzen. Das folgende Beispiel listet die Hobbys einer Person auf, wobei diese in eckige Klammern eingefasst werden:

{
     "Vorname":"André",
     "Nachname":"Minhorst",
     "Hobbys":[
         "Lesen","Reiten","Programmieren"
     ]
}

Nun sind leider nicht alle Elemente in Anführungszeichen eingefasst, sondern nur Zeichenketten.

Es gibt jedoch noch weitere Datentypen, die wie folgt formatiert werden – hier eine übersicht über alle Varianten:

  • Zeichenkette: wie oben dargestellt in Anführungszeichen eingefasst. Zeichenketten können Escape- und Unicode-Zeichen enthalten, also beispielsweise \n für einen Zeilenumbruch.
  • Nullwerte: werden als null angegeben.
  • Boolean: true oder false, allerdings ohne Anführungszeichen
  • Zahlen: ebenfalls ohne Anführungszeichen, dargestellt mit Vorzeichen (+/–), gegebenenfalls mit Angabe eines Expontenten (e/E).

Außerdem kann der Wert eines JSON-Elements, wie bereits oben in den Beispielen angeführt, ein weiteres Element oder ein Array von Elementen sein – unter http://www.json.org/ wird dies schön dargestellt.

Damit ist ein JSON-Element zwar nicht durch eine einfache Routine zu parsen, aber der Aufwand hält sich dennoch in Grenzen.

In der Beispieldatenbank finden Sie die nachfolgend vorgestellten Funktionen zum Parsen eines JSON-Dokuments im Modul mdlJSONParser.

Ergebnis nach dem Parsen

Bevor man sich an die Programmierung eines Parsers begibt, sollte man sich zunächst Gedanken darüber machen, welches Ergebnis man erhalten möchte und wie man dieses weiter verarbeiten kann.

Ein JSON-Element enthält im einfachsten Fall einfache Informationen, die man beispielsweise in einen Datensatz einer Tabelle schreibt, in anderen Fällen aber auch verschachtelte Elemente, für deren Speicherung man eine Reihe verknüpfter Tabellen benötigen würde. Dies ist ja bereits bei dem Beispiel mit der Person und ihren Hobbys der Fall (siehe oben).

Sie werden die Daten aber nicht immer in eine oder mehrere Tabellen schreiben wollen, sondern möchten diese vielleicht auch einmal ad hoc auswerten.

Vielleicht möchten Sie dabei noch nicht einmal auf alle Daten zugreifen, sondern nur bestimmte Elemente auslesen.

Dies ist beispielsweise der Fall, wenn Sie ein JSON-Dokument von einem Webservice erhalten und etwa den Fehlercode auslesen möchten. Rufen Sie beispielsweise einmal im Internet-Browser die folgende URL auf:

https://graph.facebook.com/me/feed

Dann erhalten Sie von Facebook das folgende JSON-Dokument als Antwort:

{
    "error": {
       "message": "An active access token must be used to query information about the current user.",
       "type": "OAuthException",
       "code": 2500
    }
}

In diesem Fall möchten Sie vielleicht nur auf den Wert des Attributs code zugreifen. Wie kann man die Informationen nach dem Parsen also am besten zugänglich machen

Wenn Sie immer mit den gleichen Strukturen arbeiten, könnten Sie dies beispielsweise mit einer oder mehreren Klassen erledigen, die Sie mit den entsprechenden Eigenschaften versehen. Bei vielen verschiedenen Aufrufen der Funktionen eines Webservices würde dies jedoch den Rahmen sprengen – wir benötigen eine flexiblere Variante.

Speichern des geparsten JSON-Dokuments in Collections

Die Lösung ist das Collection-Objekt: Es bietet genau die Möglichkeiten, die für die Unterbringung der in einem JSON-Dokument enthaltenen Informationen nötig sind. In diesem Fall bedeutet dies:

  • das Objekt soll Name-Wert-Paare speichern,
  • als Wert sollen auch weitere Objekte gespeichert werden können und
  • es sollen mehrere gleichartige Name-Wert-Paare berücksichtig werden können.

Für das erste Beispiel von oben sieht dies beispielsweise wie in Listing 1 aus. Zunächst deklarieren und instanzieren Sie das Objekt:

Dim colPerson As Collection
Dim colBeitrag As Collection
Set colPerson = New Collection
colPerson.Add "André", "Vorname"
colPerson.Add "Minhorst", "Nachname"
Set colBeitrag = New Collection
With colBeitrag
     .Add "JSON parsen mit VBA", "Titel"
     .Add "4/2013", "Ausgabe"
     .Add colPerson, "Autor"
End With
Debug.Print "Titel:", colBeitrag("Titel")
Debug.Print "Ausgabe:", colBeitrag("Ausgabe")
Debug.Print "Autor:", colBeitrag("Autor")("Vorname"), colBeitrag("Autor")("Nachname")

Listing 1: Hinzufügen und Abrufen von Collection-Elementen

Dim col As Collection
Set col = New Collection

Dann fügen Sie die Name-Wert-Paare hinzu, wobei der Wert zuerst für den Parameter Item und dann der Name für den Parameter Key angegeben wird:

col.Add "André", "Vorname"
col.Add "Minhorst", "Nachname"

Schließlich können Sie die Werte einfach über den Namen abfragen – sowohl über die Syntax mit Ausrufezeichen als auch als Auflistungselement:

Debug.Print col!Vorname, col!Nachname
Debug.Print col("Vorname"), col("Nachname")

Im zweiten Beispiel mit dem Autor als Unterobjekt sieht der Einsatz des Collection-Objekts wie in Listing 3 aus.

Hier deklarieren wir zwei Objekte – eines für den Artikel und eines für den Autor des Artikels. Beide werden zunächst mit den als Zeichenkette vorliegenden Daten gefüllt, bevor die eine Collection der anderen als weiterer Eintrag hinzugefügt wird. Interessant wird der Zugriff auf die in den Collection-Objekten enthaltenen Daten. Auf Titel und Autor greifen Sie wie gewohnt zu. Auf die Unterelemente können Sie ebenfalls per Ausrufezeichen-Referenzierung und über die Auflistung zugreifen – die verschiedenen Collection-Objekte werden dabei einfach aneinandergereiht. Im Falle eines Datenfeldes hilft das Collection-Objekt ebenfalls weiter. Hier legen Sie wiederum eine Collection für die Personen-Daten an und eines für die Hobbys.

Die Hobbys werden der Collection colHobbys nacheinander hinzugefügt, wobei wir einen durchnummerierten Schlüssel verwenden (Hobby1, Hobby2 und Hobby3).

Diese können Sie später bei der Referenzierung der enthaltenen Texte verwenden. Sie können aber auch direkt über die Auflistung des Collection-Objekts Hobbys darauf zugreifen – s.-Listing 2.

Dim colPerson As Collection
Dim colHobbys As Collection
Dim i As Integer
Set colPerson = New Collection
colPerson.Add "André", "Vorname"
colPerson.Add "Minhorst", "Nachname"
Set colHobbys = New Collection
With colHobbys
     .Add "Lesen", "Hobby1"
     .Add "Reiten", "Hobby2"
     .Add "Programmieren", "Hobby3"
End With
colPerson.Add colHobbys, "Hobbys"
Debug.Print colPerson!Vorname, colPerson!Nachname
Debug.Print colPerson!Hobbys!Hobby1
Debug.Print colPerson!Hobbys!Hobby2
Debug.Print colPerson!Hobbys!Hobby3
For i = 1 To 3
     Debug.Print _
        colPerson!Hobbys("Hobby" & i)
Next i
For i = 1 To 3
     Debug.Print colPerson!Hobbys(i)
Next i

Listing 2: Zugriff auf eine Collection mit mehreren gleichartigen Elementen

Da es unter JSON keine anderen Strukturen gibt, reicht das Dictionary-Objekt also völlig aus, um die in einem JSON-Dokument enthaltenen Daten zu speichern und zu referenzieren.

Fehlt nur noch der Code, um das JSON-Dokument zu parsen und in ein oder mehrere Collection-Objekte zu schreiben.

JSON parsen

Die nachfolgend vorgestellten Prozeduren und Funktionen lehnen sich weitgehend an ein im Internet gefundenes Beispiel an (siehe https://code.google.com/p/vba-json/). Wir haben dort jedoch ein paar änderungen vorgenommen – zum Beispiel, um die Referenzierung der Objekte im Direktbereich anzuzeigen.

Den Beginn macht die Funktion JSONParsen, welche das JSON-Dokument als Parameter erwartet (s. Listing 3). Diese Routine ruft zunächst eine weitere Funktion auf, welche überflüssige Steuerzeichen entfernt – wie beispielsweise Zeilenumbrüche oder Leerzeichen außerhalb der Zeichenketten.

Public Function JSONParsen(strJSON As String) As Collection
     Dim l As Long
     strJSON = SteuerzeichenUndLeerzeichenEntfernen(strJSON)
     l = 1
     Select Case Mid(strJSON, l, 1)
         Case "{"
             Set JSONParsen = NameWertParsen(strJSON, l)
         Case "["
             Set JSONParsen = ListeParsen(strJSON, 1)
     End Select
End Function

Listing 3: Parsen eines JSON-Dokuments

Steuerzeichen entfernen

Diese Funktion heißt SteuerzeichenUndLeerzeichenEntfernen und sieht wie in Listing 4 aus. Die Funktion erwartet die zu ändernde Zeichenkette als Parameter und durchläuft diese Zeichen für Zeichen. Das aktuelle Zeichen speichert die Prozedur jeweils in der Variablen strAktuell.

Public Function SteuerzeichenUndLeerzeichenEntfernen(strJSON As String) As String
     Dim l As Long
     Dim strTemp As String
     Dim bolAnfuehrungszeichen As Boolean
     Dim strAktuell As String
     For l = 1 To Len(strJSON)
         strAktuell = Mid(strJSON, l, 1)
         If strAktuell = """" Then
             bolAnfuehrungszeichen = Not bolAnfuehrungszeichen
         End If
         If Not IstSteuerzeichen(strAktuell) Then
             If strAktuell = " " Then
                 If bolAnfuehrungszeichen Then
                     strTemp = strTemp & strAktuell
                 End If
             Else
                 strTemp = strTemp & strAktuell
             End If
         End If
     Next l
     SteuerzeichenUndLeerzeichenEntfernen = strTemp
End Function

Listing 4: Entfernen der Steuerzeichen aus einer Zeichenkette

Die Prozedur soll Steuer- und Leerzeichen nur außerhalb der eigentlichen Werte des JSON-Dokuments entfernen. Da diese Werte entweder in Anführungszeichen eingefasst sind oder aber keine Leerzeichen enthalten (Zahlen et cetera), muss für jedes Zeichen zunächst geprüft werden, ob es sich innerhalb oder außerhalb einer mit Anführungszeichen eingefassten Zeichenkette befindet.

Deshalb prüft die Prozedur für jedes Zeichen, ob es sich dabei im ein Anführungszeichen handelt. Falls ja, wird die Variable bolAnfuehrungszeichen beim ersten Aufeinandertreffen mit einem Anführungszeichen auf den Wert True eingestellt.

Bei jedem folgenden Anführungszeichen stellt die Prozedur bolAnfuehrungszeichen auf True, wenn diese Variable vorher den Wert False hatte und umgekehrt. So kann die Prozedur jederzeit ermitteln, ob sich das aktuelle Zeichen innerhalb von Anführungszeichen befindet.

Die folgende Zeile prüft anhand der Funktion IstSteuerzeichen, ob es sich bei dem Zeichen um eines der Steuerzeichen vbCrLf, vbCr, vbLf oder vbTab handelt (s. Listing 5). Ist dies der Fall, geschieht nichts. Anderenfalls können zwei interessante Fälle vorliegen:

Public Function IstSteuerzeichen(strZeichen As String) As Boolean
     Select Case strZeichen
         Case vbCrLf, vbCr, vbLf, vbTab
             IstSteuerzeichen = True
     End Select
End Function

Listing 5: Steuerzeichen finden

  • Ist das Zeichen ein Leerzeichen, wird dieses zur temporären Variablen strTemp hinzugefügt – aber nur, wenn sich dieses nicht innerhalb einer durch Anführungszeichen eingefassten Zeichenkette befindet.
  • Jedes andere Zeichen wird zu strTemp hinzugefügt.

Auf diese Weise stellt SteuerzeichenUndLeerzeichenEntfernen eine neue Zeichenkette aus allen Zeichen des JSON-Dokuments zusammen, die kein Steuerzeichen und kein Leerzeichen außerhalb geschlossener Anführungszeichen sind. Warum das Ganze Weil JSON-Dokumente der besseren Lesbarkeit halber gern mit Zeilenumbrüchen und Einrückungen gesendet werden (s. Bild 1), die beim späteren Parsen aber eher stören würden. Daher extrahieren wir diese in einem ersten Schritt.

Ein JSON-Dokument im Webbrowser

Bild 1: Ein JSON-Dokument im Webbrowser

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar