Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A love letter to the CSV format

github.com

Post reply on HN
Print view View original

311–320 of 711 posts

Re: A love letter to the CSV format

#311

Post by nukem222 »

PaulHoule wrote: post #310
nukem222 wrote: post #294

Earlier quoted context omitted.

> It's simple My experience has indicated the exact opposite. CSVs are the only "structured" format nobody can claim to parse 100% (ok probably not true thinking about html etc, just take this as hyperbole.) Just use a well-specified format and save your brain-cells. Occasionally, we must work with people who can only export to csv. This does not imply csv is a reasonable way to represent data compared to other optio…

The HTML 5 spec says exactly how you're supposed to deal with broken HTML files.

Yes, that is a single spec with correspondingly-small importance. Generally parsing html remains extremely difficult.

Re: A love letter to the CSV format

#312

Post by zeroimpl »

klysm wrote: post #233
koolba wrote: post #63

Earlier quoted context omitted.

JSON serialized without extra white space with one line per record is superior to CSV. If you want CSV-ish, enforce an array of strings for each record. Or go further with actual objects and non-string types. You can even jump to an arbitrary point and then seek till you see an actual new line as it’s always a record boundary. It’s not that CSV is an invalid format. It’s that libraries and tools to parse CSV tend to…

You serialize the keys on every row which is a bit inefficient but it’s a text format anyway

Space-wise, as long as you compress it, it's not going to make any difference. I suspect a JSON parser is a bit slower than a CSV parser, but the slight extra CPU usage is probably worth the benefits that come with JSON.

Re: A love letter to the CSV format

#313

Post by didgetmaster »

I have written a new database system that will convert CSV, JSON, and XML files into relational tables.

On of the biggest challenges to CSV files is the lack of data types on the header line that could help determine the schema for the table.

For example a file containing customer data might have a column for a Zip Code. Do you make the column type a number or a string? The first thousand rows might have just 5 digit numbers (e.g. 90210) but suddenly get to rows with the expanded format (e.g. 12345-1234) which can't be stored in an integer column.

Re: A love letter to the CSV format

#314

Post by LegionMammal978 »

zzo38computer wrote: post #228
LegionMammal978 wrote: post #173

Earlier quoted context omitted.

> The entire argument against ASCII Delimited Text boils down to "No one bothered to support it in popular editors back in 1984. Because I grew up without it, it is impossible to imagine supporting it today." There's also the argument of "Now you have two byte values that cannot be allowed to appear in a record under any circumstances. (E.g., incoming data from uncontrolled sources MUST be sanitized to reject or repl…

One benefit of binary formats is not needing the escaping.

Length-delimited binary formats do not need escaping. But the usual "ASCII Delimited Text" proposal just uses two unprintable bytes as record and line separators, and the signalling is all in-band.

This means that records must not contain either of those two bytes, or else the format of the table will be corrupted. And unless you're producing the data yourself, this means you have to sanitize the data before adding it, and have a policy for how to respond to invalid data. But maintaining a proper sanitization layer has historically been finicky: just look at all the XSS vulnerabilities out there.

If you're creating a binary format, you can easily design it to hold arbitrary data without escaping. But just taking a text format and swapping out the delimiters does not achieve this goal.

Re: A love letter to the CSV format

#315

Post by fsckboy »

didgetmaster wrote: post #313

I have written a new database system that will convert CSV, JSON, and XML files into relational tables. On of the biggest challenges to CSV files is the lack of data types on the header line that could help determine the schema for the table. For example a file containing customer data might have a column for a Zip Code. Do you make the column type a number or a string? The first thousand rows might have just 5 digit…

csv does not stop you from making the first line be column headers, with implied data types, you just have to comma separate them!

Re: A love letter to the CSV format

#316

Post by miningape »

meepmorp wrote: post #189
hajile wrote: post #169

Earlier quoted context omitted.

CSV is lists of lists of fixed length. JSON is lists of lists of any length and groups of key/value pairs (basically lisp S-expressions with lots of unnecessary syntax). This makes it a superset of CSV's capabilities. JSON fundamentally IS made to represent tabular data, but it's made to represent key-value groups too. Why make it able to represent tabular data if that's not an intended use?

> CSV is lists of lists of fixed length. I'd definitely put that in my list of falsehoods programmers believe about CSV files.

yeah if I had a cookie for every time I've had to deal with this I'd have maybe 10 cookies - it's not a lot but it's more than it should be.

Re: A love letter to the CSV format

#318

Post by emmelaich »

BeFlatXIII wrote: post #300
emmelaich wrote: post #72

Earlier quoted context omitted.

pipe (|) separated or gtfo! sqlite3 gets it right.

What do you like so much about the pipe?

Visually similar to column separator in a spreadsheet.

Less likely to appear in normal data. Of course you have to escape it but at the very least the data looks less noisy.

Re: A love letter to the CSV format

#319

Post by theLiminator »

sitkack wrote: post #304
theLiminator wrote: post #234

Earlier quoted context omitted.

I think that might make sense ingest side, but that's very expensive to deal with if you're doing anything remotely large. I think sinking into something like delta-lake or iceberg probably makes sense at scale. But yeah, I definitely agree that CSV is not great.

JSONL as a replacement for CSV, you shouldn't be using CSV as format for long term storage or querying, it has so many downsides and nearly zero upsides. JSONL when compressed with zstd, most of "expensive if large" disappears as well. Generating and consuming JSONL can easily be in the GB/s range.

I mean on the querying side. Parquet's ability to skip rowgroups and even pages, and paired with iceberg or delta can make the difference between being able to run your queries at all versus needing to scale up dramatically.

Re: A love letter to the CSV format

#320

Post by hajile »

BeFlatXIII wrote: post #300
emmelaich wrote: post #72

Earlier quoted context omitted.

pipe (|) separated or gtfo! sqlite3 gets it right.

What do you like so much about the pipe?

I think pipe is better too.

Typical latin fonts divide characters into three heights: short like "e" or "m", tall like "l" or "P" and deep like "j" or "y". As you may notice, letters only use one or two of these three sections.

Pipe is unique in that it uses all three at the same time from the very top to the very bottom. No matter what latin character you put next to it, it remains distinct. This makes the separators relatively easy to spot.

Pipe is a particularly uncommon character in normal text while commas, spaces, semicolons, etc are quite common. This means you don't need to escape it very often. With an escapable pipe, an escapable newline, and unicode escapes ("\|", "\n", and "\uXXXX") you can handle pretty much everything tabular with minimal extra characters or parsing difficulty.

This in turn means that you can theoretically differentiate between different basic types of data stored within each entry without too much difficulty. You could even embed JSON inside it as long as you escape pipes and newlines.

    "string"|123|128i8|12.3f64|false|[1,2,3,4]|{key: "val"}|2025-03-26T11:45:46−12:00
Maybe someone should type this up into a .psv file format (maybe it already exists).
Post reply on HN