What's the most popular file format in the world? As a databases guy, I wish I could say it's Parquet or SQLite. But real data in the wild almost always live in a CSV file. The work of a data scientist therefore usually starts with loading data from CSV into whatever tool they are using. The popularity of CSV is mostly thanks to its simplicity: data is organized by rows, and each row consists of a number of cells separated by commas. Simple enough, right? But that's not all - if there are commas within the data of a cell, that cell must be wrapped in double quotes; and if there are also quotes in that cell, those quotes should be escaped. That's not hard to do, but also very easy to forget. One of the most common mistakes in preparing CSV data is to simply concatenate cell contents separated by commas:
cells = ["apple", "banana", "cherry"]
row = ",".join(cells)...because that's literally what the name "CSV" suggests. But this
can result in outputs that are almost impossible to parse. Suppose we
have two values, Tom, Holland and Zendaya for
a table over married couples. Then after concatenating, we get:
husband,wife
Tom, Holland, ZendayaHow do we know if we should read it as Tom, Holland and
Zendaya, or as Tom and
Holland, Zendaya? Maybe Tom decided to drop his last name
at the same time as Zendaya decided to take his name. We'll never
know.
But in many cases we can know, for example:
name,networth
Zendaya,40,000,000From the values we can guess name must be
Zendaya and not Zendaya,40, and the networth
is 40,000,000.
In general, semantic information in the text can help us determine which parts of it correspond to which column. A natural idea is then to shove the entire CSV file into an AI model and ask it to break up the words. But CSV files can span millions of rows, with each row containing hundreds of characters. Even with the cheapest models today it can cost hundreds if not thousands of dollars just to process a single file.
Luckily, our problem is a perfect fit for a new class of so-called decision models. You might have heard of Jev from TypeSafe that was released not too long ago. Like LLMs, these models can take any text as input, but instead of producing text, they can only output short responses: "yes" or "no", one of several choices, or a number as a score of something. This also allows them to be orders of magnitude more efficient than LLMs, and therefore orders of magnitude cheaper.
Although there are probably better approaches to the CSV parsing problem like training a specialized classification model, I was curious how well decision models can do the job. So I hacked together some quick experiments over an afternoon and here's what came out (the following summary is generated by Claude and edited by me).
I parse each line with Python's csv module in strict
mode, and only when that fails does the model get involved. For a broken
record, every comma becomes one yes/no question "is this comma a
separator, or part of a text field?". The shared state
(context) describes the file: the column names, twenty sample values for
each column taken from clean rows, and the record itself:
CSV file with 2 columns: name, networth. Delimiter ',', quote ".
Sample values of column name: 'Tom Holland'; 'Timothée Chalamet'; ...
Sample values of column networth: '25,000,000'; '30,000,000'; ...
Attempting to parse: 'Zendaya,40,000,000'Each question then shows only fourteen characters on either side of a comma:
Is the ',' between 'Zendaya,40' and '000,000' a separator between two fields?
true: a field separator
false: part of the text of one valueLine breaks get the same treatment ("is this the end of a record?"), which is how a value that spans lines gets stitched back together.
For ground truth I took clean data whose cells naturally contain
commas and line breaks (the Project Gutenberg catalog with its
Surname, Given, dates authors, NYC job postings, and Hacker
News comments), and wrote each record the naive way,
",".join(cells). That gives 860 records and 32,571 labeled
decisions. On 100 records per source, Jev gets 98% of decisions right:
98.3% of real separators, 97.9% of commas that are text, and 100% of
line breaks.
Interestingly, only a small portion of rows are correctly parsed, because that requires the model to make a perfect decision for every comma in the row. A Gutenberg row needs about thirteen correct answers in a row and a job posting over a hundred: 52% of Gutenberg rows, 27% of job postings and 85% of Hacker News comments come out exactly right.
How much does it cost to parse a CSV with Jev? A run of 300 records, 12,044 decisions, takes 15 seconds and 1.8 million input tokens, which at Jev's $0.042 per million is eight cents. Since a record that parses cleanly costs nothing, cleaning the entire Gutenberg catalog (79,474 rows) would be about $11, all of NYC's job postings about $1.40, and Hacker News about $73 per million comments. I think that's pretty good value to get back clean data doing almost no work!
I also tried Ollaya, which serves
open decision models locally. Its recommended winnow:e4b (a
7.5B model) got 82% of decisions with the same prompt, but only 69% of
the textual commas and 31% of the Gutenberg names, so almost no record
survived intact. Most job postings did not even fit its context window.
It also took 19 minutes on an M5 laptop for a subset that Jev finished
in 5 seconds. The smaller laya:en answered the polarity of
the question rather than its content, and was of no use at all. So for
now, at least on this task, the hosted model is both far more accurate
and, given the cost above, hard to beat on price.
The code and the benchmark are at remysucre/csv-inhaler.