Wer kleine und mittlere Webanwendungen erstellt, kennt die Situation: Auf die Frage, wie die zu verarbeitenden Daten vorliegen, bekommt man eine Arbeitsmappe im MS Excel-Format. Darin wurden die Daten bislang gepflegt, und das soll auch so bleiben. Gesucht ist also ein Weg, die Spreadsheet-Daten in ein Format zu überführen, das Browser nativ verstehen, und der möglichst wenig Arbeit bereitet, besonders im Hinblick auf spätere Aktualisierungen der Daten. Hier beschreiben wir einen effizienten Weg zu einer JSON (JavaScript Object Notation)-Datei, die man dann in die Anwendung AJAXen kann.

Ausgangspunkt
Als Beispiel nehmen wir einen Produktkatalog(auszug), der Felder wir Produktnummer, Name, Kategorie und für jedes Produkt ein bin vier Varianten mit Bezeichnung und Code enthält. Der Katalog umfasst 500 Zeilen, das erfordert noch keinen Datenbankserver, ist aber definitiv zu viel für reine Handarbeit.

Werkzeuge
Gebraucht wird erstens ein Programm, das Excel-Arbeitsmappen nach CSV exportieren kann. Wir geben LibreOffice Calc den Vorzug, weil es auf Wunsch CSV erzeugt, in dem jeder Zelleninhalt in Anführungszeichen eingeschlossen ist. Für die Weiterverarbeitung ist es wichtig, dass die Inhalte die gleiche Form haben. Mit Microsoft Excel bekommt man das auch hin, aber nur durch Einsatz eines Makros.
Zweitens wird ein Texteditor benötigt, der Suchen und Ersetzen mit regulären Ausdrücken auf einen ganzen Bereich (aber nur einmal in jeder Zeile!) anwenden kann. Wer sich mit Vi Improved anfreunden kann, findet darin ein mächtiges Werkzeug. Wer mit Linux arbeitet, kann einfach sed verwenden. Es empfiehlt sich, die Schreibweise, die das gewählte Werkzeug für reguläre Ausdrücke verwendet, nachzuschlagen. Insbesondere Verweise auf gefundene Teilausdrücke werden unterschiedlich notiert. Die Beispiele hier beziehen sich auf Vi Improved, für sed wäre die Bereichsangabe "1,$" wegzulassen.

Der Auftakt: CSV exportieren
Wie angesprochen, soll das resultierende CSV jeden Zelleninhalt in Anführungszeichen einschließen. Das Trennzeichen ist relativ beliebig, unsere Beispiele beziehen sich auf den bei LibreOffice üblichen senkrechten Strich „|“. Die Zeichenkodierung sollte UTF-8 sein. Die Titelzeile wird aus dem Export entfernt.
Wenn die Arbeitsmappe mehrere Blätter umfasst, können diese in separate CSVs exportiert, getrennt umgebaut und später in eine gemeinsame Datei gepackt werden.

CSV schrittweise zu JSON umbauen
Ein dem Spreadsheet entsprechendes JSON-Objekt enthält ein Array von Objekten, die jeweils die Spaltenköpfe als Schlüssel und die Zellinhalte als Werte enthalten. Da wollen wir also hin. Die Werte sehen nach dem Export schon fast richtig aus, aber die Schlüssel und die Struktur fehlen noch. Hier kommt jetzt Suchen und Ersetzen zum Einsatz. Anfang und Ende der JSON-Notation ergänzen wir zum Schluss, dann müssen wir keine Zeilen von der Bearbeitung ausschließen.

Als Erstes müssen wir uns um einen Spezialfall kümmern: Anführungszeichen, die in den Zellinhalten vorkommen. Der Export hat diese verdoppelt, für JSON müssen sie aber maskiert werden. Als zusätzliche Schwierigkeit können diese doppelten Anführungszeichen direkt benachbart zu einer Feldgrenze auftreten, daher verwenden wir eine ganze Serie von Ersetzungen:

1,$s/"""$/\\""|/
1,$s/"""|/\\""|/
1,$s/^"""/"\\"|/
1,$s/|"""/|"\\"/
1,$s/\([^|\\]\)""/\1\\"/g

Jetzt beginnen den Einbau der Feldschlüssel damit, den Beginn des Objekts und den ersten Feldschlüssel an die Zeilenanfänge zu setzen. Für den Editor formulieren wir das als Ersetzen des Zeilenanfangs, das auf alle Zeilen der Datei angewendet wird:

1,$s/^/ { “prodNummer”: /

Dann kommen die weiteren Felder der Zeile an die Reihe. Da die Werte alle mit Anführungszeichen beginnen und enden und zwischen ihnen Trennzeichen stehen, können wir nach der Sequenz „|” suchen. Hier wird wichtig, dass der Editor die folgende Änderung zwar auf alle Zeilen anwendet, aber nur auf das erste Auftreten in jeder Zeile. Die genannten Werkzeuge machen das so (wenn man sie nicht anders anweist). Also:

1,$s/”|”/”, “prodName”: /

Das können wir nun für jede Spalte wiederholen. Um der Anwendung die Arbeit zu erleichtern, wollen wir aber die bis zu vier Produktvarianten als Array kodieren. Daher schließen wir beim Einfügen der ersten Variantenspalte den Beginn eines Arrays und eines Objekts mit ein:

1,$s/”|”/”, [ { “variantenCode”: /

Und bei der ersten Spalte der zweiten Variante muss zusätzlich das erste Objekt abgeschlossen werden:

1,$s/”|”/” }, { “variantenCode”: /

Wenn nicht alle Produkte die maximale Anzahl von Varianten haben, bleiben in der Zeile Trennzeichen stehen, die von den Ersetzungen nicht erfasst wurden. Die entfernen wir jetzt, bevor die Zeile abgeschlossen wird, denn dann stehen sie leicht adressierbar am Ende:

1,$s/|*$//

In unserem Beispiel kann das Abschließen des letzten Objekts und des Varianten-Arrays mit dem Abschluss der Datenzeile zusammen erfolgen. Hier zielen wir auf das Zeilenende (das abschließende Komma wird benötigt, weil die Datenzeilen ja den Inhalt eines Arrays bilden):

1,$s/$/ } ] },/

Abschließende Änderungen
Jetzt sind alle Zeilen umgebaut, aber noch ist das Ergebnis kein korrektes JSON, was ja genau ein Objekt beschreibt. Daher fügen wir jetzt noch am Anfang und am Ende die große Klammer ein. Vor den Anfang setzen wir:

{ “produktDaten”: [

Indem wir die Daten an einen Schlüssel hängen (anstatt ein anonymes Array als äußerste Struktur zu verwenden), ermöglichen wir, auch noch weitere Datenpakete in dieselbe Datei zu packen, das kommt oft genug vor, um hier vorzusorgen.
Von der letzten Zeile muss noch das abschließende Komma entfernt werden, das ist in JSON, im Gegensatz zu JavaScript, nicht erlaubt:

$s/,$//

Dann wird noch der Schluss von Array und Objekt eingefügt:

] }

Damit ist die Konvertierung abgeschlossen. Der resultierenden Datei sollte man noch die Endung .json geben.

Prüfen kann man das Resultat übrigens einfach, indem man die Datei im Browser öffnet. Der zeigt dann die Objektstruktur an – oder eben eine Fehlermeldung.

Der ganze Prozess liest sich kompliziert, aber jede einzelne Anweisung baut alle Datenzeilen um, in unserem Fall also ein gutes Dutzend Kommandos für 500 Zeilen. Das lohnt sich dann schon, selbst wenn man vielleicht manchen regulären Ausdruck mehrmals testen muss. Es empfiehlt sich, die erfolgreiche Sequenz von Anweisungen zu notieren, denn die nächste Version der Arbeitsmappe kommt bestimmt, und dann geht die Konvertierung schnell von der Hand.