Suggesties
JSON parser


   JSON parser
   De struktuur van JSON
   Array
   Hiërarchische Array
   UDF
   Dictionary
   TreeView
   Webversie

JSON Parser

Gegevens kunnen in allerlei strukturen/patronen worden opgeslagen: denk bijv. aan CSV, XML.
Ook JSON is een veel gebruikte struktuur.
Als je met de gegevens van zo’n struktuur wil verderwerken zul je die eruit moeten destilleren.
Dat kan met een destilleer-routine. In het Engels wordt dat een parser genoemd.
Hieronder een aanpak voor de verwerking van gegevens uit een JSON bestand voor verdere verwerking in Excel.
De routine is gemaakt met VBA en sluit daarmee naadloos aan op Excel en andere Office programma’s.
In alle gevallen is gebruik gemaakt van de ISBN-gegevens van de website http://openlibrary.org

De struktuur van JSON

Een JSON bestand bestaat uit velden. Die velden kunnen subvelden bevatten. Ook die subvelden kunnen weer subvelden bevatten.
Een veld kan een naam bevatten, maar er kunnen ook velden zonder naam voorkomen.
Een veld kan ook een waarde bevatten.
Alle velden in een JSON-bestand hebben een hiërarschische struktuur ten opzichte van elkaar.
Daardoor kunnen verschillende velden dezelfde naam hebben, afhankelijk van hun hiërarchische positie.
Met schrijfconventies voor het gebruik van {,}, [, ] , : en , worden de velden, de verhouding tussen velden en de inhoud van velden ondubbelzinnig vastgelegd.

In Excel heb je diverse mogelijkheden om velden en hun inhoud weer te geven.
In VBA komen de Array, de hiërarchische Array, de Dictionary en de Treeview in aanmerking.
In Excel kun je ook gebruik maken van User Defind Functions (UDF) die als een gewone Excel-formule gebruik maakt van VBA.
Voor iedere methode is een apart voorbeeldbestand gemaakt.
Voor de eenvoud van de illustratie per bestand zijn de JSON-gegevens van een vijftal ISBN-nummers al in de cellen B2:B6 geplaatst.
In het bestand Web-import worden de gegevens rechtstreeks van internet gehaald en verwerkt.

Array

De cellen A2 t/m A6 bevatten ISBN-nummers: een unieke identificatie voor boeken.
De cellen B2 t/m B6 bevatten de gegevens in JSON-opbouw die voor de overeenkomstige ISBN-nummers zijn opgehaald uit de website http://openlibrary.org/

Met de ‘Spinbutton’ selekteer je het ISBN-nummer waarvan de JSON-gegevens uit cel B2 t/m B6 moeten worden getoond.
De Macro M_fields in de macromodule van Sheet1 leest alle velden en hun overeenkomstige gegevens.
Vervolgens wordt daaraan een ‘pad’ toegevoegd, waaruit blijkt van welk (sub)-veld het veld deel uitmaakt.
Het pad geeft de struktuurinformatie van elk veld weer.
Vanaf cel A10:C10 worden de uitgelezen gegevens gepresenteerd: pad, veldnaam en waarde van het veld.

Array

Hiërarchische Array

De cellen A2 t/m A6 bevatten ISBN-nummers: een unieke identificatie voor boeken.
De cellen B2 t/m B6 bevatten de gegevens in JSON-opbouw die voor de overeenkomstige ISBN-nummers zijn opgehaald uit de website http://openlibrary.org/

Met de ‘Spinbutton’ selekteer je welke JSON-gegevens uit cel B2 t/m B6 moeten worden gepresenteerd.

De Macro M_fields in de macromodule van Sheet1 leest alle velden en hun overeenkomstige gegevens.
Voor ieder nivo van velden wordt een eigen kolom gebruikt.
Zo ontstaat ook een grafische presentatie van de velden met hun gegevens.
Iedere veldnaam wordt donkerder gemarkeerd met een vet lettertype.
De eerste kolom bevat het ’pad’ van het veld.

Hiërarchische Array

User Defined Function

In de cellen A2 t/m A6 staan ISBN-nummers: een unieke identificatie voor boeken.
In de cellen B2 t/m B6 staan JSON-gegevens voor de overeenkomstige ISBN-nummers.
Die zijn opgehaald uit de website openlibrary

Met de Spinbutton selekteer je welke JSON-gegevens uit cel B2 t/m B6 moeten worden getoond.

In gebied A10:C129 staan formules die verwijzen naar de UDF F_json.
De funktie F_json staat in macromodule M_UDF.
De funktie F_json gebruikt de gegevens in variabele sp.
De macro M_fields in de macromodule van Sheet1 leest alle velden en hun waarden en zet het resultaat in variabele sp.
Om variabele sp beschikbaar te maken in de macromodule M_UDF wordt de variabele sp als ’Public’ gedeclareerd in de macromodule M_UDF.

Variabele sp bevat een array met 3 kolommen.
De formules in A10:C129 geven aan welke ‘rij’ en welke ‘kolom’ uit variabele sp in de cel moeten komen.

UDF

Dictionary

De cellen A2 t/m A6 bevatten ISBN-nummers: een unieke identificatie voor boeken.
De cellen B2 t/m B6 bevatten de gegevens in JSON-opbouw die voor de overeenkomstige ISBN-nummers zijn opgehaald uit de website http://openlibrary.org/

Met de ‘Spinbutton’ selekteer je welke JSON- gegevens uit cel B2 t/m B6 moeten worden gepresenteerd.

De Macro M_fields in de macromodule van Sheet1 leest alle velden en hun overeenkomstige gegevens.
Kenmerkend voor een Dictionary is, dat gegevens worden opgeslagen aan de hand van een unieke sleutel. Met behulp van die sleutel kan een veld snel worden uitgelezen.
Omdat velden in een JSON-opbouw geen unieke naam hebben wordt aan de veldnaam de naam van het ‘parent’ veld toegevoegd.
Aan naamloze subvelden wordt per subveld een volgnummer toegekend.

Ter illlustratie toont gebied A10:B..n de sleutels en waarden in de dictionary.
Met de knop ‘zoek’ kan de waarde van een veld worden opgezocht uit de dictionary aan de hand van de unieke sleutel.

Dictionary

TreeView

De cellen A2 t/m A6 bevatten ISBN-nummers: een unieke identificatie voor boeken.
De cellen B2 t/m B6 bevatten de JSON-gegevens die voor de overeenkomstige ISBN-nummers zijn opgehaald uit de website http://openlibrary.org/

Met de ‘Spinbutton’ selekteer je welke JSON- gegevens uit cel B2 t/m B6 moeten worden gepresenteerd.

De Macro M_fields in de macromodule van Sheet1 leest alle velden en hun overeenkomstige gegevens.
In de macro M_treeview worden de gegevens van array sn als ‘Node’ met een unieke sleutel en in onderlinge samenhang in de TreeView gezet.

De Treeview is feitelijk de grafische representatie van het JSON-bestand. De velden en subvelden worden in een boomstruktuur weergegeven.

Er treedt iets merkwaardigs op bij het gbruik van de Treeview: als het bestand wordt opgeslagen wordtde boomstruktuur volledig uitgeklapt. Als het bestand wordt gesloten en daarna geopend heeft Excel de TreeView verwijderd en daarvan een afbeelding gemaakt. Dan kun je er niet mee werken. In het bestand staan de stappen om in het bestand een ‘verse’ TreeView op te nemen.Daarna kun je de spinbutton gebruiken.

TreeView

Webversie

In dit bestand worden de JSON-gegevens rechtstreeks van de website http://openlibrary.org/ binnengehaald.

De cellen A2 t/m A6 bevatten als voorbeeld ISBN-nummers: een unieke identificatie voor boeken.
Als een van deze gevulde cellen wordt aangeklikt haalt de Worksheet_SelectionChange macro de gegevens van het aangeklikte ISBN-nummer op van de website.
Vervolgens zet macro M_fields in de macromodule van Sheet1 de gegevens om in een matrix met 3 kolommen en schrijft die weg in gebied A10:C129.

Als je in cel B2 een 13-cijferig ISBN_nummer invoert, zoekt de macro Worksheet_change op internet de bijbehorende gegevens en plaatst die in gebied A10:C129.

Web-import