Data format converter

These four formats hold data in incompatible shapes. JSON is a tree, CSV is a grid, YAML is a tree with type coercion attached, and a run of INSERT statements is a grid wrapped in syntax. Moving between them always costs something, and knowing what it costs is most of the job.

JSON and CSV

Going to CSV, the header is the union of every key across the whole array. Taking the first object as the template instead is the difference between a correct export and a silently lossy one: if row one has three fields and row two has a fourth, the fourth disappears and nobody notices until the data is needed. A record missing a field gives an empty cell, which is visible and recoverable.

Nested objects and arrays have no good answer, because CSV is flat and JSON is not. They are serialised as JSON text inside the cell, which at least preserves them. If you want real columns, flatten the JSON to dotted paths first and the spreadsheet is far more useful. Coming back the other way the problem inverts: CSV carries no types at all, so everything arrives as a string unless something guesses, and guessing is how a part number with a leading zero becomes a float. One Excel quirk is worth knowing before you blame the file. A CSV whose first header is exactly ID is treated by Excel as a SYLK document and the first column comes out mangled. Rename the column, or open the file through the import dialog.

JSON and YAML

This is the one pair where one direction is genuinely safe. Every JSON value has exactly one YAML representation, so serialising has no ambiguity to resolve and the only question is how much of it needs quotes. The answer is: anything that would read back as something else. A JSON string "1.0" written unquoted comes back as the number 1, which loses the trailing zero and changes the type, and for a version number that is a real bug. The same applies to "true", "no", "null" and anything starting with a digit, so all of them are quoted here. The rule applies to the keys too, which is easy to forget: an object key yes written bare reads back as the boolean true, a key beginning with a # opens a comment and takes its own line away, and a key containing a colon and a space splits in the wrong place. All three are quoted.

Parsing YAML is the direction with the ambiguity, and the type resolution follows YAML 1.1, which is where the famous surprises live. The words yes, no, on and off are booleans, which is why the Norway country code NO becomes false in a YAML file unless it is quoted. That is the specification behaving as specified, and it has broken real deployments. Anchors, aliases, tags and multi-document streams are refused with a message instead of being half-supported, because getting those subtly wrong is worse than declining them.

The SQL directions

Escaping is the whole job going into SQL. An apostrophe is escaped by doubling it, so O'Brien has to become 'O''Brien', and getting that wrong is not a formatting bug, it is the exact mechanism of SQL injection. Every value here goes through that escape. Values that look numeric or boolean are written bare so a typed column accepts them, and an empty cell becomes NULL instead of an empty string; put quotes around it in the CSV if you genuinely want an empty string.

Batched or not is a real choice. One statement per row is easy to read and easy to re-run after a failure. A single statement carrying many value tuples is dramatically faster, because the round trip and the parse happen once instead of a thousand times, and for an import of any size that is what you want. Keep the batches to a few thousand rows, since most servers cap the size of a single statement. And to say it plainly: statements built by string concatenation are for a one-off import you run by hand. An application should use parameters, which move the escaping into the driver where it cannot be forgotten.

Reading statements back out comes up more than you would expect: a dump file, a migration someone wrote by hand, a set of statements pasted into a ticket. This is not a SQL parser. It looks for INSERT INTO table (columns) VALUES and reads the tuples that follow, which covers what generators emit and what people write by hand. A tuple containing an expression, VALUES (1, NOW()), comes through as the literal text NOW(), because evaluating it would need a database. The header comes from the first statement, so a dump whose statements carry different column lists is worth checking before you trust the result.