Convertir TSV en XLSX

Les pipelines de séquençage, les tables d’expression différentielle, les fichiers d’annotation, les journaux d’instrument, les téléchargements de Search Console et les rapports de plateformes publicitaires sortent tous en TSV, et la plupart ne proposent pas d’alternative. La tabulation a été choisie parce que les descriptions de gènes, les noms de campagne et les titres de pages sont pleins de virgules, et c’est le bon choix pour un format de transport.

Ce n’est pas un format de travail. Le fichier ne porte pas de types de colonnes, pas de largeurs, pas de volet figé et aucune défense contre ce qui va se passer ensuite, à savoir que quelqu’un l’ouvre dans Excel parce que c’est là que l’analyse va réellement se faire. L’ouverture est l’étape dangereuse, et c’est celle que cette conversion remplace.

Excel qui importe du texte applique sa propre interprétation à chaque valeur, et une partie de cette interprétation est un analyseur de dates au filet très large. `1-2` devient le deux janvier. `MARCH1` et `SEPT1` deviennent des dates. `2/3` devient une date. Les longues chaînes de chiffres perdent leurs zéros de tête et les très longues sont rendues en notation scientifique.

Ce n’est pas une rumeur ni un vieux bug. Cela a été mesuré dans la littérature génomique comme affectant une part substantielle des tables supplémentaires publiées, et cela a finalement été répondu en renommant un ensemble de gènes humains plutôt qu’en corrigeant le tableur — ce qui en dit long sur la difficulté de défaire une fois que c’est arrivé. La raison pour laquelle ce n’est pas défaisable est que le texte d’origine a disparu : la cellule contient un numéro de série de date, et il n’y a plus rien qui enregistre quels caractères l’ont produit.

La conversion lit le TSV et écrit un classeur, et elle ne fait pas tourner l’analyseur de dates d’Excel à aucun moment. Une valeur `1-2` reste les quatre caractères `1-2`, `SEPT1` reste `SEPT1`, et `2024-03-11` reste cette chaîne. Chacune est écrite dans la cellule en tant que texte, avec la cellule portant son type.

C’est cette dernière partie qui fait tenir la protection. Une cellule XLSX enregistre quel type de valeur elle contient, donc quand Excel ouvre le classeur il affiche ce qui s’y trouve plutôt que de ré-analyser. La coercition n’a lieu qu’à l’import de texte non typé, et au moment où le classeur atteint Excel il n’y a plus de texte non typé. Ouvrez le .tsv directement et vous obtenez la coercition ; ouvrez le .xlsx produit ici et vous ne l’obtenez pas.

Ce n’est pas un refus global d’interpréter quoi que ce soit, et ce serait malhonnête de le présenter ainsi. Un champ qui se lit comme un nombre simple devient une cellule numérique, ce que vous voulez pour des comptes, des p-values, des fold changes, des impressions et des coûts — ils arrivent prêts à trier et à calculer.

Le coût tombe sur les codes à remplissage de zéros. `00123` devient 123, et une position de plaque ou une référence interne qui dépend de son remplissage le perd silencieusement. Les valeurs en notation scientifique comme `1e5` deviennent 100000. Si ces colonnes comptent, la défense est dans la source : un export avec la colonne citée, ou un préfixe non numérique, passe en texte. Cela vaut la peine de vérifier une colonne de codes dans le classeur avant tout travail, puisqu’une cellule numérique est alignée à droite et une cellule texte est alignée à gauche, ce qui rend une colonne mixte visible d’un coup d’œil.

Le même refus qui sauve `1-2` s’applique à une vraie colonne de dates : `2024-03-11` est écrit en texte, pas en valeur de date, donc il ne triera pas chronologiquement et l’arithmétique de dates n’agira pas dessus tant qu’elle n’est pas convertie dans Excel.

C’est le côté délibéré du compromis, et c’est le bon sens. Transformer une colonne texte en dates dans Excel est une opération que vous effectuez en connaissance de cause sur une colonne que vous avez regardée — sélectionnez la colonne, Données, Convertir, et fixez le format de date explicitement, y compris quel jour et quel mois vient en premier. Reconvertir en texte une colonne de dates mal créées n’est pas possible du tout. Choisir de faire un peu de travail sur les colonnes qui sont des dates est une bien meilleure position que de découvrir que certaines colonnes qui ne le sont pas le sont devenues.

Un TSV contient une table, donc le classeur a une feuille. Elle est nommée d’après le fichier sans son extension, tronquée aux trente-et-un caractères qu’un nom de feuille autorise, ce qui signifie qu’un long nom d’export descriptif est coupé plutôt que rejeté.

La ligne d’en-tête devient la première ligne de la feuille et elle fixe les colonnes : chaque ligne suivante est indexée contre elle, donc un champ supplémentaire apparaissant à mi-chemin ne gagne pas une colonne propre. Ses valeurs en surplus sont collectées sous un nom de colonne `__parsed_extra.0`, ce qui est la feuille qui vous dit que l’export est irrégulier.

Les lignes ayant moins de champs que l’en-tête obtiennent une cellule vide plutôt qu’une valeur décalée, ce qui est l’échec qu’un découpeur naïf produit et celui qu’il est le plus difficile de repérer. Donc les deux directions d’irrégularité sont visibles à des endroits différents : trop peu de champs se montre comme un trou dans la ligne, trop comme une colonne à l’extrême droite avec un nom qu’aucun fichier n’avait.

Une feuille contient 1 048 576 lignes en-tête comprise, et 16 384 colonnes. C’est une limite du format de fichier plutôt que de cette conversion, et aucun outil ne peut écrire une feuille plus grande.

Les matrices d’expression, les journaux d’événements et les exports de mots-clés complets dépassent cette ligne régulièrement. Quand ils le font, la réponse honnête est qu’un tableur est le mauvais instrument et pas qu’un convertisseur plus grand est nécessaire : filtrez ou agrégez la table avant de convertir, ou prenez le même fichier en Parquet et interrogez-le dans DuckDB ou pandas, où quelques millions de lignes sont sans histoire. Découper un grand export sur plusieurs feuilles est techniquement possible et analytiquement misérable, puisque chaque formule doit alors savoir sur quelle feuille se trouve la ligne.

Une colonne de description ou d’annotation contenant un saut de ligne est réellement dangereuse dans un TSV : la ligne se termine là où la valeur devait continuer, et chaque compte en aval est faux. Une tabulation collée dans une valeur fait la même chose plus silencieusement, ajoutant une colonne à partir de cette ligne.

Un classeur n’a pas cette fragilité. Un saut de ligne à l’intérieur d’une cellule est un saut de ligne à l’intérieur d’une cellule, et une tabulation est un caractère ordinaire. Si un export TSV vous a donné des comptes de lignes incohérents entre outils, le convertir est un diagnostic raisonnable autant qu’une correction — le compte de lignes du classeur fait autorité d’une manière que celui du fichier texte ne l’a jamais été.

Les deux moitiés tournent sur cette page : l’analyseur de TSV est du JavaScript ordinaire et l’écriture de tableur est une bibliothèque chargée à la demande. Aucune requête ne transporte le fichier, donc un jeu de résultats non publié, les données de campagne d’un client ou une table liée à des patients n’est pas copié chez un tiers pour être ouvert.

La limite est la mémoire plutôt qu’une offre. La table entière est tenue d’un coup pendant que le classeur est assemblé, donc quelques dizaines de mégaoctets se convertit sans cérémonie et quelques centaines est le point où un onglet de navigateur commence à peiner — ce qui est aussi à peu près là qu’arrive le plafond de lignes d’une feuille, donc les deux contraintes mordent ensemble.

Gardez-le si l’étape suivante est un script. R le lit avec `read.delim`, pandas avec `sep="\t"`, et les deux vous laissent déclarer les types de colonnes explicitement, ce qui est une garantie plus forte que ce qu’aucune conversion peut offrir. Un classeur n’apporte rien à ce pipeline et retire la capacité de le diff.

Gardez-le comme copie d’archive dans tous les cas. La conversion lit les valeurs à allure numérique comme des nombres, donc le classeur n’est pas un enregistrement octet fidèle de l’export, et le fichier d’origine est la seule chose qui peut trancher une dispute sur ce qu’une colonne contenait. Là où la table sera interrogée de manière répétée plutôt que lue, Parquet est la meilleure destination et se convertit depuis la même source.