Wenn Sie Daten in einer JSON-Datei gespeichert haben, die Sie in eine Excel-Datei konvertieren möchten, können Sie diese mit Power Query in Excel importieren.
JSON, kurz für JavaScript Object Notation, ist ein offenes Standarddateiformat (textbasiert), das zum Speichern und Transportieren von Daten verwendet wird. Es wird häufig zum Übertragen von Daten von einem Server an eine Webseite verwendet (z. B. zum Übertragen von Daten vom Server an den Client, damit sie auf einer Webseite angezeigt werden können oder umgekehrt).
JSON ist ein Datenübertragungsformat, das als reine Textdatei (wie XML) vorliegt. Wenn Sie wichtige Daten in einer JSON-Datei gespeichert haben, die Sie in eine Excel-Tabelle importieren möchten, können Sie dies ganz einfach mit der Funktion "Get & Transform" von Excel tun, und Sie benötigen nicht einmal VBA-Code, um Daten aus dem zu importieren lokale Festplatte oder über eine Web-API. Sehen wir uns an, wie Sie eine JSON-Datei in eine Excel-Datei konvertieren/importieren.
So importieren Sie eine JSON-Datei in Excel
JSON wird in einer logischen, leicht lesbaren universellen Datenstruktur dargestellt. Es besteht nur aus zwei Datentypen – Objekten oder Arrays oder einer Kombination aus beidem. Objekte sind Schlüssel-Wert-Paare mit einem Doppelpunkt dazwischen und Arrays sind einfach Sammlungen von Objekten, die durch ein Komma getrennt sind.
Sie können JSON-Dateien ganz einfach in Excel-Dateien (.xlsx) konvertieren, indem Sie das Tool "Get & Transform" von Excel (Power Query) verwenden. Anfangs hieß es „Data Explorer“, dann wurde es in „Power Query“ umbenannt. Power Query ist nur in den Versionen 2010 und 2013 von Excel verfügbar. In Microsoft Excel 2016, 2019 und 365 wurde es auf der Registerkarte Daten erneut in die Funktion "Get & Transform" umbenannt.
So sieht eine JSON-Datei aus:
Jetzt haben wir eine JSON-Beispieldatei namens Employees, wie oben gezeigt. Sehen wir uns an, wie wir diese JSON-Datei in eine Excel-Datei konvertieren können.
JSON-Daten
JSON-Daten werden als Name/Wert-Paare geschrieben. Ein Name(Schlüssel)/Wert-Paar besteht aus einem Feldnamen (in doppelten Anführungszeichen), gefolgt von einem Doppelpunkt, gefolgt von einem Wert:
"Vorname": "Dulce"JSON-Objekte
JSON-Objekte können mehrere Name/Wert-Paare enthalten (genau wie in JavaScript) und sie werden wie unten gezeigt in geschweifte Klammern geschrieben.
{ "Vorname": "Dulce", "Nachname": "Abril", "Geschlecht": "Weiblich", "Land": "USA", "Alter": "32", "Datum": "15 /10/2017", "Id": "1562" }JSON-Arrays
JSON-Arrays werden in eckige Klammern ( [ ] ) geschrieben und sind eine Sammlung von Objekten.
Importieren einer JSON-Datei in Excel
Im obigen Beispiel enthält das JSON-Array zahlreiche Objekte. Und jedes Objekt ist ein Datensatz eines Mitarbeiters (mit Vorname, Nachname, Geschlecht, Land, Alter, Datum und ID). In diesem Tutorial verwenden wir „Excel 2016“, um den Datenimport zu demonstrieren.
Öffnen Sie zunächst Microsoft Excel, wechseln Sie zum Tab „Daten“ und klicken Sie in der Gruppe „Daten abrufen und transformieren“ in der linken Ecke des Menübands auf die Schaltfläche „Daten abrufen“. Erweitern Sie in der Dropdown-Liste die Option „Aus Datei“ und wählen Sie die Option „Aus JSON“.
Wenn Sie auf „Von JSON“ klicken, erhalten Sie ein Dateibrowserfenster. Suchen Sie die JSON-Datei auf Ihrem lokalen Datenträger und klicken Sie auf „Importieren“.
Wenn Sie Daten aus einer Web-API (Web Application Programming Interface) importieren möchten, können Sie die Daten direkt aus dem Internet importieren. Um dies zu tun, gehen Sie, anstatt auf die Option „Von JSON“ zu klicken, zur Registerkarte Daten > Daten abrufen > Aus anderer Quelle > „Aus dem Web“ und geben Sie die Web-URL ein.
Wenn Sie auf die Schaltfläche "Importieren" klicken, wie im obigen Screenshot gezeigt, gelangen Sie in den Excel Power Query-Editor. Sie werden feststellen, dass alle Datensätze in dieser Liste in Zeilen unterteilt sind. Aber wir können die tatsächlichen Daten nicht sehen. Um diese Liste in eine Tabelle umzuwandeln, klicken Sie auf die Option „Zu Tabelle“.
Ein Dialogfeld Zu Tabelle wird angezeigt. Behalten Sie im Dialog die Voreinstellungen bei und wählen Sie „OK“.
Ihre Daten liegen jetzt im Tabellenformat vor, aber Sie können die Datensatzdetails immer noch nicht sehen. Um die Spalten zu erweitern, klicken Sie auf die Schaltfläche „Spalte erweitern“ (Symbol mit zwei voneinander weg weisenden Pfeilen).
Sie sehen die Spalten, die in den Datensätzen aufgeführt sind. Wählen Sie die Spalten aus, die Sie in die Tabelle aufnehmen möchten, und klicken Sie auf „OK“. Deaktivieren Sie die Spalten, die Sie ausschließen möchten.
Die Daten werden dann wie unten gezeigt in separate Spalten aufgeteilt.
Sie können die Spalten auch nach Belieben verschieben. Klicken Sie dazu mit der rechten Maustaste auf eine Spaltenüberschrift, wählen Sie „Verschieben“ und wählen Sie, wohin Sie sie verschieben möchten.
Wenn Sie mit dem Layout zufrieden sind, klicken Sie auf die Schaltfläche „Schließen und laden“ unter der Registerkarte „Start“, um die Daten als Tabelle in Excel zu laden.
Die Daten werden nun in ein neues Arbeitsblatt in Excel importiert.
JSON-Datei online in Excel-Datei konvertieren
Wenn Sie JSON-Dateien schnell in Excel-Dateien (.xslx) konvertieren möchten, verwenden Sie eine der vielen online verfügbaren Websites von Drittanbietern. Sie können Ihre Dateien in Sekundenschnelle konvertieren, sind jedoch nicht immer zuverlässig. Geben Sie einfach "JSON in Excel konvertieren" in eine Suchmaschine ein und Sie erhalten viele Websites, die Sie verwenden können.
Eine der Websites, die Sie zum Konvertieren von JSON in XSLX verwenden können, ist json-csv.com. Öffnen Sie die Website und klicken Sie auf die Schaltfläche „JSON-Datei hochladen“, um die JSON von Ihrer lokalen Festplatte hochzuladen. Suchen Sie die JSON-Datei auf Ihrer Festplatte und klicken Sie auf „Öffnen“.
Nachdem Sie die Datei hochgeladen haben, sehen Sie unten eine Vorschau Ihrer Tabelle. Klicken Sie dann auf die Schaltfläche „Excel-Datei (XLSX)“, um Ihre konvertierte Excel-Datei herunterzuladen.
Das ist es! So importieren Sie eine .json-Datei in Excel.