Convertir XLSX en NDJSON

Un tableau JSON est une valeur unique. Pour lire le dernier objet dedans, vous devez avoir analysé tout ce qui le précède, ce qui signifie tenir tout le document en mémoire ou aller chercher un analyseur en flux qui comprend des structures partielles. Pour un fichier de configuration c’est sans importance. Pour quatre millions de lignes, c’est la différence entre un import qui marche et un processus que le système d’exploitation tue.

Le JSON délimité par des sauts de ligne retire le problème en retirant le conteneur. Chaque ligne est une valeur JSON complète et indépendante, donc un consommateur lit une ligne, en fait quelque chose, la jette et continue avec une empreinte mémoire constante. Cette seule propriété est la raison pour laquelle les chargeurs en masse, les expéditeurs de journaux et les travaux d’ingestion en entrepôt ont tous convergé vers la même forme. C’est aussi pourquoi un chargement raté est récupérable. Un flux qui échoue à l’enregistrement huit cent mille peut être repris depuis cette ligne, là où un document qui échoue à l’analyse ne vous donne rien du tout et aucune indication de l’enregistrement responsable.

La garantie ne tient que si rien dans les données ne peut produire son propre saut de ligne, et cette conversion l’applique. Une cellule contenant un saut de ligne — une adresse sur plusieurs lignes, un commentaire tapé avec Alt+Entrée — est écrite avec le saut de ligne échappé à l’intérieur de la chaîne JSON, donc l’enregistrement reste sur une ligne. Une tabulation à l’intérieur d’une cellule est échappée de la même manière.

C’est l’avantage concret sur un fichier délimité. Un export séparé par des tabulations de la même feuille doit envelopper une cellule gênante dans des guillemets et espérer que le lecteur implémente la convention ; ici, il n’y a rien à implémenter. Découper le fichier en morceaux de dix mille lignes est sûr, compter les lignes vous donne le nombre d’enregistrements, et aucune ligne ne peut être corrompue par celle du dessus. La comparaison est concrète : la même cellule contenant une tabulation est écrite ici comme un échappement dans une chaîne JSON entre guillemets, et dans un export séparé par des tabulations comme un champ enveloppé dans des guillemets droits que les outils shell ne comprennent pas. L’une est gérée par chaque analyseur JSON jamais écrit et l’autre est un ticket d’assistance.

Un fichier délimité a un seul type : le texte. Ce qui le lit doit deviner, et deviner, c’est là que des identifiants deviennent des entiers et que des numéros de version deviennent des décimaux. JSON porte la distinction explicitement, donc une quantité est écrite 12, un drapeau true, une valeur manquante null, et un code produit qui était du texte dans le classeur reste entre guillemets avec ses zéros initiaux intacts.

Pour un chargement vers une destination typée, cela représente du vrai temps. Le schéma que vous déclarez et les valeurs que vous fournissez s’accordent sans couche de coercition entre les deux, et un job d’ingestion qui rejette une ligne vous dit quel champ est en désaccord plutôt que de signaler une erreur d’analyse sur un numéro de ligne. Les types viennent du classeur plutôt que d’une inférence sur le texte, ce qui est la partie importante. Une cellule qu’Excel tenait comme nombre est écrite ici comme nombre parce qu’elle en était une, pas parce qu’un analyseur a regardé les caractères et a décidé qu’ils ressemblaient à un.

Chaque ligne répète chaque clé. Cinquante mille lignes de six colonnes signifient cinquante mille copies des six noms de champ, ce qui sur une vraie feuille donnait environ 4,4 Mo contre 1,8 Mo pour les mêmes données en texte séparé par des tabulations — environ deux fois et demie, à partir d’un .xlsx d’environ 4,3 Mo.

Ce compromis est en général correct pour cette destination, parce que le fichier va être lu une fois par une machine plutôt que stocké pour toujours. Si les mêmes enregistrements vont être interrogés à plusieurs reprises, la forme à viser est en colonnes plutôt qu’orientée ligne, et la même feuille atterrit à une petite fraction de la taille. NDJSON est un format de transport, pas un format de stockage. En transit, la taille n’importe en général pas non plus, parce que le fichier se compresse extrêmement bien : les clés répétées sont la chose la plus compressible dedans, et gzip sur le chemin d’un magasin d’objets retire presque tout le surcoût que cette section décrit.

Les outils découlent de la forme. `wc -l` vous donne le nombre exact de lignes, parce qu’il y a un enregistrement par ligne et un saut de ligne à la fin. `head -1` vous montre les noms des champs tels qu’ils ont été réellement écrits plutôt que tels que vous vous en souvenez. `split -l 10000` produit des morceaux qui sont chacun valides seuls.

Pour tout ce qui est au-delà, jq lit un flux de valeurs plutôt qu’un document, donc `jq -c "select(.qty > 100)"` filtre un fichier plus grand que la mémoire et émet la même forme que celle qu’il a consommée. Cette composabilité est la raison de convertir vers cette forme plutôt que vers un tableau qu’il faudrait démonter à nouveau. C’est aussi le moyen le moins cher d’inspecter un tableur que quelqu’un vous a envoyé. Une commande vous donne les noms des champs, une autre vous donne le nombre de lignes, et une troisième vous montre chaque valeur distincte dans une colonne — aucune de ces choses n’exige d’ouvrir le classeur ni de faire confiance à ce que son premier écran semble dire.

Toute API qui dit JSON délimité par des sauts de ligne ne signifie pas seulement cela. Les requêtes en masse d’Elasticsearch et d’OpenSearch intercalent une ligne d’instruction avant chaque document, donc le corps fait deux fois plus de lignes qu’il n’y a d’enregistrements et cette sortie est la matière première plutôt que la charge utile. Générer les lignes d’action est quelques lignes de script sur le fichier.

Les chargeurs qui prennent le format tel quel existent et sont le cas courant : un job de chargement en entrepôt pointé vers du JSON délimité par des sauts de ligne, un producteur de file lisant une ligne par message, la routine d’import propre d’une application. Vérifiez la documentation de la destination pour savoir si elle veut des enregistrements ou un corps de requête, parce que les deux se ressemblent assez pour faire perdre un après-midi. La question discriminante est de savoir si la description du format mentionne une ligne d’action ou de métadonnées. Si oui, on vous demande un protocole et ce fichier est la moitié payload ; si elle ne décrit que les enregistrements, cette sortie entre telle quelle.

Les dates dans un tableur sont des comptes de jours portant un format d’affichage, et la conversion écrit la valeur plutôt que l’apparence : 1er janvier 2024 devient 45292, et un horodatage devient ce nombre avec une fraction pour l’heure. Le compte commence à fin décembre 1899.

C’est le mode de défaillance à surveiller sur cette paire spécifiquement, parce que les nombres JSON sont légitimes et qu’un chargeur avec une colonne numérique prendra 45292 avec plaisir et ne vous dira rien. Convertissez ces champs délibérément — dans une étape jq sur le fichier, ou dans la cible après chargement — et faites-en une partie du pipeline plutôt que quelque chose que vous remarquerez dans un tableau de bord six semaines plus tard.

Une cellule vide est écrite comme null plutôt qu’omise, donc chaque objet porte les mêmes clés et un consommateur attendant une forme fixe en obtient une. Là où des lignes ultérieures introduisent un champ que les premières n’avaient pas, l’ensemble des clés est l’union à travers la feuille, donc rien n’est écarté pour être apparu tard.

Seule la première feuille du classeur est convertie, parce qu’un flux d’enregistrements n’a aucun moyen d’exprimer « et maintenant une table différente ». Si la feuille qu’il vous faut n’est pas la première, réordonnez le classeur et convertissez à nouveau ; s’il vous en faut plusieurs, convertissez plusieurs fois et chargez-les comme flux séparés, ce que la destination voudra de toute façon.

Tout se passe dans votre navigateur. Le classeur est analysé et les lignes sont écrites localement, sans téléversement et sans file — ce qui pour un export de fiches clients, de commandes, ou quoi que ce soit sous une politique de protection des données est la différence entre un outil que vous pouvez utiliser au travail et un que vous ne pouvez pas.

Le plafond est de 100 Mo par classeur et 100 fichiers par dépôt, le même pour tout le monde et pas un palier à dépasser en payant. Bien avant ce chiffre, c’est la mémoire que vous sentirez, parce que la feuille est lue dans un tableau avant d’être écrite : quelques dizaines de mégaoctets est sans histoire, et un classeur proche du plafond est là où un onglet de navigateur commence à peiner. Au-delà, le bon instrument est un lecteur en flux dans un script, et il est plus utile de le dire que d’échouer aux trois quarts d’un très gros fichier.

Si le consommateur est un programme qui lira le fichier entier et le passera à autre chose — une fixture, un script de seed, un corps de requête — un tableau JSON est la forme qu’il attend, et NDJSON n’ajoute qu’une étape d’assemblage. Convertissez en JSON dans ce cas.

Si les enregistrements vont être interrogés à plusieurs reprises plutôt que chargés une fois, convertissez en Parquet : mêmes lignes, une fraction des octets, et une requête qui touche deux champs lit deux colonnes au lieu de chaque ligne. NDJSON est la bonne réponse tant que les données bougent, et c’est une mauvaise réponse une fois arrivées.