A CSV file does not say what type anything is

Here is a three line file whose first line names its own delimiter:

sep=|
A|B
1|2

Four readers, run on this machine, on those exact bytes:

Python csv 3.13.16        1 column:  ['sep=|'] ['A|B'] ['1|2']
LibreOffice 24.2.7.2      1 column:  three text cells, same strings
DuckDB 2.0.0              2 columns named 'sep=' and 'column1'; rows (A,B) and (1,2)
DuckDB-Wasm 1.33.1-dev45.0  2 columns named 'sep=' and 'column1'; rows (A,B) and (1,2)

And a fifth, which I could not run and am taking from the person who measured it: Excel for Microsoft 365 splits the file on | and does not show the first line at all.

So: one file, five readers, three outcomes. Two readers see one column of three strings. Two readers get the delimiter right, name the columns after the declaration line, and quietly turn the real header row into data. One reader does the thing the file was asking for.

The instinct here is to rank them. That instinct is the problem, and it is worth spending a post on, because the ranking does not exist. Every one of those three behaviours is the correct output of a consistent rule. The file does not contain the information that would make any of them wrong.

What the file actually contains

Run the bytes out and there is no mystery about what is in there:

0000000   s   e   p   =   |  \r  \n   A   |   B  \r  \n   1   |   2  \r
0000020  \n

Seventeen bytes. There is no header declaring a delimiter, no schema, no type annotation, no encoding declaration, no marker saying which line is a header. The sep=| line looks like metadata because we read it as English, but to a tokenizer it is a row of text like any other. Whether it means anything is a decision made entirely on the reading side.

This post quotes no standard, deliberately. There is no single document that defines what a field means, which is the whole point, and the clearest statement of that I can cite is shipped with one of the readers above. Python's csv module carries this in its own module docstring, in the interpreter I ran:

Although many CSV files are simple to parse, the format is not formally defined by a stable specification and is subtle enough that parsing lines of a CSV file with something like line.split(",") is bound to fail.

The same docstring then documents the dialect it calls excel, which is the closest thing in a major standard library to a definition of the format Excel reads. It has seven settings: delimiter, quotechar, escapechar, doublequote, skipinitialspace, lineterminator, quoting. All seven are about where fields and records begin and end, and how quoting works. Not one of them is about types. A dialect tells a reader where the fields end. It says nothing about what is inside them, because the file does not say either.

That is the gap every spreadsheet importer has to fill, and filling it is not optional: a grid has to put something in the cell. The question is only what rule you fill it with, and whether anyone can tell afterwards which rule ran.

Two readers, opposite answers, on adjacent inputs

The sharpest case I measured is a column of exponents with a comma in the mantissa, the way a German writer puts 1500 as 1,5E3. Five values, semicolon delimited so the commas are not also separators:

v;n
1,5E3;1
-1,5E3;2
12,3e4;3
1,000E3;4
1,234E2;5

LibreOffice 24.2.7.2, converting to xlsx with the delimiter set to semicolon, writes this into the sheet:

1,5E3     text
-1,5E3    text
12,3e4    text
1,000E3   number 1000000
1,234E2   number 123400

That is the same column the GridCraft issue reports for Excel with a de-DE interface, which I could not run: the first three stay text, the last two become large numbers. LibreOffice ran here with no locale set, so the agreement says two implementations share a rule, not that either applied German number rules. Neither reads 1,5E3 as the 1500 a German writer meant.

Now look at what it is doing. The comma is being read as a thousands group separator, not a decimal point. 1,000E3 parses as the grouped number 1000 followed by an exponent, so 1000 times 10 cubed, which is 1000000. 1,234E2 is 1234 times 100, which is 123400. And 1,5E3 is rejected, because 5 is not a three digit group, so the string is not a grouped number and the cell stays text.

GridCraft 0.7.0 does the exact inverse. From its author's table: 1,5E3 becomes 15000, -1,5E3 becomes -15000, 12,3e4 becomes 1230000, while 1,000E3 and 1,234E2 stay text. The issue's own root cause section explains it in two sentences:

parse_plain checks the thousands groups on the whole string, exponent included [...] In 1,5E3 the group 5E3 has three characters and passes; in 1,000E3 the group 000E3 has five and fails.

Once the group passes, the comma goes as a thousands separator would, and 1,5E3 becomes 15E3, which is 15000; 12,3e4 becomes 123e4, the 1230000 in their table. Two readers, one grouping rule each, applied to different parts of the string, and the result is that each is strict exactly where the other is loose. Hand both of them the same column and one gives you text where the other gives you a number, on a row by row basis, with no overlap in which rows.

DuckDB, meanwhile, refuses the whole column. Both the 2.0.0 build and the DuckDB-Wasm build return VARCHAR and the original strings for all five values. It is not that DuckDB declines to infer types: given 1.5E3 with a decimal point it returns DOUBLE 1500, and given 1000 it returns BIGINT. It simply has no rule under which a comma inside a number means anything, so nothing in that column looks numeric to it.

So, across the four readers named in this section: 1,5E3 is text to three of them and the number 15000 to the fourth. 1,000E3, one line later, is the number 1000000 to two of them and text to the other two. None of them is reading the file wrong. The file never said.

The field that tried to declare itself

There is one convention for marking a field as text, and it is the leading apostrophe. Type '007 into a spreadsheet cell and you get the text 007 rather than the number 7. It is the only part of any of this that a user can reach without a settings dialog.

In a file it is not a convention at all, just a character. All four readers here keep it verbatim, and so does Excel, per the issue author, on both File then Open and From Text/CSV:

Python csv            "'s-Hertogenbosch"  "'t Hooft"  "'007"
DuckDB 2.0.0          "'s-Hertogenbosch"  "'t Hooft"  "'007"
DuckDB-Wasm           "'s-Hertogenbosch"  "'t Hooft"  "'007"
LibreOffice           three text cells, apostrophes present in the stored strings

GridCraft strips it, per its author's table, and on the From Text/CSV path '007 becomes the number 7. The root cause names the line responsible: the fields go through parse_input, "where a leading ' is the typed-text prefix and is stripped".

That is the whole bug in one clause. The apostrophe is a keyboard convention. It is an instruction from a human who is typing into a cell right now, meaning "do not retype what I am about to enter". Applying it to bytes arriving from a file reads an instruction that was never addressed to the importer, consumes the only marker the writer had available, and then types the remainder anyway. The field that went to the most trouble to say "I am text" is the one that comes out as a number.

Worth noting what is in that list besides '007. 's-Hertogenbosch and 't Hooft are a Dutch city and a Dutch surname. The apostrophe is part of the word. An importer that treats a leading apostrophe as a control character silently renames people and places.

A cell that does arithmetic it was not asked to do

The last case is the one with the sharpest edge, and it is the one I expected to be GridCraft specific. It is not. The issue author notes in passing that Excel's own File then Open, the double-click path, turns =1+1 into a formula, while its From Text/CSV keeps it as text.

Two rows, the second with its inner quotes doubled, which is how all four readers here expect them:

f
=1+1
"=HYPERLINK(""report.pdf"",""click"")"

Python and both DuckDB builds hand back two strings, exactly as written. LibreOffice 24.2.7.2, with soffice --headless --convert-to xlsx, writes this into the worksheet XML:

<c r="A2" s="0" t="n"><f aca="false">1+1</f><v>2</v></c>
<c r="A3" s="0" t="str"><f aca="false">HYPERLINK(&quot;report.pdf&quot;,&quot;click&quot;)</f><v>click</v></c>

Those are <f> elements. Live formulas. The cell that held the four characters =1+1 now holds a formula whose cached value is 2, and the second cell holds a live HYPERLINK call whose display text is click. Open that xlsx and neither string is stored as a string anywhere in it. Each has been replaced by something that evaluates to it, which is not the same object and does not behave the same way: it recalculates, it changes when you move it, and it is no longer the string someone exported.

This is a known class of problem with a name, formula injection, and the security dimension of it is well covered elsewhere; I am not going to write a recipe here. The part that belongs in a post about types is simpler and applies even when nobody is being malicious. An exported column of product codes, or of spreadsheet formulas stored as documentation, or of anything at all beginning with =, comes back from a round trip as something that computes. The conversion was lossy and nothing reported a loss.

It is not even one answer per application

The thing that moved this from "readers disagree" to "there is no such thing as the rules" was reading GridCraft's root cause closely. Its author names three separate tokenizers inside the one application: parse_records in crates/xlsx/src/csv.rs for File then Open, split_delimited at crates/engine/src/cmd/data.rs#L855-L884 for Text to Columns, and parse_tsv at crates/engine/src/cmd/edit.rs#L849 for paste. A fourth path, From Text/CSV (from_csv at crates/engine/src/cmd/extra.rs#L857), reuses the File then Open reader but makes its own delimiter guess and then retypes every field.

So the answer to "what does GridCraft do with this file" is not one answer. It depends on whether you used File then Open, or Data then From Text/CSV, or Text to Columns. The author's table shows those paths diverging on the same inputs: File then Open keeps =1+1 as text while From Text/CSV makes it a formula, and File then Open handles quotes inside a field correctly while Text to Columns, per the root cause, "toggles quoting on every " anywhere in the field and drops it".

Excel is no different in kind. By the same author's account its two paths split on =1+1 too, in the opposite direction: File then Open makes the formula and From Text/CSV keeps the text.

The author's proposed fix is the right shape and worth quoting, because it is the only durable answer to any of this: "One tokenizer in crates/xlsx/src/csv.rs, public". One rule, written down, applied everywhere, so that at minimum the behaviour is a property of the application rather than of the menu item.

Where TableRune fits

TableRune imports CSV through DuckDB, and the call is three lines in apps/web/src/lib/engine/duckdb.ts: it registers the file bytes in DuckDB's virtual filesystem and runs CREATE OR REPLACE TABLE ... AS SELECT * FROM read_csv_auto(file), with no options passed. So it inherits DuckDB's rules exactly, and the measurements above for DuckDB-Wasm 1.33.1-dev45.0 are that path: I ran the same call sequence against the same build the app ships.

What that buys, on these four files: no formula is ever created, no apostrophe is ever stripped, and no comma inside a number is reinterpreted. On the exponent column every value comes back as the string that was in the file. That is not TableRune being clever, it is TableRune inheriting a reader that has no rule for those cases.

What it does not buy is any escape from the underlying problem, and there are two specific things worth saying plainly.

First, it does infer types, and it has to. 1.5E3 becomes a DOUBLE, 1000 becomes a BIGINT. The useful part is that the inference is not hidden: DataGrid.svelte renders the DuckDB type next to the column name in every header cell, so the decision the importer made is on screen rather than buried. That is a mitigation, not a fix. A visible wrong guess is still a wrong guess, but you can see it, and the difference between a wrong number you can see and a wrong number you cannot is most of what makes these bugs expensive.

Second, the sep= file costs it the header row. Measured on the shipped build: DuckDB sniffs | as the delimiter, decides the sep=| line is the header, and names the columns sep= and column1, which pushes the real header A|B down into the data. This is the worst failure shape in this post, because everything about the result looks right. The delimiter is correct, the column count is correct, the row count is plausible. Only the names are wrong and one row of data is not data. DuckDB will tell you what it decided if you ask it: calling sniff_csv on that file reports delimiter |, header true, columns sep= and column1. TableRune does not ask, and should.

The honest conclusion from all of it is the one sitting in a comment in TableRune's own source, on the function next to the CSV importer:

Parquet carries its own schema, so types round-trip exactly (no re-inference)

That is the actual answer. Not a better heuristic, not a longer list of locale rules: a format that carries its types, so that nothing has to guess. TableRune exports Parquet, and the comment on that function records that both export formats are free, which the feature gate confirms: the entire ProFeature union in gating.ts is workflows.unlimited and export.charts, so nothing about Parquet is behind the paid tier. If you are moving a table between two tools you control, moving it as Parquet removes this whole class of problem rather than managing it.

If you are writing an importer

Four things, all of which fall out of the above:

• One tokenizer, per GridCraft's own proposed fix. Three code paths mean three behaviours, and your users will find all three.
• Keep the typed-text prefix. A leading apostrophe in a file is text, not an instruction. It is part of 's-Hertogenbosch.
• Never promote a field to a formula. A string that starts with = is a string. If a user wants a formula they can ask for one after the import.
• Show what you inferred, and make the inference reversible. The type is your guess, not the file's content, and the only person who can check it is looking at the grid.

What was verified, and what was not

Measured on this machine on 2026-10-22, on the byte sequences shown above: Python csv 3.13.16; LibreOffice 24.2.7.2 via soffice --headless --convert-to xlsx, with the resulting worksheet XML read directly rather than through a spreadsheet library; DuckDB 2.0.0 through its Python API; and DuckDB-Wasm 1.33.1-dev45.0, the build in TableRune's lockfile, which reports engine version v1.5.1.

Not verified here, and taken from the issue author:

• Every Excel result in this post. There is no Excel on this machine. The author ran Excel for Microsoft 365 on Windows with a de-DE interface and gives no build number. The exponent column is the only Excel claim that an independent reader reproduced here, and LibreOffice is not Excel.
• Every GridCraft result, and the code references in the root cause section. Those are quoted from the issue, not read from the repository.

Boundaries on what I did run. LibreOffice was driven through the command line converter, not the interactive import dialog, whose options are different and whose defaults may be too. It ran with no locale set, which matters for exactly the numbers this post is about, so the exponent results are reported with the delimiter given explicitly and no other filter option changed. DuckDB-Wasm was run under Node with the package's Node runtime rather than in a browser Web Worker, which is where TableRune runs it. And the TableRune claims about type display and about the feature gate are read from source at the current tip of main, not observed in a running browser.