JSON in TSV umwandeln

In deutscher Excel-Einstellung ist das Komma das Dezimaltrennzeichen, nicht der Spaltentrenner — Excel erwartet in einer CSV-Datei deshalb standardmäßig ein Semikolon, während viele JSON-Exporte mit Komma geschrieben sind. Öffnet man eine mit Komma getrennte CSV-Datei in einem deutschen Excel direkt per Doppelklick, landet häufig der gesamte Datensatz in einer einzigen Spalte, weil das Trennzeichen nicht zur Ländereinstellung passt.

Ein Tabulator umgeht dieses ganze Problem, weil er in keiner Sprachregion gleichzeitig Dezimaltrennzeichen ist und in echten Werten so gut wie nie vorkommt. Eine Adresse, eine Produktbeschreibung oder ein Freitextkommentar enthält Kommas in Hülle und Fülle — einen Tabulator tippt dort niemand hinein, weil die Tabulatortaste in den meisten Eingabefeldern den Fokus weiterspringen lässt statt ein Zeichen einzufügen.

Excel und Google Sheets lesen tabgetrennten Text aus der Zwischenablage nativ, unabhängig von der eingestellten Ländervariante. Die umgewandelte Ausgabe kopieren, eine Zelle anklicken, einfügen — die Daten landen in Spalten, ganz ohne Trennzeichen-Abfrage und ohne Importassistent.

Das macht TSV zum schnellsten Weg von einem JSON-Export in eine sortierbare Tabelle, und es lohnt sich, ehrlich zu sagen, wann dieser Weg der falsche ist: Soll die Datei aufbewahrt, weitergeschickt oder wiederholt importiert werden, ist eine gespeicherte CSV- oder Excel-Datei die bessere Wahl — die Zwischenablage ist eine Abkürzung, kein Archiv, und eingefügte Daten tragen keinerlei Herkunftsvermerk mehr.

Jeder Eintrag im Array wird eine Zeile, und die Spalten ergeben sich aus den Schlüsseln. Die Datensätze müssen dabei nicht übereinstimmen: Die Umwandlung sammelt die Vereinigung aller Schlüssel über die gesamte Datei und lässt eine Zelle leer, wo ein Eintrag nichts dafür hat — ein Export, in dem nur manche Einträge ein optionales Feld tragen, wandelt sich um, ohne dass eines davon verloren geht.

Das erklärt eine typische Überraschung: Wandelt man testweise nur die ersten zehn Einträge um, entstehen unter Umständen weniger Spalten als bei der ganzen Datei, weil die Stichprobe die selteneren Felder gar nicht enthielt. Fehlt eine erwartete Spalte, liegt der Grund fast immer darin, dass tatsächlich kein Datensatz dieses Feld besaß.

API-Antworten liefern selten ein nacktes Array zurück. Üblich ist ein Objekt mit einem Schlüssel — results, data, items —, dessen Wert das eigentliche Array ist. Die Umwandlung erkennt diese Form: Ein Objekt mit genau einem Schlüssel, dessen Wert ein Array ist, wird ausgepackt, und die enthaltenen Datensätze werden zu Zeilen.

Ein Objekt mit zwei Schlüsseln wird dagegen nicht ausgepackt, weil sich nicht entscheiden lässt, welcher davon die Daten und welcher etwa der Seitenzähler ist. In diesem Fall wird das gesamte Objekt zu einer einzigen Zeile mit gepunkteten Spaltennamen — das sieht falsch aus und ist das Signal, den umgebenden Umschlag vorher in einem Texteditor zu entfernen und erneut umzuwandeln.

JSON ist ein Baum, eine Tabelle ist ein Raster, also muss die Verschachtelung irgendwohin — sie landet im Spaltennamen. Ein Datensatz mit einem kunde-Objekt, das ein ort enthält, ergibt eine Spalte kunde.ort. Eine Verschachtelungsebene ergibt eine Tabelle, die jeder lesen kann, zwei sind meist noch akzeptabel.

Darüber hinaus wird es schnell unübersichtlich. Spaltennamen werden lang, die Tabelle sehr breit, und die meisten Zellen bleiben leer, weil unterschiedliche Datensätze unterschiedlich tief verschachtelt sind. Sechzig Spalten aus einem scheinbar einfachen Export sind dann die Struktur der Daten, die sichtbar wird, kein Fehler — und ein guter Moment zu entscheiden, dass die Daten besser als JSON bleiben.

Ein Datensatz mit drei Schlagworten wird zu drei Spalten namens tags.0, tags.1 und tags.2. Das ist korrekt, es geht kein Wert verloren, und es ist trotzdem eine schlechte Tabelle: Der nächste Datensatz mit fünf Schlagworten fügt zwei weitere Spalten hinzu, und nach „dem zweiten Schlagwort" zu sortieren ergibt keinen Sinn.

Trägt das Array die eigentliche Bedeutung — eine Bestellung mit Positionen, ein Beitrag mit Kommentaren —, ist die brauchbare Lösung eine zweite Tabelle, verknüpft über eine ID, wie eine Datenbank es täte. Ist das Array eher beiläufig, ergibt das Zusammenfügen der Werte zu einer einzigen Zeichenkette schon in der JSON-Datei eine deutlich brauchbarere Tabelle als eine Reihe durchnummerierter Spalten.

Die Annahme, ein Tabulator komme in echten Daten nie vor, stimmt fast immer und nicht ganz immer. Aus einem PDF kopierter Text, ein Codeschnipsel in einem Kommentarfeld oder etwas aus einer anderen Tabelle eingefügtes kann einen enthalten. Ein Zeilenumbruch in einem Wert kommt noch häufiger vor.

In diesem Fall wird der Wert in doppelte Anführungszeichen gesetzt, was das übliche Escaping ist und von jedem Werkzeug korrekt gelesen wird, das die Datei importiert. Am unzuverlässigsten ist genau dieser Fall bei der Zwischenablage, weil sich das Einfügeverhalten zwischen Programmen und Versionen unterscheidet. Enthalten die Daten Freitext mit Zeilenumbrüchen, die Datei besser herunterladen und über den Datei-Import einlesen statt einzufügen.

Zahlen werden als Zahlen geschrieben, Wahrheitswerte als true und false, ein Nullwert als leere Zelle. Nichts wird in Anführungszeichen gesetzt, was das nicht braucht, sodass die Datei genau so liest, wie sie aussieht.

Was danach passiert, liegt außerhalb der Kontrolle der Datei. Excel und Sheets erkennen beim Einfügen für jede Spalte automatisch einen Typ, was führende Nullen aus Postleitzahlen und Artikelnummern entfernt und alles, das wie ein Datum aussieht, in ein Datum verwandelt. Die Gegenmaßnahme bleibt dieselbe wie immer: Zielspalten vor dem Einfügen als Text formatieren, oder einen Importweg wählen, bei dem sich der Spaltentyp festlegen lässt — die Umwandlung selbst kann das nicht verhindern, weil die Information beim Ankommen bereits korrekt ist.

Die Zwischenablage ist die eine Zielgruppe für eine TSV-Datei, die andere ist Software, die standardmäßig Tabulatoren erwartet. Der COPY-Befehl von Postgres verwendet den Tabulator als Standardtrennzeichen, R liest mit read.delim ebenfalls Tabulatoren, und eine ganze Reihe von Analyse- und Log-Werkzeugen nimmt ausschließlich tabgetrennte Eingaben.

Für diese Fälle ist die Ausgabe direkt nutzbar, mit einem Vorbehalt: Zeilen enden mit einem einfachen Zeilenumbruch statt Wagenrücklauf und Zeilenumbruch, was Unix-Werkzeugen entspricht und manche reine Windows-Importer stört. Beschwert sich ein Ladeprogramm über die Zeilenenden, ist das ein einzelner Durchgang durch einen Texteditor.

Parsen und Schreiben geschehen beide als JavaScript in diesem Tab. Nichts wird hochgeladen, es gibt kein Konto und kein Tageslimit, und die kostenlose Stufe nimmt bis zu 100 Megabyte entgegen — die praktische Grenze ist der eigene Arbeitsspeicher, weil das gesamte Array gelesen sein muss, bevor irgendetwas geschrieben werden kann.

Das zählt bei diesem Paar mehr, als es zunächst klingt. Ein JSON-Export ist so gut wie immer rohe API-Ausgabe — Kundendaten, Bestellhistorien, Formularantworten —, und er enthält häufig Felder, an die beim Export niemand gedacht hat, weil niemand erwartet hat, dass sie gelesen würden. Sie in eine Tabelle zu bringen, ohne dass sie über einen fremden Server laufen, ist die einfache Antwort darauf, sich darüber gar nicht erst Gedanken machen zu müssen.