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

Time to retire the CSV?

bitsondisk.com

Post reply on HN
Print view View original

281–290 of 594 posts

Re: Time to retire the CSV?

#281

Post by dasyatidprime »

da_chicken wrote: post #220
masklinn wrote: post #164

Earlier quoted context omitted.

> No, SQLite's dynamic data types would silently coerce data just like opening a CSV directly with Excel does. SQLite's "dynamic data types" coerce data on input, not output. Once the data is in sqlite the way you wanted it, excel has no interpretation to perform, except insofar as really really wanting dates. > The advantage of CSV is that it's as accurate as your plain text representation of your data can be. Yeah…

> SQLite's "dynamic data types" coerce data on input, not output. That's not relevant. If a data format coerces data when it's stored, it's still not a data format suitable for serialization.

It's strictly dependent on the table definitions you use. A column with no type (the simplest way to use them) will take data in any of the natively supported types—whatever you actually insert in your SQL statement—and will not attempt any translation.¹

If you do add column types to your tables as in traditional databases, then of course you should expect some varying behavior based on that.

I can imagine some potential for issues from people expecting to insert exact decimal numbers and choosing floating-point by accident, perhaps, or using integer types for actually-text numeric ID fields. If you go all-strings on input with no column affinity, then you'll get the same strings on output, though, so that's isomorphic to good-CSV.

¹ https://www.sqlite.org/datatype3.html – §3.1 “If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.” + §3 “A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.”

Re: Time to retire the CSV?

#282

Post by derefr »

cbsmith wrote: post #138
derefr wrote: post #74

Earlier quoted context omitted.

You're comparing CSVs to other spreadsheet document formats. But a CSV is not a spreadsheet. A CSV is raw data. (It's data that is restricted to a shape that enables it to be easily imported into a spreadsheet—but data nevertheless.) As such, it should be compared to other data formats—e.g. YAML, JSON Lines, etc. These other data formats all win on your #2 against CSV, as CSV is actually horrible at parse-time vs. ot…

> There's also Avro, which fails your point #1 (it's a binary format) but that binary format is a lossless alternate encoding of what's canonically a JSON document, and there are both simple CLI tools / and small, free, high-quality libraries that can map back and forth between the "raw" JSON document and the Avro-encoded file. At any time, you can decode the Avro-encoded file to text, to examine/modify it in a text…

> Yes Avro supports a JSON encoding, but it's not canonical.

To be clear, I'm not talking about using an Avro library to encode data to JSON. I'm saying that when you decode an Avro document, the result that comes out — presuming you don't tell the Avro decoder anything special about custom types your runtime supports and how it should map them — is a JSON document.

Where, by "JSON document" here, I don't mean "a JSON-encoded text string", but rather an in-memory ADT that has the exact set of types that exist in JSON, no more and no less. The sum-type of (JSONArray | JSONObject | String | Integer | Float | true | false | null). The "top" expression type recognized by a JSON parser. Some might call such a document a "JSON DOM." But usually it's a "JSON document", same as how the ADT you get by parsing XML is usually referred to as an "XML document."

Or, to put that another way, Avro is a way to encode JSON-typed data, just as "JSON text", or https://bsonspec.org/, is a way to encode JSON-typed data. They're all alternative encodings that have equivalent lossless encoding power over the same supported inputs.

Re: Time to retire the CSV?

#283

Post by simonw »

One of my goals with https://datasette.io is to offer a better alternative for publishing data than sharing a link to a CSV file.

The trick is that if you compile data into a SQLite file and then deploy the Datasette web application with a bundled copy of that database file, users who need CSV can still have it: every Datasette table and query offers a CSV export.

But... you can also get the data out as JSON. Or you can reshape it (rename columns etc) with a SQL query and export the new shape.

Or you can install plugins like https://datasette.io/plugins/datasette-yaml or https://datasette.io/plugins/datasette-ics or https://datasette.io/plugins/datasette-atom to enable other formats.

Re: Time to retire the CSV?

#284

Post by asdff »

turtlebits wrote: post #119
Sanzig wrote: post #44

Earlier quoted context omitted.

As I mentioned down-thread, I can generate a CSV with a couple of fprintf statements and a loop. I definitely can't do that with .xlsx. There is almost zero friction to bolting CSV export capability to an existing system, which is part of why it's so popular.

You can write what "looks" like CSV to you, but there are no guarantees it will import correctly. The problem is 10x worse when you get CSV from one source and rely on another process to load it. I fought this problem for several days going from NetSuite to Snowflake via CSV.

Can you give an example? The rules for CSV files are so simple I'm struggling to imagine a case where something looks correct but in fact isn't correct.

Re: Time to retire the CSV?

#285

Post by wombatpm »

cbsmith wrote: post #212
tw04 wrote: post #198

Earlier quoted context omitted.

The entire article is about replacing CSVs for exchanging data exported from Excel... so why wouldn't he be picking data formats based on being able to load and edit them in Excel? If you're trying to solve this problem in a way that EXCLUDES Excel, you're already doomed. The business world will laugh at you and continue on their merry CSV way. >The biggest and most thorny problem to solve is the people problem: how…

> The entire article is about replacing CSVs for exchanging data exported from Excel... No, it's not. It's about replacing CSVs for exchanging data. It mentions that CSVs often are the product of someone exporting data from a spreadsheet or doing a table dump, and how just doing that tends to create a ton of problems, but Excel is an example, not the subject matter of the article. > The business world will laugh at y…

TSV solve a lot of the pain

Re: Time to retire the CSV?

#286

Post by kazinator »

Let me define a sane CSV standard:

1. Encoding is UTF-8.

2. Header line with column names is mandatory. If there are no column names, the first line must be blank.

3. Each record is a line. A line is defined according to the operating platform's text file format.

4. In the light of (3) CSV does not dictate line endings and does not address the conversion issue of text files from one platform being transferred to another platform for processing. This consideration is a general text issue, off-topic to CSV.

5. CSV consists of items separated by quotes. An item may be:

5. a) a JSON number, surrounded by optional whitespace. Such an object may be specially recognized as a number by the CSV-processing implementation.

5. b) the symbol true, false or nil, optionally surrounded by whitespace. These symbols may have a distinct meaning from "true", "false" or "nil" strings in the CSV-processing implementation.

5. c) a JSON string literal

5. d) any sequence of printable and whitespace characters, other than comma or quote, including empty sequence.

6. In the case of (5) (d), the sequence is interpreted as a character string, after the removal of leading and trailing whitespace. (To preserve leading and trailing whitespace in a datum, a JSON literal must be used.)

7. In (5), whitespace refers to the ASCII space (32) and TAB (9) character. If there are any other control characters, the processing behavior is implementation-defined. Arbitrary character codes may be encoded using JSON literals.

Re: Time to retire the CSV?

#287

Post by asdff »

madaxe_again wrote: post #50
fighterpilot wrote: post #32

Earlier quoted context omitted.

I'm a human and I love CSVs. What other format can I open in a spreadsheet, access through cat/grep/awk, and easily load into any programming language? Any other format has to sacrifice one of these three things, and that's bad. It's this trifecta that makes it so versatile and human friendly.

Developer friendly. Not human friendly. I used to receive CSVs of product data from clients, which they often handcrafted or manipulated by hand, and inevitably, and I mean inevitably , broke. Of course, excel will make just as much of a mess - when the client hands back their sheet with all of their UPCs expressed as exponents and æ€ ligatures jammed next to every apostrophe, there’s no guessing as to what happened.…

Honestly though, if people are doing that with CSVs there is no system where they won't also go off the reservation and do something insane with an excel file or sqlite or whatever. At least with CSVs there are common lines of code all around the internet to wrangle all sorts of issues.

Re: Time to retire the CSV?

#288

Post by wombatpm »

cbsmith wrote: post #212
tw04 wrote: post #198

Earlier quoted context omitted.

The entire article is about replacing CSVs for exchanging data exported from Excel... so why wouldn't he be picking data formats based on being able to load and edit them in Excel? If you're trying to solve this problem in a way that EXCLUDES Excel, you're already doomed. The business world will laugh at you and continue on their merry CSV way. >The biggest and most thorny problem to solve is the people problem: how…

> The entire article is about replacing CSVs for exchanging data exported from Excel... No, it's not. It's about replacing CSVs for exchanging data. It mentions that CSVs often are the product of someone exporting data from a spreadsheet or doing a table dump, and how just doing that tends to create a ton of problems, but Excel is an example, not the subject matter of the article. > The business world will laugh at y…

Of course there is a old solution in the ANSI character set. File, Record, Group and Unit separator characters

Re: Time to retire the CSV?

#289

Post by dbreunig »

They answer their own question:

>Most of us don’t use punch-cards anymore, but that ease of authorship remains one of CSV’s most attractive qualities. CSVs can be read and written by just about anything, even if that thing doesn’t know about the CSV format itself.

Yes, we keep CSVs. If you care about metadata and incredibly strict spec compliance, then yes: avro, parquet, json, whatever. But most CSV usage is small data, where the ease of usage, creation, and evaluation wins.

One of the problems with CSVs he cites is a great reason why I like CSVs:

>CSVs often begin life as exported spreadsheets or table dumps from legacy databases, and often end life as a pile of undifferentiated files in a data lake, awaiting the restoration of their precious metadata so they can be organized and mined for insights.

A benefit of a CSV is a skilled or unskilled operator can evaluate these piles of aging data. Parquet? Even SQLite? Not so much.

For small-to-medium sized datasets, CSV is great and accessible to a wider user base. If you're relying on CSV to preserve structure and meta, then meh.

Re: Time to retire the CSV?

#290

Post by asdff »

habitue wrote: post #7
izietto wrote: post #3

"In favour of what?", that is the matter. CSV is a format more for humans and less for machines, but that is the use case: a format that is good enough to be compiled by humans and read by machines. At the moment there aren't many alternatives.

Objectively, CSV is terrible for humans despite being a plaintext format. No one reads CSVs: they're incomprehensible since the columns are not aligned with the headings. (You might be drawing an analogy with JSON, which is often human readable because it puts the keys right there next to the values). The best that can be said for its simplicity is that it's easy to write code that can dump data out in CSV format (an…

The columns are aligned with the headings if you use the tool for the job, like R or python or awk or insert favorite cli parsing method here.
Post reply on HN