Convertir XLSX en TSV

La raison de préférer une sortie séparée par tabulations est étroite et pratique. Les adresses contiennent des virgules. Les descriptions de produits contiennent des virgules. Les nombres formatés dans la moitié de l’Europe contiennent des virgules. Dans un fichier séparé par des virgules, chacune doit être entourée de guillemets, et chaque consommateur du fichier doit implémenter correctement le mécanisme de guillemets pour retrouver les frontières de colonnes.

Les tabulations n’apparaissent pour ainsi dire pas dans les données, donc le délimiteur et le contenu cessent de se concurrencer. La sortie de cette conversion écrit une cellule qui contient « Gadget, petit » exactement comme cela, sans guillemets nulle part sur la ligne. `cut -f2` la trouve. `awk -F"\t"` la trouve. Un champ de texte dans un autre programme la trouve. Rien n’a à analyser ; les choses n’ont qu’à séparer. C’est toute la différence de fiabilité entre les deux séparateurs, et c’est pourquoi un fichier séparé par des tabulations peut être traité correctement par une commande shell d’une ligne alors qu’un fichier séparé par des virgules a besoin d’une bibliothèque qui comprenne les guillemets, les guillemets échappés et les sauts de ligne à l’intérieur d’un champ.

Des tabulations finissent parfois à l’intérieur de cellules de tableur — collées depuis ailleurs, tapées par erreur, portées depuis un export système. Quand cela arrive, le convertisseur écrit cette cellule entourée de guillemets doubles, parce que l’alternative est une ligne qui gagne silencieusement une colonne supplémentaire.

C’est une sortie correcte et un analyseur complet la prend en charge, mais les outils shell pour lesquels ce format est choisi ne le font pas : `cut -f` compte les tabulations et ne sait rien des guillemets, donc il voit un champ de trop à partir de cette ligne. Si un pipeline produit une sortie désalignée sur quelques lignes, c’est presque toujours pourquoi. Cherchez et supprimez les tabulations dans la feuille source plutôt que de rafistoler en aval. La même remarque s’applique à une cellule contenant un saut de ligne, ce qui est courant dans une colonne de notes ou d’adresses et qui force le même mécanisme de guillemets. Si une feuille contient des champs de texte libre, la destination plus sûre est NDJSON, où les deux caractères sont échappés à l’intérieur de la valeur et un enregistrement ne peut jamais briser une ligne.

Un classeur peut contenir trente feuilles. Un fichier séparé par des tabulations contient une table avec une ligne d’en-tête, et il n’y a pas de syntaxe dedans pour quoi que ce soit d’autre. La conversion prend donc la première feuille et laisse le reste, ce qui est le seul comportement qui ne peut pas produire silencieusement un fichier fusionné que personne n’a demandé.

Si la feuille que vous voulez n’est pas la première, déplacez-la dans le classeur et convertissez à nouveau — cela prend moins de temps que de lire ce paragraphe. Si vous avez besoin de plusieurs feuilles, convertissez plusieurs fois et gardez les sorties comme fichiers séparés, ce que veut de toute façon un pipeline : joindre deux fichiers sur une clé est une commande, et démêler deux tables concaténées en une ne l’est pas. Cela vaut la peine de savoir quelle feuille est réellement la première, parce que ce n’est pas toujours celle qui s’ouvre. Un classeur mémorise la feuille qui était active au moment de l’enregistrement, donc l’onglet que vous voyez à l’ouverture peut être le troisième, et la conversion prendra la plus à gauche quoi qu’il arrive.

Un tableur ne stocke pas de dates. Il stocke un nombre de jours et un format d’affichage, et le calendrier que vous voyez dans la cellule est le format qui fait son travail. La conversion lit la valeur, pas le costume, donc le 1er janvier 2024 arrive en 45292 et un horodatage arrive en ce nombre avec une fraction attachée pour l’heure du jour.

Le décompte commence fin décembre 1899, ce qui explique que les nombres soient dans les quarante mille pour tout ce qui est récent. Convertissez-les là où vous allez — `date -d "1899-12-30 +45292 days"` dans un shell, ou l’équivalent dans ce qui lira le fichier — plutôt que de reformater le tableur d’abord, parce qu’une date écrite comme texte est un nouveau problème d’analyse à la place d’un problème d’arithmétique. Le nombre est aussi l’indice qu’une colonne est une date. Si un champ où vous attendiez 2024-03-11 contient 45362, rien n’a mal tourné ; vous voyez ce que le tableur a toujours contenu, sans le format qui le masquait.

La ligne d’en-tête fournit les noms de colonnes, et l’ensemble des colonnes est l’union des clés trouvées sur chaque ligne plutôt que seulement celles de la première. Une feuille où un bloc de lignes plus loin porte un champ supplémentaire produit quand même un fichier avec ce champ dans l’en-tête.

Les cellules vides restent comme champs vides plutôt que de disparaître, donc chaque ligne a le même nombre de tabulations et le fichier reste rectangulaire. C’est exactement ce qu’il faut à un outil positionnel. Cela signifie aussi qu’un champ vide est vraiment ambigu entre « blanc » et « non applicable », ce qu’aucun format délimité n’a jamais pu exprimer et qu’aucun convertisseur ne peut inventer.

Un code stocké comme texte dans le classeur sort avec ses zéros intacts : 007 reste 007, et une référence de compte avec zéros de tête est inchangée. Cela vaut la peine d’être dit parce que le contraire est si courant — un CSV ouvert dans Excel puis réenregistré transforme ces colonnes en nombres, et les zéros ont disparu avant même qu’une conversion commence.

Ce que cela ne peut pas réparer, ce sont des dégâts déjà faits. Si le classeur lui-même contient 7 parce que quelqu’un a importé un CSV négligemment le mois dernier, le TSV contiendra 7 aussi. Vérifiez une colonne de codes dans la feuille source avant de convertir ; l’alignement de cellule le donne en général, puisque le texte s’aligne à gauche et les nombres à droite. Une colonne où certaines valeurs sont à gauche et d’autres à droite est le pire cas, parce que cela veut dire que la colonne est moitié texte et moitié nombre et qu’aucun outil en aval ne traitera les deux moitiés de la même manière.

Les lignes sont séparées par un seul retour à la ligne plutôt qu’une paire retour chariot, et le texte est en UTF-8. C’est ce qu’attendent un pipeline Unix, un diff Git et la plupart des importateurs modernes. Un outil Windows qui exige l’autre convention lira en général quand même le fichier, et tout outil qui ne le fait pas peut être pointé vers un convertisseur qui lui est propre.

La sortie est du texte brut, donc elle survit aussi au fait d’être collée plutôt que téléversée — dans un client de base de données, un champ de formulaire, un message de clavardage, un éditeur de texte. Cette voie vaut la peine d’être gardée à l’esprit quand la destination a un écran d’import auquel vous ne faites pas confiance et une zone de collage à laquelle vous faites confiance.

Postgres lit cette forme directement avec COPY, et le détail à savoir est ce qu’il fait avec les blancs : dans son format texte un champ vide est une chaîne vide, et non un null, et seul le marqueur \N signifie null. Si la colonne cible est numérique et que la feuille a des trous, c’est le message d’erreur que vous obtiendrez.

R le lit avec `read.delim`, qui suppose déjà la tabulation, et pandas avec `sep="\t"`. Dans chacun d’eux, spécifiez les types de colonnes pour tout ce qui est un identifiant plutôt qu’une quantité, parce que chacun appliquera sinon exactement l’inférence numérique qui a transformé 007 en 7 à l’origine.

L’analyse et l’écriture se font toutes les deux dans votre navigateur, donc rien n’est téléversé et il n’y a pas de file d’attente, pas de palier et pas de limite de lignes autre que la mémoire de votre machine. Un classeur de cinquante mille lignes sur six colonnes se convertit en un instant et produit environ 1,8 Mo de texte séparé par des tabulations, à partir d’un .xlsx d’environ 4,3 Mo.

Le plafond est le fichier entier gardé en mémoire à la fois, ce qui place quelques dizaines de mégaoctets confortablement à portée et quelques centaines au point où un onglet force. Au-delà, le bon outil est un lecteur en flux dans un script plutôt que n’importe quel convertisseur basé sur un navigateur, et le dire vaut mieux que d’échouer à mi-chemin d’un très gros fichier.

Choisissez plutôt CSV si la destination est un écran d’import qui le nomme, parce qu’un outil qui dit CSV entend souvent littéralement CSV et n’acceptera pas un autre délimiteur quelle que soit sa pertinence. Choisissez NDJSON si les valeurs doivent garder leurs types au cours du voyage, puisqu’un fichier délimité n’a aucun moyen de distinguer le nombre 7 du texte « 7 ».

Choisissez SQL si les lignes vont dans une table qui existe déjà, et Parquet si elles vont dans un moteur analytique qui les interrogera à plusieurs reprises. Le TSV est la bonne réponse pour un cas précis : des outils de texte qui séparent sur un caractère, là où le caractère ne doit pas apparaître dans les données.