A love letter to the CSV format

Post by Someone » Thu, Mar 27, 2025, 9:18 AM UTC

gpvos wrote: post #419

IanCal wrote: post #403

Earlier quoted context omitted.

Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the years. There aren't vast numbers of different JSON formats. There's practically one and realistically maybe two. Headers are in each line, utf8 has never been an issue for me and quoting and escaping are well defined and obeyed. This is because for datasets, alm…

What's the problem with capital I?

Post by k_bx » Thu, Mar 27, 2025, 9:20 AM UTC

akie wrote: post #417

hiddew wrote: post #415

I think for "untyped" files with records, using the ASCII file, (group) and record separators (hex 1C, 1D and 1E) work nicely. The only constraint is that the content cannot contain these characters, but I found that that is generally no problem in practice. Also the file is less human readable with a simple text editor. For other use cases I would use newline separated JSON. Is has most of the benefits as written in…

I agree that JSONL is the spiritual successor of CSV with most of the benefits and almost none of the drawbacks. It has a downside though: wherever JSON itself is used, it tends to be a few kilobytes at least (from an API response, for example). If you collect those in a JSONL file the lines tend to get verrrry long and difficult to edit. CSV files are more compact. JSONL files are a lot easier to work with though. L…

Post by k_bx » Thu, Mar 27, 2025, 9:25 AM UTC

theoryofx wrote: post #207

k_bx wrote: post #156

I've recently been developing a raspberry pi based solution which works with telemetry logs. First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles. I've since started looking at parquet files – which turned out to not be friendly to append-only operations. I've ended up implementing writing events into ipc files which then pe…

> First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles. Did you try setting `PRAGMA synchronous=FULL` on your connection? This forces fsync() after writes. That should be all that's required if you're using an NVMe SSD. But I believe most microSD cards do not even respect fsync() calls properly and so there's technically no…

Post by yrro » Thu, Mar 27, 2025, 9:27 AM UTC

aorth wrote: post #142

Suppafly wrote: post #102

Earlier quoted context omitted.

That mostly breaks down to "excel is intentionally stupid with csv files if you don't use the import function to open them" along with the normal "don't trust customer input without stripping or escaping it" concerns you'd have with any input.

Someone filed a bug report on a project I work on, saying that it was a security vulnerability that we don't prefix cell values with a single quote (') when the cell content contains certain values like an equal sign (=). They said this can cause Excel to evaluate the content and potentially run unsafe code. I responded that this was Excel's problem, not ours, and that nobody would assign a CVE to our product for suc…

Post by cess11 » Thu, Mar 27, 2025, 9:31 AM UTC

cgio wrote: post #433

cess11 wrote: post #428

Earlier quoted context omitted.

While CSV isn't exactly grammared or standardised like XML I think if it as more schema:d than JSON. There might be data corruption or consistency issues, but there is implicitly a schema: every line is exactly n fields, and the first line might contain field names. When a JSON API turns out to have optional fields it usually shows through trial and error, and unlike CSV it's typically not considered a bug you can ex…

JSON is not schema’d per se and intentionally so. There’s jsonschema which has better expressiveness than inference of a tabular schema, as it can reflect relationships.