XLSX naar NDJSON omzetten

Een JSON-array is één waarde. Om het laatste object erin te lezen moet je alles ervoor hebben geparst, wat betekent dat je het hele document in het geheugen houdt of naar een streamende parser grijpt. Voor een configuratiebestand doet dat er niet toe. Voor vier miljoen rijen is het het verschil tussen een werkende import en een proces dat het besturingssysteem afkapt.

Newline-delimited JSON haalt het probleem weg door de container weg te halen. Elke regel is een complete, onafhankelijke JSON-waarde, dus een consument leest een regel, doet er iets mee, gooit hem weg en gaat verder met een constant geheugengebruik.

De garantie geldt alleen als niets in de data zelf een nieuwe regel kan produceren, en deze omzetting handhaaft dat. Een cel met een regelafbreking — een adres over meerdere regels, een opmerking getypt met Alt+Enter — wordt geschreven met de afbreking geëscaped binnen de JSON-string, zodat het record op één regel blijft.

Dat is het concrete voordeel boven een met scheidingsteken gescheiden bestand. Een tab-gescheiden export van hetzelfde blad moet een lastige cel in aanhalingstekens wikkelen en hopen dat de lezer die conventie kent; hier is er niets om te implementeren.

Een met scheidingsteken gescheiden bestand heeft één type: tekst. Wat het leest moet gokken, en gokken is waar identificatienummers gehele getallen worden en versienummers decimalen. JSON draagt het onderscheid expliciet, dus een aantal wordt geschreven als 12, een vlag als true, een lege waarde als null.

Voor een laad naar een getypeerde bestemming is dat echte tijdwinst. Het schema dat je declareert en de waarden die je aanlevert stemmen overeen zonder tussenlaag, en een import die een rij weigert vertelt je welk veld niet klopte in plaats van een parsefout op een regelnummer te melden.

Elke regel herhaalt elke sleutel. Vijftigduizend rijen van zes kolommen betekent vijftigduizend kopieën van de zes veldnamen, wat op een echt blad zo’n 4,4 MB opleverde tegen 1,8 MB voor dezelfde data als tab-gescheiden tekst — ruwweg tweeënhalf keer zoveel.

Die ruil klopt meestal voor deze bestemming, omdat het bestand eenmalig door een machine wordt gelezen. Worden dezelfde records herhaaldelijk bevraagd, dan is een kolomgeoriënteerde vorm de betere keuze. In transit maakt de grootte meestal niet uit, want het bestand comprimeert uitstekend.

De tools volgen uit de vorm. `wc -l` geeft je het exacte rijaantal, omdat er één record per regel is met een afsluitende nieuwe regel. `head -1` toont de veldnamen zoals ze echt zijn geschreven. `split -l 10000` levert brokken op die elk op zichzelf geldig zijn.

Voor meer dan dat leest jq een stroom waarden in plaats van één document, dus `jq -c "select(.aantal > 100)"` filtert een bestand groter dan het geheugen en geeft hetzelfde formaat terug dat het las.

Niet elke API die newline-delimited JSON zegt bedoelt alleen dat. Elasticsearch- en OpenSearch-bulkverzoeken plaatsen een instructieregel voor elk document, dus het lichaam heeft dubbel zoveel regels als er records zijn, en deze uitvoer is de ruwe grondstof eerder dan de payload.

Laadtools die het formaat zo aannemen bestaan ook en zijn het gewone geval: een warehouse-laadtaak gericht op newline-delimited JSON, een queue-producent die een regel per bericht leest, de eigen importroutine van een applicatie. Controleer de documentatie van de bestemming.

Datums in een spreadsheet zijn dagentellingen met een weergaveformaat eromheen, en de omzetting schrijft de waarde, niet het uiterlijk: 1 januari 2024 wordt 45292, en een tijdstempel wordt dat getal met een breuk voor de tijd. De telling begint eind december 1899.

Dit is het faalpatroon om op te letten bij dit paar specifiek, want JSON-getallen zijn legitiem en een laadtool met een numerieke kolom neemt 45292 zonder klachten aan. Converteer die velden bewust, als onderdeel van de pijplijn.

Een lege cel wordt geschreven als null in plaats van weggelaten, zodat elk object dezelfde sleutels draagt. Introduceren latere rijen een veld dat eerdere rijen niet hadden, dan is de sleutelset de vereniging over het hele blad, zodat niets wordt weggelaten voor een laat verschijnend veld.

Alleen het eerste blad van de werkmap wordt omgezet, omdat een stroom records geen manier heeft om "en nu een andere tabel" uit te drukken. Staat het gewenste blad niet vooraan, herorden de werkmap dan en converteer opnieuw.

Alles gebeurt in je browser. De werkmap wordt gelezen en de regels lokaal geschreven, zonder upload en zonder wachtrij — bij een export van klantgegevens of iets onder een privacybeleid is dat het verschil tussen een tool die je op je werk kunt gebruiken en een die je niet kunt gebruiken.

Het plafond is 100 MB per werkmap en 100 bestanden in één keer. Ruim voor die grens is geheugen wat je zult voelen, want het blad wordt in een array gelezen voordat het wordt weggeschreven; tientallen megabytes is onopvallend, en een werkmap tegen het plafond laat een browsertabblad merkbaar zwaarder werken.

Is de consument een programma dat het hele bestand leest en doorgeeft aan iets anders — een fixture, een seed-script, een verzoeklichaam — dan is een JSON-array de vorm die het verwacht, en voegt NDJSON alleen een assemblagestap toe. Converteer in dat geval naar JSON.

Worden de records herhaaldelijk bevraagd in plaats van eenmalig geladen, converteer dan naar Parquet: dezelfde rijen, een fractie van de bytes, en een query die twee velden raakt leest twee kolommen in plaats van elke regel.