TSV naar XLSX omzetten

Sequencingpijplijnen, differentiële-expressietabellen, annotatiebestanden, instrumentlogs, Search-Console-downloads en advertentieplatformrapporten komen allemaal tabgescheiden naar buiten, en de meeste bieden geen alternatief. De tab werd gekozen omdat genbeschrijvingen, campagnenamen en paginatitels vol komma's zitten, en dat is de juiste keuze voor een transportformaat.

Het is geen werkformaat. Het bestand draagt geen kolomtypen, geen kolombreedtes, geen bevroren rij en geen verdediging tegen wat er hierna gebeurt, namelijk dat iemand het in Excel opent omdat daar de analyse werkelijk gaat plaatsvinden. Het openen is de gevaarlijke stap, en het is degene die deze omzetting vervangt.

Excel past bij het importeren van tekst zijn eigen interpretatie toe op elke waarde, en een deel van die interpretatie is een datumparser met een heel breed net. `1-2` wordt de tweede januari. `MARCH1` en `SEPT1` worden datums. `2/3` wordt een datum. Lange cijferreeksen verliezen hun voorloopnullen en heel lange worden herschreven in wetenschappelijke notatie.

Dit is geen gerucht of een oude bug. Het is gemeten over de genomicaliteratuur heen als effect op een aanzienlijk deel van gepubliceerde aanvullende tabellen, en het werd uiteindelijk beantwoord door een reeks menselijke genen te hernoemen in plaats van de spreadsheet te repareren — wat je vertelt hoe moeilijk het ongedaan te maken is zodra het gebeurd is. De reden dat het niet ongedaan te maken is: de oorspronkelijke tekst is weg, de cel bevat een datumserieel getal, en er blijft niets over dat vastlegt welke tekens dat produceerden.

De omzetting leest het tabgescheiden bestand en schrijft een werkboek, en draait op geen enkel moment Excels datumparser. Een waarde `1-2` blijft de vier tekens `1-2`, `SEPT1` blijft `SEPT1`, en `2024-03-11` blijft die tekenreeks. Elk ervan wordt als tekst in de cel geschreven, met de cel die zijn type draagt.

Dat laatste deel is wat de bescherming laat vastzitten. Een XLSX-cel legt vast welk soort waarde hij draagt, dus toont Excel bij het openen van het werkboek wat er staat in plaats van het opnieuw te parseren. De dwang gebeurt alleen bij het importeren van ongetypeerde tekst, en tegen de tijd dat het werkboek Excel bereikt, is er geen ongetypeerde tekst meer over. Open de .tsv rechtstreeks en je krijgt de dwang; open de .xlsx die hier gemaakt is en je krijgt het niet.

Dit is geen algeheel weigeren om iets te interpreteren, en het zou oneerlijk zijn het als zodanig te presenteren. Een veld dat als een gewoon getal leest wordt een numerieke cel, wat is wat je wilt voor aantallen, p-waarden, foldveranderingen, vertoningen en kosten — ze komen aan klaar om te sorteren en te berekenen.

De prijs valt op codes met voorloopnullen. `00123` wordt 123, en een platpositie of een interne referentie die op zijn opvulling leunt raakt die kwijt. Waarden in wetenschappelijk ogende notatie zoals `1e5` worden 100000. Doen die kolommen ertoe, dan zit de bescherming in de bron: een export met de kolom aangehaald, of een niet-numeriek voorvoegsel, komt als tekst door. Het is de moeite waard een codekolom in het werkboek te checken voor je verder werkt, want een numerieke cel is rechts uitgelijnd en een tekstcel links, wat een gemengde kolom in één oogopslag zichtbaar maakt.

Dezelfde weigering die `1-2` redt geldt voor een echte datumkolom: `2024-03-11` wordt als tekst geschreven, niet als datumwaarde, dus sorteert hij niet chronologisch en werkt datumrekenkunde er niet op tot hij binnen Excel omgezet wordt.

Dat is de bewuste kant van de ruil, en het is de juiste kant. Een tekstkolom in Excel naar datums omzetten is een bewerking die je bewust uitvoert op een kolom die je hebt bekeken — selecteer de kolom, Gegevens, Tekst naar kolommen, en stel het datumformaat expliciet in, inclusief welke van dag en maand eerst komt. Een verkeerd aangemaakte datumkolom terugveranderen naar de identifiers die hij was, kan helemaal niet. Bewust een beetje werk doen aan de kolommen die datums zijn, is een veel betere positie dan ontdekken dat sommige van de kolommen die dat niet zijn, het wel geworden zijn.

Een tabgescheiden bestand draagt één tabel, dus heeft het werkboek één werkblad. Het is vernoemd naar het bestand met de extensie eraf, ingekort tot de eenendertig tekens die een werkbladnaam mag hebben, wat betekent dat een lange beschrijvende exportnaam wordt afgekapt en niet geweigerd.

De koprij wordt de eerste rij van het blad en legt de kolommen vast: elke latere rij wordt aan die rij gekoppeld, dus krijgt een extra veld dat verderop verschijnt geen eigen kolom. De overtollige waarden worden in plaats daarvan verzameld onder een kolom genaamd `__parsed_extra.0`, wat het blad je vertelt dat de export rafelig is.

Rijen met minder velden dan de kop krijgen een lege cel in plaats van een verschoven waarde, wat de fout is die een naïeve splitser oplevert en de moeilijkste om te zien. Zo zijn de twee richtingen van rafeligheid zichtbaar op verschillende plekken: te weinig velden toont zich als een gat in de rij, te veel als een kolom uiterst rechts met een naam die niets in je bestand had.

Een werkblad houdt 1.048.576 rijen inclusief de kop, en 16.384 kolommen. Dat is een grens van het bestandsformaat en geen van deze omzetting, en geen enkel gereedschap kan een groter blad schrijven.

Expressiematrixen, gebeurtenislogs en volledige zoekwoordexports overschrijden die regel regelmatig. Doen ze dat, dan is het eerlijke antwoord dat een spreadsheet het verkeerde instrument is en niet dat er een grotere converter nodig is: filter of aggregeer de tabel voor het omzetten, of neem hetzelfde bestand mee naar Parquet en bevraag het in DuckDB of pandas, waar een paar miljoen rijen onopvallend is. Een grote export over meerdere bladen verspreiden is technisch mogelijk en analytisch ellendig, want elke formule moet dan weten op welk blad een rij staat.

Een beschrijvings- of annotatiekolom met een regeleinde erin is echt onveilig in een tabgescheiden bestand: de rij eindigt waar de waarde had moeten doorlopen, en elke telling stroomafwaarts klopt niet meer. Een tab geplakt in een waarde doet hetzelfde stiller, en voegt vanaf die rij een kolom toe.

Een werkboek kent die kwetsbaarheid niet. Een regeleinde in een cel is een regeleinde in een cel, en een tab is een gewoon teken. Gaf een tabgescheiden export je inconsistente rijaantallen tussen gereedschappen, dan is omzetten een redelijke diagnose én een oplossing tegelijk — het rijaantal van het werkboek is gezaghebbend op een manier die dat van het tekstbestand nooit was.

Beide helften draaien op deze pagina: de tabgescheiden parser is gewoon JavaScript en de spreadsheetschrijver is een bibliotheek die op verzoek geladen wordt. Geen verzoek draagt het bestand, dus wordt een ongepubliceerde resultatenset, de campagnedata van een klant of een aan een patiënt gekoppelde tabel niet naar een derde partij gekopieerd om geopend te worden.

De grens is geheugen en geen abonnement. De hele tabel staat in één keer in het geheugen terwijl het werkboek wordt samengesteld, dus zetten tientallen megabytes zonder omhaal om en is honderden waar een browsertabblad begint te knijpen — wat ook ongeveer waar het rijenplafond van een werkblad ligt, dus de twee beperkingen bijten meestal samen.

Bewaar het als de volgende stap een script is. R leest het met `read.delim`, pandas met `sep="\t"`, en beide laten je kolomtypen expliciet verklaren, wat een sterkere garantie is dan elke omzetting kan bieden. Een werkboek voegt niets toe aan die pijplijn en neemt het vermogen weg om het te diffen.

Bewaar het sowieso als archiefkopie. De omzetting leest getalachtige waarden als getallen, dus is het werkboek geen byte-getrouwe vastlegging van de export, en het originele bestand is het enige dat een discussie kan beslechten over wat een kolom bevatte. Wordt de tabel herhaaldelijk bevraagd in plaats van gelezen, dan is Parquet de betere bestemming en zet het om vanuit dezelfde bron.