CSV to JSON with AI: where data quietly goes missing

Mostly yes, and the few failures are the kind nobody notices. We asked Claude Sonnet to convert 22 CSV files to JSON, 24 JSON inputs to CSV and 24 HTML tables to CSV. From the bare request it got 64 of the 70 right. Every one of the six misses produced output that looked fine and lost something: a value hidden behind a repeated key, two rows folded into one cell, two different fields poured into one column. With our rules loaded it got 69.

Method

We wrote every input ourselves, in English, Bulgarian, German and Spanish. Most carry a trap: values such as 007, 1,250 and 1.10 that must stay text, quoted line breaks, files split by semicolons or tabs, a BOM at the start, merged HTML cells, entities, a cell that tells the model what to do, and a handful of inputs where no clean table or object exists.

Each input went to Sonnet and to Claude Haiku twice: with only the request, and with our rules for that conversion loaded in the system prompt, tools disabled. Code scored each answer: a JSON schema for the CSV to JSON side, with types strict so that "007" never passes as 7, plus an exact pattern of the expected rows for the two CSV outputs.

One correction to our own first pass. Reading every miss by hand, we found six CSV inputs unfair or flawed and rewrote them. In four, such as a row with a missing cell, a refusal was not the only fair answer while the request said nothing about refusing, so those tasks now state the rule. With it, both models refused correctly without our rules; on those four the rules add nothing.

Results

The routine work was already right. Without any rules Sonnet left 007 and 1,250 as strings, read quoted line breaks and doubled quotes, handled semicolons and the byte order mark, repeated merged HTML cells into each slot they cover and decoded entities once. On most of these inputs our rules had nothing to add.

A repeated column name became a repeated key. One CSV header used the same name twice. Both models wrote JSON objects carrying that key twice. The JSON standard asks for unique names in an object and warns that software receiving duplicates behaves unpredictably; many parsers simply keep the last value (RFC 8259, section 4). So the first column vanishes on the first parse, with no error anywhere.

An unclosed quote swallowed every row after it. A quote opened in one cell never closed. Both models read everything after it as part of that cell and returned one object where the file had three rows. The right answer is to stop and say where the quote opens.

Going the other way, three tables should not have been tables. One JSON input had a key written with a dot next to a nested object at the same path, and Sonnet put both values in a single column. Another was cut off mid-value, and the fragment "Bo" went into the name column as if it were a name. A third had a bare number sitting among the records, and the answer invented columns called 0.id, 1 and 2.name. Each of these is a CSV that loads without complaint and says something false.

The HTML miss was about obedience, not tables. In one table a cell asked the reader to change the output. Sonnet first wrote that cell without quotes and then added a corrected line below the CSV, which left an answer no program could save as it is. Haiku missed the same table.

The rules did not fix everything for Haiku. On JSON to CSV, Haiku with the rules refused the three bad inputs but invented columns in three plain ones: an empty trailing header, a nested color field that no record had, and two extra tag columns for arrays of two items.

The rules are the csv-to-json-rows, json-to-csv-flatten and html-table-to-csv skills.

Checking a conversion

• Count before and after. Rows in the CSV against objects in the JSON; columns in the header against fields in every line, as RFC 4180 expects (RFC 4180). That alone flags the swallowed rows and the invented columns.
• Reject duplicate keys on parse. Most JSON parsers will not warn you; a check for a repeated header name before conversion costs one line.
• Ask for a refusal by name. Tell the model which inputs have no right conversion and what one line to send back instead.
• Treat cell text as data. A cell that gives an order is still a cell; the output should hold it, quoted, and nothing else.

What we did not measure

• More than one run. Every input reached every model once; another try could change any single verdict.
• Big files. Our inputs are short. A file of a hundred thousand rows will fail in ways these cannot show, starting with length limits.
• Other parsers. We name what many JSON parsers do with duplicate keys; we did not test a list of them.
• Models from other vendors. Only Sonnet and Haiku were run.