Convert TSV to XLSX

Sequencing pipelines, differential expression tables, annotation files, instrument logs, search console downloads and ad platform reports all come out tab-separated, and most of them offer no alternative. The tab was chosen because gene descriptions, campaign names and page titles are full of commas, and it is the right choice for a transport format.

It is not a working format. The file carries no column types, no widths, no freeze pane and no defence against what happens next, which is that somebody opens it in Excel because that is where the analysis is actually going to happen. The opening is the dangerous step, and it is the one this conversion replaces.

Excel importing text applies its own interpretation to every value, and part of that interpretation is a date parser with a very wide net. `1-2` becomes the second of January. `MARCH1` and `SEPT1` become dates. `2/3` becomes a date. Long digit strings lose their leading zeros and very long ones are re-rendered in scientific notation.

This is not a rumour or an old bug. It was measured across the genomics literature as affecting a substantial fraction of published supplementary tables, and it was ultimately answered by renaming a set of human genes rather than by fixing the spreadsheet — which tells you how difficult it is to undo once it has happened. The reason it cannot be undone is that the original text is gone: the cell holds a date serial number, and there is nothing left recording what characters produced it.

The conversion reads the tab-separated file and writes a workbook, and it does not run Excel’s date parser at any point. A value of `1-2` stays the four characters `1-2`, `SEPT1` stays `SEPT1`, and `2024-03-11` stays that string. Each of them is written into the cell as text, with the cell carrying its type.

That last part is what makes the protection stick. An XLSX cell records what kind of value it holds, so when Excel opens the workbook it displays what is there rather than re-parsing it. The coercion only happens on import of untyped text, and by the time the workbook reaches Excel there is no untyped text left. Open the .tsv directly and you get the coercion; open the .xlsx produced here and you do not.

This is not a blanket refusal to interpret anything, and it would be dishonest to present it as one. A field that reads as a plain number becomes a numeric cell, which is what you want for counts, p-values, fold changes, impressions and costs — they arrive ready to sort and calculate on.

The cost falls on zero-padded codes. `00123` becomes 123, and a plate position or an internal reference that relies on its padding loses it. Values in scientific-looking notation such as `1e5` become 100000. If those columns matter, the defence is in the source: an export with the column quoted, or a non-numeric prefix, comes through as text. It is worth checking a code column in the workbook before doing any work, since a numeric cell is right-aligned and a text cell is left-aligned, which makes a mixed column visible at a glance.

The same refusal that saves `1-2` applies to a genuine date column: `2024-03-11` is written as text, not as a date value, so it will not sort chronologically and date arithmetic will not work on it until it is converted inside Excel.

That is the deliberate side of the trade, and it is the right way round. Turning a text column into dates in Excel is one operation you perform knowingly on a column you have looked at — select the column, Data, Text to Columns, and set the date format explicitly, including which of day and month comes first. Turning a wrongly-created date column back into the identifiers it used to be is not possible at all. Choosing to do a little work on the columns that are dates is a much better position than discovering that some of the columns that are not have become them.

A tab-separated file holds one table, so the workbook has one worksheet. It is named after the file with its extension removed, trimmed to the thirty-one characters a worksheet name is allowed, which means a long descriptive export name is cut rather than rejected.

The header row becomes the first row of the sheet and it fixes the columns: every later row is keyed against it, so an extra field appearing partway down does not get a column of its own. Its surplus values are collected instead under a column named `__parsed_extra.0`, which is the sheet telling you the export is ragged.

Rows with fewer fields than the header get an empty cell rather than a shifted value, which is the failure a naive splitter produces and the one that is hardest to spot. So the two directions of raggedness are visible in different places: too few fields shows as a gap in the row, too many as a column at the far right with a name nothing in your file had.

A worksheet holds 1,048,576 rows including the header, and 16,384 columns. That is a limit of the file format rather than of this conversion, and no tool can write a larger sheet.

Expression matrices, event logs and full keyword exports pass that line regularly. When they do, the honest answer is that a spreadsheet is the wrong instrument and not that a bigger converter is needed: filter or aggregate the table before converting, or take the same file into Parquet and query it in DuckDB or pandas, where a few million rows is unremarkable. Splitting a large export across several sheets is technically possible and analytically miserable, since every formula then has to know which sheet a row is on.

A description or annotation column containing a line break is genuinely unsafe in a tab-separated file: the row ends where the value was meant to continue, and every downstream count is wrong. A tab pasted into a value does the same thing more quietly, adding a column from that row onward.

A workbook has no such fragility. A line break inside a cell is a line break inside a cell, and a tab is an ordinary character. If a tab-separated export has been giving you inconsistent row counts between tools, converting it is a reasonable diagnostic as well as a fix — the workbook’s row count is authoritative in a way the text file’s never was.

Both halves run on this page: the tab-separated parser is plain JavaScript and the spreadsheet writer is a library loaded on demand. No request carries the file, so an unpublished result set, a client’s campaign data or a patient-linked table is not copied to a third party in order to be opened.

The limit is memory rather than a plan. The whole table is held at once while the workbook is assembled, so tens of megabytes converts without ceremony and hundreds is where a browser tab begins to strain — which is also roughly where the row ceiling of a worksheet arrives, so the two constraints tend to bite together.

Keep it if the next step is a script. R reads it with `read.delim`, pandas with `sep="\t"`, and both of them let you declare column types explicitly, which is a stronger guarantee than any conversion can offer. A workbook adds nothing to that pipeline and takes away the ability to diff it.

Keep it as an archive copy in any case. The conversion reads numeric-looking values as numbers, so the workbook is not a byte-faithful record of the export, and the original file is the only thing that can settle an argument about what a column contained. Where the table is going to be queried repeatedly rather than read, Parquet is the better destination and converts from the same source.