CSVs: The Good, the Bad, and the Ugly | Hacker News Reader

CSVs: The Good, the Bad, and the Ugly

alexgaynor.net

89 pointsgoranmoomin104 comments

CSVs are the worst and only option for data exchange in 99% of the cases.

Most CSVs travel through excel at some point in their lives, so we need to talk about excel:

Excel neither reads nor writes UTF-8 CSV files without jumping through hoops.

Excel saves files in different character sets on MacOS and Windows.

Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's)

Excel will try and interpret date fields and write them out in a different format.

Outside of Excel:

There is no way to know what character set a CSV file is encoded in. You can only guess.

UTF BOM - sometimes there, sometimes not.

If the file is malformed but still opens in Excel it is a bug in your CSV parser, and not a problem with the producer of the file (so they will tell you)

Most CSV libraries are extremely buggy and cannot generate CSV to spec.

Most CSV libraries are extremely buggy and cannot read CSV that meets spec.

Many programmers think CSV is easy and do not use a library go generate or read CSV producing even worse results than their language's buggy CSV library.

The spec is just a suggestion

Nobody reads the spec

That's just the tip of the iceberg

> Most CSV libraries are extremely buggy

Examples? (Of libraries and bugs)

op is being very hyperbolic. most widely used and supported csv libraries are not nearly as deficient as op claims.

golang builtin doesnt handle UTF BOM well . have to preprocess the io.Reader

Seems reasonable to me: this way, you don't have to duplicate the BOM stripping in the decoders of every single format you want to support.

It's also more general as there are other commonly-needed transformations that have an even weaker case for explicit support in the CSV library (charset conversion, dropping other bogus bytes, ...).

it's a stark difference compared to, say, https://www.papaparse.com/ in javascript which pretty much does anything it can to parse a file

There's a spec?

If nothing else there is RFC4180 (which is the spec that backs the mime type).

Yes, I experienced a lot of pain while working with CSVs in UTF-16, turns out some libraries can't handle it.

Also, CSVs don't really scale because parsing generic CSV that may have quote and line-end inside a field is inherently single-threaded, so once you get to multi-gigabyte CSVs, and things start to go slow, there's nothing you can do.

> Excel saves files in different character sets on MacOS and Windows.

Doesn't excel also use different separators depending on the locale? I recall once it would create semicolon-separated files if the locale uses `,` as decimal separator, and will put commas in your file in that case.

Yes it does. Excel does not generate valid CSV (as per RFC4180) on e.g. German computers.

This is why I switched to TSV. The risks are too high.

(Yes, I know about ASCII delimiters, but IMHO /t and /n are good enough for numbers-first files, especially considering how most keyboards don't feature the aforementioned delimiters, and hardly anyone has even heard of them.)

https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

With commas one uses "CSV" as the file extension, and with tabs presumably TSV.

Is there a file extension and MIME type for ASCII-delimited files? ASV?

CSVs are ascii-delimited. Do you mean files using US as field separator?

.gop?

Yes. If I want to use the 0x1c to 0x1f in a delimited file, how would I signal by file extension (and/or MIME type) that is what is happening?

TSV is a lot better, but still isn't great for tabs in fields (rare) and line breaks in fields (more common). One nice characteristic of tab-delimited is you can typically copy all the text from a text editor and paste right into a spreadsheet.

I'm fixing this with a smooth upgrade to QTSV:

https://news.ycombinator.com/edit?id=25022836

Each cell is QSN, which can contain tabs and newlines:

http://www.oilshell.org/release/latest/doc/qsn.html

The nice thing about this is that you can import QTSV into ANY program that accepts TSV. A QTSV file is syntactically valid TSV.

You may have to do an extra step to dequote certain fields, but that's better than a completely new format that doesn't import at all.

Let me know if you want to help :)

>Excel neither reads nor writes UTF-8 CSV files without jumping through hoops.

It's literally as simple as selecting "csv utf-8" when saving. When opening excel also handles it automatically if it has BOM.

It shouldn't need a BOM. Besides, UTF-8 doesn't need a BOM at all.

It doesn't, but only if you assume all text is utf-8. You can't do that on windows because there's plenty of legacy files that use encoding like windows-1252 which are incompatible with utf-8.

Start by converting them to UTF-8. As a metaphor : would you want to work with files that can only be opened properly in, say, Lotus 1-2-3 ?

The time to drop that support is long passed. UTF-8 has won, the value of pandering to these ancient codepages versus the cost of choking and displaying mojibake just because someone dared to add a kanji character to a CSV should be clear by now.

And what happens to all the legacy office versions out there, or all the legacy csvs floating around? What you described works fine on a rolling release distro, but would be unacceptable for enterprise software. The current approach (opt in utf-8 with BOM to disambiguate between utf-8 and non-utf8) files is the best option that doesn't piss off their customers.

UTF-8 has won on the web and file storage. For programming (C#, probably Java too, the Windows API) it’s UTF-16. Good point is it’s an Unicode encoding, bad point is that is variable-size, which look fixed-size for common characters.

> For programming (C#, probably Java too, the Windows API) it’s UTF-16.

For programming it's UTF8, except for Windows-centric developers (and Microsoft technologies).

Java uses WTF16 internally but rarely assumes an external charset, and when it does, older API tend to use the default charset (which is generally ascii-compatible at best: on western windows it's commonly windows-1252, though it's been UTF-8 for years on most unices). Newer APIs like Files.newBufferedReader(Path) straight go with UTF-8.

It’s a relatively new feature (and it outputs UTF-8-BOM). If you work with one-time purchase versions, it’s only available in Office 2019. It’s been in Office 365 for a bit longer. But if you (or whoever sends you the CSV file) use older one-time-purchase versions of Excel, the option is not available. Even if you do have the latest version of Excel, the old option is still there, so someone might save a file as Windows-XXXX accidentally.

Those are hoops that IME you can't expect people not in your organization to jump through.

The worst sin of Excel is the import dialog, which doesn’t scroll on MacOS and if updated since the last revision in 1997 would eliminate most issues.

Good point about Excel, there's not much point discussing CSVs and leaving Excel out of the discussion

UTF-8 or GTFO.

(Without BOM, you heretic.)

Please tell me that you're talking about Excel 2003 ?

Microsoft Office worships the concept of backwards compatibility.

Outlook renders HTML emails with a buggy and incomplete implementation of HTML 3.2 that has had approximately two changes in the last twenty years (one was to support high DPI, the other I can’t remember). There was that time they switched to using Trident (IE) in Outlook 2000–2003, but they switched back to MSO (Word) in 2007 for terrible reasons (“uh… security, yes, security”; their explanation was atrocious and never made a lick of sense to me; the original article talking about it went down a few years ago, if I recall correctly from last time I tried to find it).

Excel sticks with known-bad and known-stupid behaviour because people depend on it. I can’t think of an example off-hand, but they literally have help pages saying “if you encounter this specific bug, here’s how to work around it”, because writing that help page was easier and/or safer than actually fixing the bug.

A few months ago, scientists even renamed some human genes to stop MS Excel from misreading them as dates: https://news.ycombinator.com/item?id=24070385

Backwards compatibility doesn't have to mean to give up on forward compatibility !

(How do browsers deal with that ?)

Excel even implemented lotus 1-2-3 bugs to ensure compatibility: see 1900-02-29 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...

>Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's)

Fyi for those unaware... many other tools other than MS Excel also remove leading zeros and interpret as number.

Examples of users asking questions on how to preserve them in other software:

- Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdat...

- LibreOffice: https://ask.libreoffice.org/en/question/246295/how-can-i-imp...

- Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-lea...

- R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-cs...

- Tableau: https://community.tableau.com/s/question/0D54T00000C5i2iSAB/...

Everybody complains about the misinterpretation of leading zeros and blame Microsoft Excel but for some reason, the programmers of the other non-Excel software have deliberately coded the same default behavior.

> for some reason

The reason is Excel.

>The reason is Excel.

I've thought about that and it seems plausible but I'm not fully convinced.

As another example, Google Sheets does not blindly copy Excel behavior of converting scientific gene names into a date. Excerpt from paper[1]:

> To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. We note, however, that the spreadsheet program Google Sheets did not convert any gene names to dates or numbers when typed or pasted; notably, when these sheets were later reopened with Excel, LibreOffice Calc or OpenOffice Calc, gene symbols such as SEPT1 and MARCH1 were protected from date conversion.

There are several Excel behaviors that alternative software don't copy. But for some reason, the leading zero removal in csv import has stuck as a default even though it has repeated complaints and generates very similar Q&A in other software packages. (Why copy a behavior that makes you have to answer identical frustrated questions from users wanting to preserve leading zeros?!? Do programmers of LibreOffice/Google/Pandas all like inflicting pain on their users?!?)

Maybe another reason is that other industries or workflows depend on leading zero removal but we never hear from them praising it. Therefore, we're distorted by a biased subset that only complains loudly about it.

[1] https://genomebiology.biomedcentral.com/articles/10.1186/s13...

So, if all your friends jump off a cliff are you going to follow them?

I’m just here like “there is a spec for CSV?”

I don’t know how many times I’ve dealt with broken .CSV files that someone ram through Excel or Access

> CSVs are the worst and only option for data exchange in 99% of the cases.

If it helps, for database data (eg not spreadsheet / excel), SQLite is a good alternative single-file format.

So true!

I wrote a few parsing tools for a client, and pretty much every problem we had was due to BOM in excel-edited CSVs.

I was hoping to see some mention of the security downsides of CSVs - something that as a programmer of over 10 years experience, I wasn't aware of until a couple of years ago.

If you hand a CSV to a normal person, they'll open it in MS Office. Or LibreOffice.

If you put a formula into any cell starting with an `=` character, the office suite will give you a terse warning about updates or something, and when you accept, it will run every formula.

For example, you could write a formula to convert cells A1:D10 to params, and make a GET request to an address on your personal server. If the CSV contains user submitted data, e.g. if someone has set that formula as their name, and the CSV hasn't been carefully sanitised, then hand someone from the business your CSV to build some sort of report, they may well be sending much of the data to a 3rd party.

While maybe not a CSV-specific issue, certainly CSVs are believed to be safe (it's just a text file! We're only using it internally!) They really are not safe, and it sometimes takes a non-trivial amount of work to make them safe.

This is a bug in spreadsheet csv import functionality.

In that it's undesirable behaviour, yes. Certainly the messaging could be a lot clearer.

But the fact that multiple office suites do the same thing... one could argue that there are good reasons for allowing formulae in CSV files.

So I would argue that the "bug" is never going to be fixed, and that the real fix has to be our attitudes. We need to start treating CSVs the same way as we treat HTML - sanitise any user-provided strings strongly, quote all fields, etc.

> If you put a formula into any cell starting with an `=` character, the office suite will give you a terse warning about updates or something, and when you accept, it will run every formula.

Also Excel use the prefix apostrophe (') as an escape character to force text interpretation of a cell.

It will save that escape character to CSV, and will interpret an apostrophe prefix as an escape character.

Yep. There are a bunch of gotchas that don't necessarily present security issues, but can be annoying to debug.

Another good example is we used to generate random strings and report on those, but sometimes we got weird numbers appear in the fields, or ###, or even 0.

Turned out that the random string generator would sometime produce strings like '327e-733" which excel helpfully parses for us.

    Line-delimited JSON works, but imposes a high size overhead (field names are repeated for every record) and is not well supported in non-programming environments.

The article only discussed line-delimited JSON objects. How about line-delimited JSON arrays? You put the field names in an array on the first line, and the values in arrays on the subsequent lines.

Although in my day-to-day, the overhead of field names is rarely problematic.

Yeah, line delimited JSON where each entity is a JSON array seems to hit exactly the point being aimed at.

Also, like XML (definitely not space efficient) with SAX parsers, plain JSON has streaming processors available. They are naturally more complex than streaming processors for line-oriented formats, but they exist, so plain JSON, either array of arrays objects with the main data in array of array is an alternative, e.g.:

  {
    "headers": [...],
    "body": [ 
      [...],
      [...],
      ...
    ]
  }

the last form is also trivially extensible to support other keys for additional metadata, without harming the ability of applications without knowledge of the other keys to process the main data

I think the OP was talking more about:

    ["name","address"]
    ["Bob","1 Apple Street"]
    ["John","2 Apple Street"]

It's compatible with JSON Lines, and mostly equivalent to CSVs, at least how they are most commonly used.

That was also my thought getting to this line. First line is a list of the records fields names, then every line is a tuple of a record, convert tuple to struct and done.

I've seen very large JSON files passed around in zipped form, and there's a Python library that reads them in this form (afaict without first creating an uncompressed version of the entire zipfile). I would think if you zipped a JSON file where the fields are repeated, the compression algorithm would pick up on this and compress those field names (including their quotes and trailing colon etc.). Or do I misunderstand how compression works?

"Parquet files work well, but streaming is a tad more complex (you need to be able to seek to the end of the file to read the metadata before you can stream the contents)"

I didn't realize that all the metadata in Parquet was stored at the end. That is indeed unfortunate for streaming use cases. Especially sad because columnar dictionary formats can offer great compaction for some data. I've been achieving 20x+ size redutions by converting from CSV to Parquet.

Do you think of any solutions for this other than batching? I'm working on something similar just now, and I buffer several million records in memory, then write the file to external storage.

Parquet is intended as a file storage format primarily. When streaming, I think you are recommended to use Arrow, which is basically an in-memory Parquet. It supports putting the schema first and streaming a undefined number of rows.

https://arrow.apache.org/docs/python/ipc.html

Ultimately there’s no format that corrects all of CSVs flaws while retaining its benefits.

I'm planning to fix this with https://www.oilshell.org/ !

Eliminating the need for ad hoc parsing and splitting is one of the Four Features That Justify a New Unix Shell: http://www.oilshell.org/blog/2020/10/osh-features.html

I've designed a format called QTSV (quoted TSV) which lets you embed newlines and tabs in fields. It's built on QSN (quoted string notation), which are essentially just Rust string literals:

http://www.oilshell.org/release/latest/doc/qsn.html

QSN is fully implemented, but QTSV isn't done yet. Proposal here:

https://github.com/oilshell/oil/wiki/TSV2-Proposal

However the latest Oil release actually does emit QTSV! The "pp" builtin (for pretty-print) will print a QTSV file of shell function names (procs) and their doc comments (like docstrings).

----

If you'd like this to be a reality, contact me and help out! Useful things to do:

- write a messy CSV to strict QTSV converter (it will need some flags for the heuristics, like Python's CSV library) - write a QTSV library in your language

[1] https://www.oilshell.org/release/0.8.4/

We can still decide to start to use ascii 34, 35, 36 and 37. Sure, we'll need to deal with tsv and csv backward compat. But at least we won't have any escaping problems and can do very simple parsing.

Those are characters " # $ % if you meant decimal codes, and characters 4 5 6 7 if you meant hexadecimal. I would guess you didn't mean either.

Maybe you meant 1 2 3 4 which are SOH STX ETX EOT, which are transmission control codes for serial communications.

Or maybe you meant decimal 28 29 30 31 which are FS GS RS US, which are intended for the purpose of structuring data on magnetic tape.

The problem with those low order characters [0] is typing them and seeing them in editors. It's not impossible to do, but just not commonly done.

0: https://en.wikipedia.org/wiki/Control_character

I'll trade editor awareness of control chars for eliminating the problem in 99.999% of cases.

Also some editors do show the ascii codes, like notepad++.

Probably meant octal 34–37, i.e. decimal 28–31.

Yeah, Ctrl+_ (oct 37) has been sitting there patiently waiting since 1963...

Slightly tangential, but my feeling is that most people don't know where the "the good, the bad, the ugly" expression comes from.

It's a "spaghetti western" movie [0], directed by Sergio Leone, released in 1966, and credited with the rise to fame for Clint Eastwood.

If you have never watched it, I highly recommend it. In the genre, it is probably among the best.

Music was composed by Ennio Morricone, another pillar of Italian cinema and music, who died last July. One of the theme songs for the movie, "The Ecstasy of Gold", is very famous all around the world, and even Metallica played it at some concerts. [1]

Also, interesting detail from a Quora answer [2] - I wasn't aware of the locations used to film until now:

> The term "spaghetti western" was used by American filmgoers and reviewers because most were produced and directed by Italian film-makers, many of them trying to copy Leone’s unique style.

> Most were filmed on low budgets and shot at various locations around southern Italy and Spain. The Tabernas Desert and Cabo de Gata-Nijar were especially popular shooting locations.

[0]: https://en.wikipedia.org/wiki/The_Good,_the_Bad_and_the_Ugly

[1]: https://en.wikipedia.org/wiki/The_Ecstasy_of_Gold

[2]: https://www.quora.com/Where-does-the-expression-the-good-bad....

> Slightly tangential, but my feeling is that most people don't know where the "the good, the bad, the ugly" expression comes from.

I hope you're wrong about most people not knowing about the movie.

Maybe. But perhaps young people (<25-30) don't know it well?

You can get away with structured formats only when everything is fully automated, which works for APIs, but anything that needs human review will almost always be opened in Excel. That rules out anything fancy like JSON. Excel supports XML, but it think most people don’t know how to use that, and I suspect it would only be useful for simple tabular data — not complex structures. Many/most business processes are not 100% automated, so you just have to fall back on something that works with standard business tools (Excel).

Luke from TerminusDB here - we've been building automatic import and versioning features for CSVs over the last number of months. Just went into canary release actually.

Agree very strongly with the benefits and problems of CSVs in the article. CSVs are crazy flexible and if you are a team with a high velocity of changes and experiments with data, then it can make your life easier to work with CSVs (versus the other options).

Terminus is a straight-up open source graph database with revision control at the core. We noticed the CSV (and at best append to Git) problem when we were developing the solution. Loads of teams (irl) use CSVs + email for the storage and distribution of data (and features).

What happens is:

Data versioning is required but not uniformly specified or facilitated. It ends up being a suffix on a file, rather than a proper version-controlled system as is now common with Code.

Data communication is undertaken by sending large files via slack or e-mail. This means multiple inconsistent versions can float around the team without any visibility to project managers, and without any assurance that what you have is the latest version.

(As per the article and some comments) CSVs contain no type information, so information is routinely marshaled into and out of formats leading to extra “cleaning” time and hard to find errors (NaNs) which may not even appear in the final feature selection.

Apparently 'best practice' is to put all changes back into the data warehouse, but the reality is a 'shadow data' economy within enterprise and an open CSV economy in startups. We are all about bridging those worlds. Give you a GitHub-like way to manage your CSVs so they are version controlled and give the team the ability to see who is making changes. The ability to work offline and then sync up later (like pushing in Git) is a really important aspect.

Can really help with CSV problems (we know as first thing we did was shove all our CSVs into TerminusHub).

I never got why TSV's aren't more popular.

TSV's are exactly like CSV's but only use TABs instead of commas as delimiters.

This simply and elegantly solves one major issue with CSV's, which is that commas are quite common in real data. TABs, in contrast, are not.

It's not perfect, but it's a simple, real-world solution that popular software packages (like Excel) which consume CSVs will readily recognize.

There's no upside. Tabs are common in real world data. If someone's csv library is naive enough to fail to quote a field with a comma it will also fail to quote a field with a tab. Changing the delimiter is just moving deck chairs around on the titanic.

Depends if your data is mostly text or numbers. I'd say that commas are still more common, though one issue with tabs is that they are 'invisible'.

Or NSVs (null separated values). It doesn't have any downsides.

NSVs break any text-based tools e.g. not only is it hard to view them using standard tooling, you can't diff them either.

And if you're going to use stupid separators, at least use US (0x1F), that's the entire point of that thing.

https://prnt.sc/vf7699

This is how vim renders an NSV file. And I honestly don't remember the last time I edited a csv file by hand.

And this is how TextEdit does it:

   abc
   123

CSV files are also editable and readable with basic GUI tools like TextEdit/Notepad. Users of CSV files are not just people who can use Vim or a hex editor, it’s a general-purpose format. I can’t read nor edit the file with TextEdit, Notepad might also make it hard. Software that assumes C-style strings (where NULL terminates a string) can’t process your file without a complete rewrite. And even with Vim, I had to type ^Vu0000 to get the NULL in there, which is much less comfortable than comma or tab. (No, I’m not adding a key sequence for NULL to my .vimrc.)

Or ASCII 0x1E - record separator. They have a similar downside though: you can't easily type them and a lot of text editors won't render them by default.

0x1e (Record Separator) would replace the newline, for row values it's 0x1f (Unit Separator) you want.

Are those human-editable? How do you insert a null character?

Hard to edit from a text editor and requires using something specialised

I would think most TSVs are generated programmatically, maybe apart from a few test samples one might make in the progress of debugging one's code. And most programmers' editors that I've use allow you to insert tab chars, and visualize them as well (often as some sort of arrow). In my work (which has to do almost exclusively in language data, ASCII and otherwise), a tab should never appear in text; if it does, I replace it with a single (or multiple) space chars in a preprocessing step.

I suppose the other place that one might encounter tabs is in computer programs, most notoriously in makefiles. (You can tell which side of the tabs vs. spaces delimiting argument I come down on.) But then I've never had to send a makefile inside a TSV file...

PSVs too. (Pipes)

Pipes don't auto-format your data in nice columns.

It depends on your software. The V File Viewer does [0].

But the whole issue with alternative formats is getting widespread software support.

0: http://www.fileviewer.com

TSVs are exactly unlike CSVs where it matters. The quoting rules for CSV are just bizarre, and also make it impossible to skip records without parsing every single field. TSV does the right thing: only three characters are special (line feed, tab, backslash) and to include them in fields, you escape them. This is totally unlike the mess that is CSV!

I also don't get why TSVs aren't more popular.

Ironically, even the only reason why CSV exists at all, MS Excel, likes TSV better. The only format that can be exported and imported without trouble is TSV, encoded in UTF-16 with a BOM. But everybody uses CSV anyway. Weird.

The worst thing, to me, mentioned in "Bad" is that CSV's are untyped, which especially makes distinguishing nulls and empty strings impossible.

I wonder if a "typed csv" standard could emerge, like `.tscv` eg;

    Name::String?,ID::String,Born::Date
    Foo,0123,2020-01-01
    \NULL,0124,2020-01-02

Still streamable, human-readable/editable (although not with excel et al til they are modified to open it), compact, simple.

Of course, would only be useful to actual Data People for a while, so would not have ubiquity.

Coming back to this, I think a tuple-oriented JSON convention, like suggested elsewhere in the comments, would make more sense given the target market.

Yes, something like

    {
        headers: [...],
        types: [...],
        data: [
            [...],
            [...],
            ...
        ]
    }

Exactly. Could even add a doctype of some kind to indicate nothing follows the "data" key to help with streaming (though I'm not familiar with streaming json and this may not really help in practice).

You could look at it from the perspective that the only type is text. When you do this the responsibility for managing the data type rests with the parser and not the encoder.

> the wrong delimiter

I personally try to always generate my data as SSVs. Occasionally I'll have a need for one column with multiple words, in which case I'll make it the last column and it poses no parsing problems as I include the header always and then can just parse the last column as rest.

This means you need to be restrictive on the types in your data—which I've always found helps with a lot of other things anyway. No commas, no tabs, no newlines, no quotes—none of that is allowed in a value, and then you have nice clean data ready to parse, that is URL friendly, etc.

Of course, when I send my data back out into the world, need to "compile" it to CSV, as almost no one uses SSV.

> CSVs have no types, everything is a string.

I think this will be solved in the next 10 years or so. We'll have something that's like Schema.org meets DefiniteltyTyped. A "Common Tongue" for types, with a Wikipedia like community and an easy syntax to extend at the last mile for defining your own dialect to suit your worldview.

> SSV

https://en.wikipedia.org/wiki/SSV

All Delimiters Matter !

> I personally try to always generate my data as SSVs

That's great. I generate my data using either ZSVs or sometimes ΩSVs

addendum: SSV = "Space Separated Values"

    Name Score
    Jill 123

That incorrect parsing of fields wih newlines is real. Here’s a demonstration how you can essentially inject made up data into someone’s analysis if you know they will use the most popular Apache project and the de-facto standard tool for big data processing (with default settings): https://kokes.github.io/blog/2020/06/12/high-performance-dat...

You can stream AVRO records. AVRO is a well defined, binary, space efficient serialization format that can be used to serialize individual records (for streaming) or a whole set of records (for file storage). That should cover all of the authors requirements, right?

More info: https://www.confluent.io/blog/avro-kafka-data/

We use CSVs a lot and a good schema for a metadata file would be very useful. Rather than making up my own, does anybody know of a good, well-adopted metadata format for CSV files? I found https://specs.frictionlessdata.io/tabular-data-package, but not sure how widely used it is.

https://www.w3.org/TR/tabular-data-primer/#column-info

Or if you want column information embedded into the csv itself:

https://www.w3.org/TR/tabular-data-model/#embedded-metadata

If you want to know how ugly a format is, just look at all the options you have to enter correctly when you try to open it.

But dont forget, there is only two kinds of decimal separators in this world, those with comma and those with dots...

Or as someone might say, there are only 10 kinds of binary separators.

https://archive.is/wQQ3h

I've always wondered why parquet and arrow put metadata at the end.

I though it was already a standard to treat \NULL as null in CSV parsers.

Is it?

"null" meaning what exactly? And no, it isn't a standard.

Nope. https://tools.ietf.org/html/rfc4180

Reply on news.ycombinator.com