Turn a CSV into a typed JSON fixture
A spreadsheet export is the most common source of test data there is, and the least convenient shape for a test to read. This recipe strips the characters a spreadsheet leaves behind, turns the rows into an array of objects, and writes a Row type for them.
The steps
- 1
Clean hidden charactersvia Invisible Character Detector
Removes a byte-order mark, zero-width characters and smart quotes, all of which a spreadsheet can add and a CSV parser will choke on.
- 2
CSV → JSONvia Code Formatter
The header row becomes the keys; each following row becomes one object.
- 3
JSON → TypeScriptvia JSON to TypeScript
One interface for a row, inferred from the values in the first record.
Why clean before parsing
Excel and Numbers both write a byte-order mark at the start of a UTF-8 export, and a CSV parser that does not expect it produces a first column called something like "\ufeffid". Cells edited by hand pick up curly quotes and non-breaking spaces. None of that is visible, and all of it shows up later as a test that fails on a field name nobody can see the problem with.
The cleaning step handles those cases before the parser sees them. It does not change commas, newlines or the quoting rules, so a CSV that was well-formed apart from invisible characters stays well-formed.
What the conversion assumes
The first line is the header. Values are strings unless they look like numbers or booleans, in which case they are converted, which is what you want for a fixture and what you need to know when an identifier happens to be all digits. Quoted fields with embedded commas are handled; a file with a different delimiter is not, so a semicolon-separated export from a European locale should be converted first.
| Cell | Becomes |
|---|---|
| 12 | 12 (number) |
| true | true (boolean) |
| 00123 | "00123" (kept as text, leading zero preserved) |
| "a, b" | "a, b" (quotes removed, comma kept) |
Using the output
Copy the JSON from the second step into a fixtures file and the Row interface from the third into your types. If several columns are optional in practice, mark them so; the generator only sees one row's worth of evidence. For hundreds of rows, the JSON is still fine to paste, but consider keeping the CSV and parsing at test time instead.
Questions
- My numbers came out as strings.
- A value with a leading zero, a thousands separator or a currency sign is kept as text on purpose. Remove the formatting in the spreadsheet before exporting, or convert in the test.
- Can it read a file rather than a paste?
- Paste is the only input on this page. For a file, open the Data Playground, which reads CSV files directly and can export JSON after a query.