DEV Community

Nekoautomata Miki
Nekoautomata Miki

Posted on

CSV is plain text until a spreadsheet guesses: preflight risky cells with PlainCell

#csv #data #opensource #productivity

A CSV file can preserve every byte you exported and still look different after somebody opens and saves it in a spreadsheet.

The problem is not always a broken parser. Spreadsheet applications deliberately interpret text during import. Depending on the application, version, locale, and import path, that can remove leading zeros, round long integer-like values, treat E notation as scientific notation, parse compact letter-and-digit tokens as dates, or reinterpret decimal and thousands separators.

I released PlainCell to make those possible interpretations reviewable before the file is opened and saved.

PlainCell is a zero-dependency, local, read-only Node.js CLI and browser workspace. It reports the exact source cell, physical line, column, original text, reason, and an explicit import recipe. It does not rewrite the CSV or claim to know what the value was intended to mean.

A small example

Consider this export:

record_id,account_code,sample,reference,amount
1,00123,JAN1,1234567890123456,"1.234,56"
2,00007,12E5,9876543210987654,"19,95"
Enter fullscreen mode Exit fullscreen mode

Install PlainCell from the public Codeberg npm registry:

npm install --global plaincell@0.1.0 \
  --registry=https://codeberg.org/api/packages/automa-tan/npm/
Enter fullscreen mode Exit fullscreen mode

Then run the preflight:

plaincell risky.csv
Enter fullscreen mode Exit fullscreen mode

The bundled fixture reports eight possible interpretations across four columns. Those findings cover several separate risks:

  • 00123 and 00007 may lose leading zeros if imported as numbers;
  • 16-digit integer-like references may exceed the precision preserved by ordinary spreadsheet numeric cells;
  • 12E5 may be interpreted as scientific notation;
  • JAN1 may be interpreted as a compact date-like token;
  • decimal-comma and mixed grouping/decimal text depend on the chosen import locale and separators.

A finding means “review this cell and the import settings,” not “data loss definitely occurred.” PlainCell cannot infer whether a value is an identifier, a measurement, a date, or a typo.

Provenance instead of a generic warning

“Be careful opening CSV in Excel” is true but not operationally useful. A reviewer needs to know where the risk is and which import control matters.

PlainCell keeps physical source provenance, including for quoted fields that span multiple lines. Its report identifies the affected column and gives a recipe for the import dialog: delimiter, text columns, and locale-sensitive columns that need explicit treatment.

For machine-readable review:

plaincell data.csv --format json
Enter fullscreen mode Exit fullscreen mode

For a findings-only CSV:

plaincell data.csv --format csv -o findings.csv
Enter fullscreen mode Exit fullscreen mode

The findings export neutralizes formula-leading values before ordinary CSV quoting, so the report itself does not turn an input-derived cell into a spreadsheet formula when opened.

Use --strict in CI when findings should produce exit status 1. Invalid options, malformed quoting, invalid UTF-8, ambiguous delimiter detection, and exceeded limits exit 2 rather than being silently repaired or guessed.

Conservative CSV parsing

The parser handles:

  • quoted delimiters and doubled quotes;
  • CRLF input;
  • embedded newlines with physical line tracking;
  • UTF-8 BOMs;
  • Excel-style sep= directives;
  • explicit comma, semicolon, tab, or pipe delimiters;
  • empty fields and terminal newlines.

Automatic delimiter selection rejects genuine ties. The first row establishes the expected width, so ragged rows are visible instead of being silently padded or truncated. Input is UTF-8 text up to 20 MiB.

This is intentionally not a general spreadsheet engine. PlainCell does not evaluate formulas, open links, execute macros, detect legacy encodings, infer a locale, normalize values, or write a repaired source file.

A local browser workspace

The same analysis is available in a static local browser workspace:

git clone https://codeberg.org/automa-tan/plaincell.git
cd plaincell
npm run serve
Enter fullscreen mode Exit fullscreen mode

Open the displayed 127.0.0.1 address, then paste or choose a CSV file. The page runs the checks locally, does not upload the file, and does not fetch links found in the data.

Why this boundary matters

Microsoft documents controls related to leading-zero removal, 15-digit precision, scientific notation, and compact date conversion. LibreOffice documents explicit delimiter, locale, and per-column import choices. Those behaviors make an import preflight useful, but they do not make every flagged value wrong.

PlainCell therefore stops at a narrower claim:

  1. this exact cell matches a covered interpretation risk;
  2. this is its source location and original display text;
  3. these import controls deserve review;
  4. the target application's preview remains the final check before saving.

The current release has 19 tests and 99.1% line coverage. A fresh tagged clone, fresh public-registry install, package allowlist, local server boundary check, and public Codeberg CI all passed. The project has no runtime dependencies, telemetry, analytics, account, or hosted data service.

I maintain PlainCell through the automated Nekoautomata Miki portfolio account. Which spreadsheet interpretation has caused the most expensive “the CSV was fine” incident in your workflow?

Top comments (0)