AW: (Zahlen)Daten aus Zelle extrahieren
01.04.2024 00:40:34
Zwenn
Hallo fcsp1988,
die von Dir zur Verfügung gestellte Datei enthält in der Tabelle Sheet1 alles, was ich mir gewünscht habe. Die Tabelle Sheet1 habe ich belassen wie sie war. Sie enthält 120 JSONs in 120 Zeilen. Soweit so gut. Blöderweise sind von diesen 120 leider 53 unvollständig und lassen sich nicht "auflösen". Du schreibst, die Daten kommen aus openpyxl. Gute Info! Das ist eine Bibliothek für die Programmiersprache Python, mit der Excel-Dateien bearbeitet werden können.
Leider hat die Person, die den Export der JSONs in in die vorliegende Excel-Datei vorgenommen hat, vergessen zu prüfen, ob ein JSON zu groß ist. In eine Zelle passen nämlich nur 32.767 Zeichen. Alles was darüber noch kam, wurde abgeschnitten. Ich habe in der Tabelle Unvollständige JSONs auf die genannten 53 JSONs gefiltert. Wenn Du die Möglichkeit hast, besorge Dir die JSONs noch einmal als 120 Text-Dateien. Die kann man genau so durchrattern, hat aber keine Begrenzung in der Größe.
Es verbleiben also 67 JSONs, die mit dem JsonConverter abgearbeitet werden können. Die Auflistung dazu befindet sich in der Tabelle Testdata, die ich im Mokro als Quell-Tabelle verwende. Es ist unterm Strich nur die um die korrupten JSONs gekürzte Tabelle Sheet1.
Die von Dir gewünschten Daten kommen für die 67 JSONs nun in die Tabelle Extracted Data. Allerdings sind die Daten nicht ganz so einfach strukturiert, wie Du Dir das vorgestellt hast glaube ich. Du schreibst, Du möchtest mit den Koordinaten Polygone zeichnen. Auch das ist eine gute Info. Denn jedes JSON enthält eine Figur, die Du zeichnen möchtest. Also ein shape. Eine Figur besteht aber aus zwei Teilen, nämlich der Hülle, der shell und Löchern, die sich darin befinden können, den holes. Das hat die Sache etwas komplizierter gemacht, als ich dachte, aber im Grunde muss man "nur" die richtigen Pfade, in der richtigen Reihenfolge, im JSON-Baum abklappern. Das macht das Makro.
Die englischen Begriffe shape, shell und holes, findest Du genau so in den JSONs. Deshalb habe ich sie hingeschrieben. Während eine shell auf jeden Fall vorhanden ist, gibt es in den 67 vollständigen JSONs zwischen 0 und 10 holes. Deshalb findest Du in den Spalten C, D und E eine entsprechende Aufgliederung, um welchen Datenpunkt aus Latitude (Spalte F) und Longitude (Spalte G) es sich pro Zeile in der Tabelle handelt. Zusammengenommen ergibt jede dieser 3er-Kombinationen eine eindeutige Bezeichnung, mittels der Du jeden Punkt einem shape, seiner shell und den zugehörigen holes zuordnen kannst. Keine Ahnung, wie und womit Du Deine Polygone zeichnen willst. Aber ohne diese Infos dürfte das schwierig werden glaube ich.
In Spalte A habe ich einfach die id aus der Quelltabelle und in Spalte B die search_id aus dem jeweiligen JSON übernommen. Keine Ahnung ob Du die Infos brauchst, frisst aber kein Brot, wie man so sagt. Es gibt allerdings nur vier verschiedene search_id, womit die vermutlich eh witzlos sind. Lösche die Spalte aber nicht, sonst stimmt die Zuordnung im Makro nicht mehr.
In der Datei findest Du neben Modul1 noch das Modul JsonConverter und die Klassendatei Dictionary. Beide stammen von Tim Hall, wobei ich Dictionary nur aus Bequemlichkeit eingebunden habe. Damit sollte das vorliegende Konstrukt allerdings auch auf einem Mac laufen, denn die JSONs liegen alle lokal vor. Aber ich habe keinen Mac und kann das deshalb nicht garantieren, falls es jemand ausprobieren möchte.
Im Modul1 befindet sich folgendes Makro:
Public Sub getShapesFromJSON()
Dim json As Dictionary
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim countShell As Long
Dim currShell As Long
Dim countHoles As Long
Dim currHoles As Long
Dim countHolePoints As Long
Dim currHolePoint As Long
Dim shapeNr As Long
Dim id As String
Dim searchId As String
Dim currRowSource As Long
Dim currRowDest As Long
Set wsSource = ThisWorkbook.Sheets("Testdata")
Set wsDest = ThisWorkbook.Sheets("Extracted Data")
currRowDest = 2
For currRowSource = 2 To wsSource.Cells(Rows.Count, 2).End(xlUp).Row
id = wsSource.Cells(currRowSource, 2)
Set json = JsonConverter.ParseJson(wsSource.Cells(currRowSource, 3))
searchId = json("results")(1)("search_id")
countShell = json("results")(1)("shapes")(1)("shell").Count
countHoles = json("results")(1)("shapes")(1)("holes").Count
shapeNr = shapeNr + 1
For currShell = 1 To countShell
wsDest.Cells(currRowDest, 1) = id
wsDest.Cells(currRowDest, 2) = searchId
wsDest.Cells(currRowDest, 3) = "shape_" & shapeNr
wsDest.Cells(currRowDest, 4) = "shell"
wsDest.Cells(currRowDest, 5) = currShell
wsDest.Cells(currRowDest, 6) = json("results")(1)("shapes")(1)("shell")(currShell)("lat")
wsDest.Cells(currRowDest, 7) = json("results")(1)("shapes")(1)("shell")(currShell)("lng")
currRowDest = currRowDest + 1
Next currShell
If countHoles > 0 Then
For currHoles = 1 To countHoles
countHolePoints = json("results")(1)("shapes")(1)("holes")(currHoles).Count
For currHolePoint = 1 To countHolePoints
wsDest.Cells(currRowDest, 1) = id
wsDest.Cells(currRowDest, 2) = searchId
wsDest.Cells(currRowDest, 3) = "shape_" & shapeNr
wsDest.Cells(currRowDest, 4) = "hole_" & currHoles
wsDest.Cells(currRowDest, 5) = currHolePoint
wsDest.Cells(currRowDest, 6) = json("results")(1)("shapes")(1)("holes")(currHoles)(currHolePoint)("lat")
wsDest.Cells(currRowDest, 7) = json("results")(1)("shapes")(1)("holes")(currHoles)(currHolePoint)("lng")
currRowDest = currRowDest + 1
Next currHolePoint
Next currHoles
End If
Next currRowSource
End Sub
Wenn ich es starte, braucht es auf meinem Rechner etwa 18 Sekunden, um die 67 JSONs zu verarbeiten. Das dauert also einen Moment, erstellt dafür aber immerhin auch eindeutig zugeordnete 24.415 Datenpunkte. Die Daten sind noch nicht enthalten, weil die maximale Uploadgröße im Forum 660 KB beträgt. Aber Du lässt halt einfach einmal das Makro laufen.
https://www.herber.de/bbs/user/168424.xlsm
Viele Grüße,
Zwenn
