> This results in a truly hierarchical, filesystem-like data format. In fact, resources in an HDF5 file can be accessed using the POSIX-like syntax /path/to/resource.
That seems a whole higher level of complexity compared to CSV or the other options listed in TFA (perhaps comparable to Excel).
NetCDF4 (built on top of HDF5 largely through sub-setting) is considerably more powerful than excel/libreoffice. Its also easy to access through widely-available libraries. I frequently use the Python `netCDF4` (yes, it really is capitalized that way) library for exploratory work.
You can just use sqlite then. Very compact, highly popular (in different role though). Seen it used for large datasets - map tiles (millions of jpeg files). Much smaller size than zip or tar archive, indexed, fast.
Its a little weird at first but its a great format and has libraries in a lot of major languages. It stores a sparse matrix which cuts the size down a lot.
I don't think anyone wants tabular data. They want to ingest it into a system so they can query it, or join it, or aggregate it. They want to get rid of tabular data as quickly as possible
Data at the boundary must be validated, a file format can't do that for your. Semantics is harder than syntax and you can't push every problem to that level.
The finance world runs on tables. They want to ingest it, query it, join it, aggregate it ... and look at the result in tables. Tables are everywhere, and a generic tabular format is constantly useful for a la carte one off spontaneous data processing. I love strict schema validation as much as the next guy, but sometimes you just want to cut and paste a table.
yeah, um, my life is in tables (stats, finance, etc).
while csv is less than optimal, it's always worked out in practice for me (because we still have to validate anything of significant size and nothing finance or city/nation sized should generally be edited by hand anyway, and errors are everywhere independent of format).
honestly, my bigger nightmare has been people trying to put things into something like xml...
edit: not that there aren't huge problems with the csv/excel/database software and interaction: generally I just end up having to assume everything is text and defining type information my end afterwards on successful parsing
We've had XML for decades and the default interop is still tabular data - probably in large part because unless you go into non-standard extensions, tabular data is the easiest thing to get from a database query.
> Most tabular data currently gets exchanged as: CSV, Tab separated, XML, JSON or Excel. And they are all highly sub-optimal for the job.
> CSV is a mess. One quote in the wrong place and the file is invalid.
That breaks the other formats too, why pick on CSV? I can imagine a format designed to be friendly to syntax errors, but contra Postel's Law I'm not sure it would be an improvement over a strict, fail-fast syntax.
That's CSV/TSV's real shortcoming: about the only generic validation they allow is to make sure the column count is the same for all rows.
>> CSV is a mess. One quote in the wrong place and the file is invalid.
> That breaks the other formats too, why pick on CSV?
I think it's perhaps badly worded, but the implied (and more important) criticism seems to me to be that CSV makes this kind of error much more likely, with its handling of quotes. Having worked with CSV files that had commas in the data (and sometimes quotes too), I quickly learned that I should `set readonly` on my editor and only interact with the file through programmatic tools, and give up any notion of it being a plaintext hand-editable data format.
Indeed, it’s because people use an editor that isn’t designed for editing csv to edit csv.
Every csv files is a text file, but not every text file is a csv file, but people use text editors to edit them.
A csv editor would forbid you from entering that quote, automatically add a matching one, ask you for your intentions, or whatever, but it shouldn’t silently corrupt your file.
A spreadsheet-like UI, but without formulas, automatic single header row, etc. would be a better UI for csv files.
If you use zip to edit a .xlsx or .docx file, you easily get an invalid file, too.
VisiData is an interactive multitool for tabular data. It combines the clarity of a spreadsheet, the efficiency of the terminal, and the power of Python, into a lightweight utility which can handle millions of rows with ease.
We can blame CSV, or we can blame the way people use CSV. Either way CSV is so unreliable that I try to “fail-fast” as soon as possible in automated pipeline.
At work, we explicitly define data structuring process, converting CSV to Parquet with strict schema and technical/structural validation. We assign interns and new grad engineers for this, which is nicely within their capabilities too with minimal training.
> That's CSV/TSV's real shortcoming: about the only generic validation they allow is to make sure the column count is the same for all rows.
Once upon a time, when I was doing a lot of data interchange between a wide variety of systems (OS'es, applications, etc.) I considered proposing an "enhanced CSV" (ECSV) where the values did not start on the second row in the file, but instead the second row would be regular expressions that could be used to validate the contents of the columns that followed, and data would start on row 3.
(Newlines added because HN doesn't speak Markdown, sigh.)
In the end, I think the solution was far simpler: we just exchanged a separate descriptor file that had column names and their corresponding regexp patterns for validation as a separate (versioned) file, in order to save a few bytes inside each file transmission, which was a real savings when you paid per-byte over an EDI network.
I spent a while making a binary format for tabularish documents, and even started on an editor for it. What I decided on after some long months of gradual iteration was to give each cell its own header that could contain various forms of type info, flags, and modes, and to define a cell type that described forms of break (space, line, page, etc. - a 16-bit range of break types could be encoded). The document header also described a dictionary mapping for the data so that it could immediately be presented to the editor in a readable form.
But now I just use plain old spreadsheets to do things - I obsoleted my own tech, although I like certain things about it. The editing and storage encoding isn't really the problem so much as the integrity of the literals, which a solution like the regex idea could accommodate.
I do think that CSV would benefit by having a header area that described the encoding of breaks in cells and lines. Maybe that's the only thing that really needs fixing in it. And if it included arbitrary break levels like my thing and dropped the rectangular row-column shape, it would cover a huge number of documents.
HN actually does support markdown code blocks, you just have to preface lines with four (nope, two)[1] spaces instead of the (newer) triple-backtick codefences.
Yup, I noticed that and my Level of Care was insufficient to go and edit my comment a second time to fix it. But, thanks for confirming that it could have worked!
if you add an extra comma in a CSV (outside of quoting) then the rest of the cells in that row are off by 1. Which is not good obviously. But if you add an extra quote, then the entire rest of the file is garbage.
If it's tabular, I want schema for the columns. Is this column a 'number' or a string? Even better, is there a max length or precision known? Can the cell be null and how is that represented? How are dates formatted? Are they UTC/specific TZ, etc.
Most of my complaints about CSV relate to trying to determine the types used to parse or import as, not how commas are escaped. Excel, for example, actually warns you about this if you try to save a native Excel file as CSV: you are effectively throwing away information.
Mostly the problem comes from how excel is apocalyptically shitty at inferring datatypes, incorrectly assuming non-dates are dates and ISO8601 dates are god knows what, when a sane format would default to text if it didn't know better.
That kind of frustration was what drove me away from spreadsheets in the end. Someone sent me a native excel file for the first time in ages the other day and I just opened it in Pandas without thinking about it.
Pandas also gets things wrong (eg give it a column of unix timestamps with one missing value and there's a good chance it will treat them as float) and it took me a while to adapt to its vector math approach. But once I got comfortable with it working with a new pile of messy data began to feel pleasant rather than annoying.
> Columns are separated by \u001F (ASCII unit separator)
> Rows are separated by \u001E (ASCII record separator)
Or, how about columns separated by \u002C, and rows separated by \u000A. And, for bonus points, we can even define unambiguous ways of escaping those two characters so that they CAN appear within column values, if we wanted to, and not tell people that our encoding format is totally stupid and that they need to use a different format.
OP's proposal is equally "highly sub-optimal for the job" for exactly the same imaginary reasons they dislike the currently available encoding formats, but they don't seem to realize it.
> OP's proposal is equally "highly sub-optimal for the job" for exactly the same imaginary reasons they dislike the currently available encoding formats, but they don't seem to realize it.
This is a really unfair appraisal in a bunch of different ways.
Removing the ability to embed record delimiters, for example, means you can process the records in parallel. That’s a massive improvement all by itself.
Stating that their reasons are “imaginary” is just a needless insult, apart from being wrong. Why be like that?
> Removing the ability to embed record delimiters, for example, means you can process the records in parallel. That’s a massive improvement all by itself.
I can process records from a csv in parallel just fine. Lexing isn't slow.
.csv stream -> tokens -> records (-> aggregate into ~100ms blocks) -> work queue with N workers
You lex the whole file, separate it into records and then process them?
Doesn’t sound like you’re processing the file in parallel to me.
TFA’s way would allow you to start at any arbitrary file position, just advance until you find a record separator then start parsing from there without having to worry about starting in the middle of a quoted string.
> Stating that their reasons are “imaginary” is just a needless insult, apart from being wrong. Why be like that?
It's not an insult, let alone a needless one: it is a statement of fact.
The reasons OP cites for criticizing CSV are creations of OP's imagination, their subjective beliefs, and not based in objective truth.
For example:
> "CSV is a mess."
Actually, CSV is quite orderly and predictable. OP only imagines it's a mess because of some skewed experience they've personally had.
> "One quote in the wrong place and the file is invalid."
Any file format that is encoded improperly renders it invalid. Imagining that this is strictly limited to CSV, is just that: a creation of OP's imagination.
> "It is difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file)."
This is absolutely untrue. Anyone who's written a high-volume streaming CSV parser that is multi-process and multi-threaded should be giving OP the side-eye here.
Yes, you can absolutely start parsing from any arbitrary point in the file, and yes, in the degenerate worst-case scenario, you may need to rewind back to the beginning of the file if the data exists in such a way that your arbitrary starting point requires it, but that is true of ANY file format: it is possible to craft a worst-case scenario and specific starting position within that data that would require this. CSV is no exception.
Hope this helps you better understand what I had written.
The proposed format is reasonably same, but you really want to prevent people from writing them by hand, and adding a bit of metadata to describe the column data types at a minimum, and ideally more information such as allowed values, semantics, etc.
To that end, I suggest that putting the tabular data file, along with a metadata descriptor file, inside an archive format (zip, tarball, etc.); that would put just the right size speed-bump to encourage accessing the data through tools and libraries (though if someone is just a bit determined, reading and editing the contained data directly isn't actually impossible or forbidden).
All that said, if you want a better tabular data interchange format badly enough that you're considering devising one, you should probably look at using something even more featureful, like SQLite:
SimonW’s preferred technique of using sqlite as the means of exchange is also very powerful. Particularly when combined with all of the utils he maintains.
The fact that it can do more is in no way a negative. Can even make a limited JSON parser with reduced capabilities. And with JSON can do more definitions like header names vs column names vs just arrays of arrays.
I remember my annoyance at XML that you couldn’t simply append a row, due to the closing tag.
I think there was some off-spec dialog called something like “appendable xml” (only snappier) that said the closing tag of the root element was optional, but it was clearly a hack.
I wanted there to be no root element at all, and the filename (or, the last part of the url) would act as the root element. Kind of weird idea in hindsight, but kind of useful too.
One downside is that there’d be no obvious way to specify attributes on the root element.
Doesn't have a great int64 number story, no native dates / date-times. If you want named tuples, then the names need to go everywhere, otherwise it's a serialization mechanism on top of JSON.
I think the problem is that there is no clear standard or convention for how to do this. Indeed there are many ways to represent tabular data. Most large software projects probably have at least one bespoke file format that they use internally. The trouble is the lack of such a format that everyone agrees on.
Not being able to do more is exactly the point. By restricting the space of the file format, we can free the mind to think about how to fit the data to the format.
If you can do anything, it becomes hard to do the right thing. If you can only do the right thing, it becomes trivial to decide.
in that way the keys aren't repeated. It wouldn't be coherent useful JS objects when deserialized, but it would be trivial to convert js table object into a true array of Javascript objects.
That's inferior to even csv in any all its forms. It cannot be appended w/o removing the trailing square bracket. It requires column names per each row, you can't even know how many rows are there w/o parsing. It requires quotes (technical csv allows multiline rows but that's rarely used in practice)
json is absolutely horrid for this type of hack-in jobs. Tabular data just not well structure data, easy to cut and paste.
No doubt binary formats like Parquet are the way to go for high performance with multi-GB datasets. Seems like total overkill if you have a few hundred or thousand rows of data though. Being able to create/edit/view stuff in a text editor and easily version it is very useful.
Do people really edit csvs in a text editor? It's horrific, the columns don't line up at all, empty cells are represented by a bunch of commas in a row (which, are you supposed to count all the commas?)
And in terms of versioning, I have seen people commit diffs of csvs before, and they're equally unreadable.
CSV is a plain text format, but that basically buys you nothing. As long as you're going to be loading it into excel or whatever anyway, might as well just pick a good binary format like parquet.
I frequently use the table syntax in org-mode. No, I wouldn’t edit CSVs because they are miserable, but a friendly syntax with some basic text editor support, absolutely.
I frequently edit CSVs by hand. As long as you understand your data, and stakes are low (e.g. it's a one off analysis not prod code) it's pretty easy and convenient.
It’s strange that we pretend text is not binary. The truth is our many tools are set up to handle binary text data and these tools are not set up for alternate encodings.
If you grab a text file from a Windows machine and bring it to a mac, you’ll see that txt is far from perfect.
This is a long way of saying that if we develop both the format and the tooling then the distinction of text vs “binary” tabular data goes away.
Fair point. But the vast majority of programming tools do already handle UFT8 text (although perhaps don't do a great job with US/RS characters).
also you could write an efficient parser for US/RS separated data in 5 minutes. For parquet you would have to integrate with a library (with all it's dependencies and design choices) or spend days writing your own parser.
Text is text. It's stored as bits, typically 8 bits per character. It allows arbitrary precision but is very space (and memory throughput) inefficient. Fine, compression works but there are many more bits flying around and you're spending CPU cycles on decompression.
What could you do instead? Use appropriate integers, fixed point, or floating point representations. Everybody here knows these things. Nobody is pretending anything.
To the point of portability: IEEE-754 is the same everywhere.
I rarely want to see tabular data in a human-readable format. It is always the most tedious way to approach it. My go-to is Excel/LibreOffice Calc. This approach is at least tolerable to edit in a text editor, while something like the OpenDocument Spreadsheet format or the Excel format is impenetrable.
> I rarely do it, but it’s nice to be able to Human read when I need to. Also being able to use all the command line text tools is super convenient.
Sometimes it helps a lot to eyeball what you have before doing one off scripts to filter/massage your data. Had a recent case where the path of least resistance was database to csv to one off python to statistic tools with a gui and tabular display.
Could have probably done some enterprise looking export infrastructure but it was a one off and not worth it.
Yes, that is an issue with the suggested approach. Unless we can persuade all the editor developers to break lines as 'RS' characters (which is not going to happen).
I wonder if there is some hacky way around that. For example, an LF (line feed) directly after a RS character is not considered data (but will break the line when shown in a text editor).
Does Excel support it? No? Then that's the end of that.
Excel is tabular data to all non developers. The formats supported by Excel are the whole thing.
And if we're inventing a CSV-like format that uses a more convenient character than quotes and commas, maybe jumping to a non-displayable non-typeable character isn't the best?
Honestly, if I were inventing a table format, I'd use a strict subset of HTML5 tables. Strip it down as hard as possible to the bare minimal elements - no unneeded close tags. The only real flaw is that their encoding of whitespace and escaped chars are verbose and ugly.
This is a very cool idea but why not just add the closing tags and have a minimal format that is also valid HTML and can easily view the data by pointing your browser at the file?
It's already a universal format and as dtech mentions below, you get Excel compatibility.
I would also add headers and an optional type attribute...
<tr>
<th data-type="int">Age</th>
</tr>
If the file is too big then you probably need compress it or use a more appropriate format.
(I had considered <html application="tabular-data">; my reason was that it seems to work better with user CSS, and might be simpler to parse in other cases too.)
I've pondered exactly that. After a previous HN thread, I tried my hand at writing a specification that was minimal as possible but followed HTML5. For example many end tags are optional like `tr` and `td` end tags. Though I pulled in RDFa for richer data types.
I never understood why the ASCII separator characters aren't used more. It seems like we're one simple text editor feature away from having easy display and modification. Is there some historical reason for not doing that?
My editor, and lots of editors now, put spaces instead of tabs when I press the tab key. No reason something similar couldn't happen in CSV modes/apps.
Early on they were used in some serial and modem protocols, which was problematic when you used them in actual file content. I remember trying to use them in a file, and then when I tried to transfer them, the transfer would stop at RS.
I’ve wondered this too. At one point I started writing a hobby programming language and explicitly added escape codes for these extra control characters to be used in string literals. (Ada has them all included in the “ASCII” package which is nice.) It seems like a no-brainer to bake them into file formats like the OP describes.
Hi folks, Stavros from TileDB here. Here are my two cents on tabular data. TileDB (Embedded) is a very serious competitor to Parquet, the only other sane choice IMO when it comes to storing large volumes of tabular data (especially when combined with Arrow). Admittedly, we haven’t been advertising TileDB’s tabular capabilities, but that’s only because we were busy with much more challenging applications, such as genomics (population and single-cell), LiDAR, imaging and other very convoluted (from a data format perspective) domains.
Similar to Parquet:
* TileDB is columnar and comes with a lot of compressors, checksum and encryption filters.
* TileDB is built in C++ with multi-threading and vectorization in mind
* TileDB integrates with Arrow, using zero-copy techniques
All the standards I’ve seen haven’t been worth the effort to implement. So since csv, with all its flaws, is good enough it crowds out other open standards.
People complain about it, but it’s not really much of a challenge to use csv. I’d also prefer it over the crap (rdf, xml, even schemad json) proposed by people who value more structure. It’s easier for me to just make clean production and consumption programs than to spend time on a really structured table format.
Although I would love a simple, tabular format so I’d never have to use csv again.
I think CSV is crappy because commas are so common in real data.
For almost all scenarios I've had to work with, I'd have been perfectly happy with TSV where literal Tab was a disallowed character. No escaping histrionics required.
I prefer commas because I can see them over tabs. I spend zero time escaping commas because the libraries and read and write with (usually pandas but pretty much everything) escape for me. So unless I’m manually building csvs myself it’s a non-issue and certainly not histrionics.
I prefer tabs because my data can often have commas in it. Seeing tabs isn’t an issue as I also have invisible characters visible in all of my editors.
But having your delimiter not be allowed in the record (as in \t is disallowed), makes parsing so much easier. CSV is a bear to parse because you have to read each value from a buffer to handle the quoting.
line.strip().split(‘\t’)
Is so handy. Batteries included are one thing, but I don’t want to pull in a library just to parse CSV files.
Given your example looks very much like python you could use the CSV module which is built into the standard library and handles all of this for you in a standards compliant manner.
You really do want to use a library to parse CSV since there are a number of corner cases. For example, your example code may not read a whole row since rows can have newlines in them.
> You really do want to use a library to parse CSV
Right… you absolutely need to use a library. I’ve written the parsers for CSV which handle the edge cases (at least RFC edge cases). But I don’t want to include a library to be able to do it. Most of my scripts are small, and adding libraries makes them more difficult to move around. So, I shy away from CSV as a format as a result.
I’m okay with not allowing new lines and tabs in my fields. It’s a valid trade off for my data.
Yes, many of my scripts are Python, but not all, so adding new libraries for different languages is more difficult for me to remember as opposed to just splitting a line by a record separator.
Also, many of my files require having comments in the file’s header. Another reason why CSV can’t be easily used, as this isn’t part of the RFC.
I appreciate, as described, some of your needs may differ from the standard format (I'd probably still use them to make interoperability between other l languages/people/systems easier though and have my comments etc. in documentation). However, with all that in mind I may not have communicated well enough about CSV as a built in.
> Most of my scripts are small, and adding libraries makes them more difficult to move around. So, I shy away from CSV as a format as a result.
The CSV library I speak of is part[0] of the python standard library. Unless you're working with a restricted subset of the language or something it should have no impact on the size of your script or its portability between platforms/locations.
Depends entirely on context. I did consulting in wholesale and supplier data very often contained pipes as content separators (e.g. for product categories, sizes, etc.).
It's easier to parse than CSV. Unfortunately, you have to decide how to handle newlines and tabs yourself (probably as \n, \t, with \\ for backslashes).
yeah the "CSV is fine" bit scared me. people think they know what everyone is doing and why; in reality (where these problems actually need to be solved) no one knows.
More than the millionth, I'm sure. If you know your input doesn't have commas (for example, because you generated it) and the code is throwaway, then splitting on comma is fine.
But, yeah, I prefer tab delimited, since "no commas" is painful, and data that contains quotes leads to scenarios where the crappy CSV I generated can't be parsed by RFC 4180 parsers.
Yeah I agree. Just the combination of opening in excel on double click and being dead simple to spit out from any software makes it a winner. It helps that it's usable in plain text too.
If it would have used ANYTHING other than half the world's decimal separator as a value separator, it would be a no brainer. If it had been .psv (pipe separated values) the edge cases would be so few that we could just ignore them.
Gnumeric (an open source spreadsheet) has a great dialog box that pops up and lets you tell it exactly how to parse the tab delimited / csv file you just handed it. It even has a preview of what your parsing options will do to the file you're opening. Also, "gnumeric foo.csv" does the right thing from the command line.
CSV has more problems than just quoting: one problem is that excel is so commonly used to open CSV files that various weirdnesses in excel have essentially become part of the CSV format.
For example, if a value looks like a formula, then the only way to get excel to treat it as text is to put a single quote in front of it, "'=not a formula". This in turn means that its common for values in a CSV to begin with a single quote, but for that single quote to not be intended as part of the value.
I think a decent format could be like CSV, but tab separated, where the header row is mandatory, and with explicit support for data types. Tabs and newlines (along with any other unicode character) can be included via backslash escaping.
Applications can use their own type specifiers (eg. "formula:") to avoid needing to introduce special rules. The value of a field (before interpretation) is the exact string written in the file. The type specifier indicates how that string should be interpreted as a typed value by the application. For example, the `str` type specifier says that backslash escape sequences should be converted to the corresponding characters when interpreting the value.
This has the following properties:
- Machine and human readable and writable.
- Does not place artificial restrictions on values.
- Can be extended as needed with new type specifiers.
- Can be processed losslessly even if an application does not understand all type specifiers in use.
- Is not ambiguous about whether data is part of a header or not.
- Can store common data patterns (eg. all values in a column have the same type) efficiently without being restrictive (values can still have different types even in the same column).
- Can easily jump to a line or field (newlines and tabs not allowed except as separators - they must be escaped when in a value).
I like the format description, thanks. Sounds like something I might implement some day for fun!
The reason I still don't like this is because most of the time I'm sharing csv is to a jira ticket or an email, or to customer and customer insists it needs to be csv.
When customer wants csv there is nothing I can do.
But in other cases I see this CSV file. So it's visual clarity is important. CSV has all the problems you listed, but TSV (and similar, such as your format) has the short coming of relying on an invisible separator. And worse, the width of tab is undefined, undecided.
I don't know what the best trade-of... Maybe we need better tooling so frontends show CSV/TSV et al as separated by cells.
When the customer wants csv, it's because they will open it on Excel, so anything about standardization or data quality is moot. They will have their data mangled in an application specific format.
You need to export to csv. But that doesn't mean you can't use an actually usable format for anything else.
> - Can be extended as needed with new type specifiers.
That's often not a good thing, at least if that allowance is given to third parties, as it wildly opens up the format to effective incompatibilities (fragmenting the ecosystem) and is a never ending source of security issues.
This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false.
There's a reason the vast majority of file formats do have some capacity for extension. By providing this capability you avoid much worse hacks (the devil you know and all that...) and you can ensure certain invariants are upheld (such as applications being able to process records losslessly even if they don't understand the format specifier)
> This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false.
Except it's true. For a trivial example, json which does not offer these capabilities doesn't suffer from incompatibilities or security issues anywhere close to how much yaml does. Because a json file which is not POD requires a lot of additional documentation and code.
> There's a reason the vast majority of file formats do have some capacity for extension.
Yes, naïvety (if not outright brain damage): for a long long time it's been assumed that flexibility and extensibility were innocuous, and while we've known that was not at all the case for at least a generation, these ideas remain attractive nuisances.
More modern formats have tended to stay away from these, and those few which have not (like, once again, yaml) are case studies in why they should have.
I live in France and let me tell you: CSV is not good enough.
What about France you say? The decimal point is a coma, and to avoid confusion, the list separator is a semicolon. So "1.5, 1.6" becomes "1,5; 1,6" when localized. And if you think it is terrible for CSVs, well, it is worse than that.
If we all used RFC4180 consistently, it would work, but of course, that would be too simple. Some software insist on localization. So unless you need to know the software and locale that goes with that CSV. In the most simple case: you can treat both comas and semicolons as separators and it will work for reading, for writing, you have to pick one that the target software hopefully understands.
Hell starts when you have decimal numbers, because sometimes, you get exported CSVs where the coma is used both as a decimal point and as a separator. It makes the number ambiguous and your file becomes useless. It can result in data loss (personal experience). For example "1,2,3" may be [1.2, 3] or [1, 2.3]. And even without that, you still have to guess if you are using points and comas or comas and semicolons.
And of course, there the quoting/escaping issues every country has. Good thing the RFC has clear rules, I heard you can even find software where they are properly implemented. Of course, in France we also have the added bonus of questioning whether or not comas should be quoted since they are not the localized separator.
And character encoding? Of course it is a mess, as with every text file, but CVS makes no effort to help, is it UTF-8? Latin1? I have seen both, and I have seen files mangled because they were opened with the wrong encoding. Also, line endings should be CRLF, for those who care.
CSV as defined by the RFC is good enough, the problem is how it is used in practice.
I don't think it's necessarily that CSV is good enough but that its the only ASCII tabular format that "double clicks into Excel" by default.
Excel supports a lot more tabular formats these days, but you have to search the ribbon for their importers. It's JSON support is actually surprisingly good, for instance.
Sometimes I think the best thing that the Excel team could do to make the state of the world of tabular formats for us better as developers is just to add a bunch of new file extensions that you can rename files to. For instance, if you could just rename a JSON file to end with something like .XLJSON and a Business Analyst or Accountant can just double click to open in Excel, that would open up a lot more nicer tabular format options beyond CSV with better developer ergonomics.
There is a decent file format for tabular data, and the author dismisses it: parquet.
It's compact, encodes all the common data types well, does int/float distinction (thanks for teaching us about how important that is json), stores null records with a mask instead of a special value, row major order, has compression, speedy random access... it has it all. And it isn't bogged down with legacy cruft (yet).
Since you need to look at tabular data outside of a text editor anyway[0], I don't see a ton of benefit to making it a plaintext format. Especially not with the author's suggestion of un-typeable ascii delimiters. If I can't type it on my keyboard, I may as well be looking at a hex dump of a binary file because I can't really edit it.
[0] Who among us hasn't experienced the joy of a pull request updating a checked in csv file? A mess of ,,,,,,,"Birmingham",,,AL, etc.
parquet is great but it's not particularly easy to read or write. the libraries that do exist to work with it are few and far between, and those that do either have a hundred dependencies or depend on native code (e.g. libarrow). certainly an important dimension in an ideal file format should be the ease of parsing/writing it, and parquet gets an extremely low score on that front imo
Parquet is also column-major which is great for many use cases, but bad for others, where row-major is more useful. For example, if you want to get just the first x rows.
Sure, but any new format is going to have the same problems. I think you're right that implementation complexity needs to be considered, but it's not like Word or Excel files or something where you need to replicate bug for bug a format accreted over decades.
Parquet isn't trivial to parse / write but that's probably good imo. CSV is really easy to write, and... that just means everybody does it slightly differently. Being somewhat difficult to interact with encourages people to use a library to centralize a bit, but it's not so complex that someone motivated couldn't write a new implementation in a reasonable amount of time.
I've done exactly what the author suggests for the exact same reasons. CSV headaches got me searching for other delimiters and I saw there were already unit and record separators, "WTF!?".
As long as you're dealing with text, and you're writing and parsing the data yourself, and you never need to use tools other than things that can be modified to set the item and line delimiters, then it's great. I haven't used the approach since then.
Mostly I haven't used it because half my use cases need binary numerical data (floats) and the others can be worked with entirely in Excel or as CSVs. But I like the idea, even if the support just isn't there.
> Tab separated is a bit better than CSV. But can’t store tabs and still has issues with line endings, encodings etc.
There are three main variations of tab-separated-values files:
1) Those that don't allow tab and line endings.
2) Those that replace tab and newline characters with escaped values (\n for newline, \t for tab, \r for carriage return, \\ for backslash).
3) Those that follow the CSV convention of quoting fields as defined in RFC4180
The third option is by far the best and is what Microsoft Excel uses. Microsoft Excel has a save file type called "Unicode Text (.txt)" which saves the data as a tab-separated file using RFC4180 quoting/escaping with the UTF-16LE character encoding. In older versions of Excel, "Unicode Text (.txt)" was the only way to export any values containing Unicode characters since the "CSV (Comma delimited) (.csv)" export format uses the ANSI encoding(Windows-1252 on Western/US computers) corrupting any characters not contained in the ANSI character set. In late 2016, Microsoft finally added the "CSV UTF-8 (Comma delimited) (.csv)" option for exporting a CSV file containing Unicode characters.
I honestly love SQLlite, so many things about this database are refreshingly amazing. The lightweight embedded nature, the extreme ubiquity[1], the single-source-file-to-compile idea[2], the gigantic test suite[3], their commitment to stability and backward compatability[4], etc
...
Simply a modern marvel of engineering. A thing to put on the next Voyager and send to aliens as a representative of what human minds and hands can do.
All I'd want is a CLI tool that can dump an SQLite file so that it actually looks like a table (using "|", "-", "+", etc.), and maybe also accept grep-style filter specs (per specified column[s] of course).
> Columns are separated by \u001F (ASCII unit separator)
> Rows are separated by \u001E (ASCII record separator)
That's a nightmare to try to edit yourself in a text editor?
I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes.
I deal with CSVs all day every day. I'm known for these two posts
Some friends and I actually started an RFC about 11 years ago for a CSV enhancement with an HTTP inspired header section with metadata including encoding. UTF-8 wasn't as clear of a winner back then. Never went anywhere.
One major limitation with quoted values that can this contain record delimiters (as opposed to escaping the delimiters) is that it stops systems from being able to load records in parallel.
Some systems ban embedded record delimiters, for this reason.
What's the difference between quoted and escaped delimiters? (Keeping in mind that escaping sequences can themselves be escaped, ad infinitum. You can't simply seek to an escape sequence and depend algorithmically on a small, fixed lookbehind.)
I think the parent that if newlines were encoded as "\n" (with a backslash) then you could always split on (actual) newlines and process them in parallel without having to tokenize the quote first.
"That's a nightmare to try to edit yourself in a text editor?"
I have found these "unusual" separators are useful, e.g., I use ASCII file separator (FS). I use tr and sed to add/remove/change separators. If I had to edit a table interactively I would change the separator to something visible before editing. Thats said, in nvi(1) or less(1), US is displayed as highlighted ^_ and RS as highlighted ^^. It is not difficult to work with if the data is just text, as it is for me. One could also use a hex editor like bvi.
On large tables, I prefer to make edits non-interactively. I use ed(1) scripts.
Unfortunately, the UNIX sort command -t option will only support a limited range characters as separators. US, RS and FS are not among them. If I want to use UNIX sort I have to change the separator to one that sort accepts as a separator before sorting.
The complaints about CSV I read on HN and elsewhere seem to be complaints about what people put into them, i.e., lack of enforced rules about what is acceptable, not about the format itself.
In the printing world inkjet printers for industrial use use a file format that is all RS, GS, US, and FS characters. It had no line breaks instead it used a RS character at the beginning of a record. It would routinely break if people tried to open in a text editor. Nothing wants to deal a 300mb file consisting of a single line. Ended up writing my own library to manipulate the files and used a lot of tr, see, and awk in the command line. It was a pain only because modern editors have forgotten control codes
Actually, I think that it is, if it supports UTF-8 without BOM and does not try to do such things like convert quotation marks into non-ASCII quotation marks automatically, etc.
However, using a proper ASCII text editor would be better, if you do not want Unicode, to avoid many of the problems with Unicode if you are loading an unknown file or copying unknown stuff using clipboard, etc, which may be homoglyphs, reverse text direction override, etc.
(I use vim with exclusively ASCII-only mode, and do not use a Unicode locale, on my computer.)
I just mean, if you _require_ plugins in order to be able to edit the content, then the content can't easily be described as text. It is fine to use a specialized application to edit a file of a non-text format, I have nothing against that, but you have then left the realm of text editor.
As an example of what I mean, if someone wrote a vim plugin that allowed a user to interact with a sqlite file and change the schema or edit raw row values from vim, it could be a really valuable and useful plugin. But the presence or absence of a plugin for some given text editor doesn't change whether a given format is generally considered a format suitable for being edited in a text editor. What it does instead is convert vim into an editor of non-text files.
I admit that the proposed file format is much closer to being editable in a text editor than a binary format such as sqlite, but the fact that the characters cannot be typed without special functionality suggests that the format is not suitable for a text editor.
The described format _is_ editable in vim without a plugin. It's just a little awkward, because everything will be on a single line, and you have to use more complicated key commands to enter the delimiters (for example `<C-v><C-6>` for \x1e).
That's just text. In one sense, yes, it's arbitrary bits. But it's also clearly text. It's probably not what was referred to. If you have binary data that's encoded as text, it seems obvious that that can be embedded in text. It probably wouldn't be worth mentioning that "Any binary allowed between the quotes."
We’re talking about a tabular data file format. If you want to include arbitrary binary, use a binary data file. Or base64 encoded data. Most datasets you’d use data like this for are small enough to fit into memory, so let’s not get carried away.
(I happen to use tab delimited files to store data that can’t fit into memory, but that’s okay too)
Yes. I think we're agreeing. I was responding to this "Any binary allowed between the quotes.". Binary data can't generally be dropped directly into a text format without some kind of organized encoding.
Yeah, I think so… I thought they meant using a quote as a flag for “binary data lies ahead”, which really seemed odd to me. But — it is completely possible in a custom file type. But yes, if this case, the entire file wouldn’t be UTF8, even if all of the non-quotes data would be.
In retrospect, the idea of random binary data enclosed in quotes is what I’m mainly responding to — which I think we can all agree is a bad idea. (If you need to do that, encode it!)
Excellent posts. Coincidentally couple weeks ago while evaluating a HTTP response for a web service, I noticed that for tabular data, CSV is much more optimal than JSON; yet there is lack of HTTP header support for CSV responses that could provide clients with supplementary information in order to keep parsers adaptable.
If you have a copy of the said RFC, would like to refer.
>> Columns are separated by \u001F (ASCII unit separator) > Rows are separated by \u001E (ASCII record separator)
>
> That's a nightmare to try to edit yourself in a text editor?
It is. When I needed to edit a file with field separators (a little tool I made used field separators) I found that Vim was great, because I could copy an existing character into a specific register, and then never use that register for anything else.
> I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes.
Encode rather than escape, such as encoding an arbitrary byte as %xx where xx is two hex digits. Use this encoding for any %s in the values, as well as any field separators and record separators and any bytes that have special meaning in your format.
Encoding rather than escaping means that given a record I can split it into fields using the built-in string splitting method of whatever language I'm using. Dealing with a format that can have the field separators escaped in the values will usually present less opportunity to use the language's efficient built-in string functions.
I mean, in business contexts at least, Excel is what we end up using nearly all the time because it's ubiquitous. This doesn't mean there aren't issues with it; obviously it's nonFree, and it's notorious for making assumptions about data (see recent coverage of scientists literally renaming a gene (I think?) because Excel kept assuming it was a date or something).
But Excel as a tool is on so many desktops that you can assume access, and so it gets used.
I have a strong sense that this may be the biggest reason why no other format has taken over. It's "good enough" that it steals oxygen from any competing idea.
Yeh, I think you’re spot on. It’s bad enough that lot of people will implement direct alternatives, but it’s good enough that none of the alternatives will get any traction — unless they can be “just click the file” compatible with Excel (or something at least as possible and useful as excel… a browser maybe? )
Why not sqlite? You can read/write to a sqlite file in pretty much any language using decent bindings aside from maybe browser JS. I've worked with third party sqlite files several times and it usually isn't that bad. You can't just use any text editor to work with them, but the sqlite CLI is very easy to install and use. If you want a GUI, DB Browser seems to work very well.
FWIW I have designed an upgrade to TSV as part of https://www.oilshell.org. Oil actually emits it now from 'pp proc' to pretty print "functions" and their docstrings as a table. It will be used in many parts of Oil, most of which aren't implemented yet.
It's called QTT -- Quoted, Typed Tables (formerly QTSV)
I think the only way that such a format becomes popular is if Oil itself becomes popular, so I haven't really emphasized it as a spec. (Similar to how JSON wouldn't be popular if the browser / JS weren't popular)
But it is a pretty easy spec -- just take TSV, add types to the column header, and specify that any cell that begins with a single quote is QSN.
A nice property is that every almost TSV file is a valid QTT file -- the exception being TSV files where a cell just a single quote, or some malformed QSN.
Note that TSV officially can't represent fields with tabs, but QTT can with '\t'.
As always feel free to contact me if you want to work on any of this
A key design point of QTT is that it's a special case of the "lines of text" narrow waist. CSV does not have that property, because fields can contain embedded newlines. And unlike TSV, QTT cells can contain arbitrary values.
That is, narrow waists are necessarily a COMPROMISE. JSON is a compromise, and Rust users will be equally unhappy as Lua or Erlang users. That is a feature and not a bug for something meant of interoperability. You are "stuck with" the lowest common denominator, but that's what enables interop.
(The lowest common denominator of them all is Unix byte streams.)
I contrast "monoglot" serialization formats like Python pickle an Go .gob with language-independent formats like JSON, TSV, and HTML. The wisdom of JSON is that Crockford specified it independently of JavaScript.
But both are useful.
It's not clear if RSON is meant to be monoglot or polyglot, but it's a huge difference and it seems more monoglot. QSN on the other hand is definitely a polyglot design like JSON, despite being derived from Rust.
354 comments
[ 1.7 ms ] story [ 149 ms ] threadhttps://en.wikipedia.org/wiki/Hierarchical_Data_Format
That seems a whole higher level of complexity compared to CSV or the other options listed in TFA (perhaps comparable to Excel).
P.S.
Zarr overcomes these limitations for array data and Parquet overcomes these limitations for tabular data.
https://linnarssonlab.org/loompy/
Its a little weird at first but its a great format and has libraries in a lot of major languages. It stores a sparse matrix which cuts the size down a lot.
https://linnarssonlab.org/loompy/format/index.html
Data at the boundary must be validated, a file format can't do that for your. Semantics is harder than syntax and you can't push every problem to that level.
while csv is less than optimal, it's always worked out in practice for me (because we still have to validate anything of significant size and nothing finance or city/nation sized should generally be edited by hand anyway, and errors are everywhere independent of format).
honestly, my bigger nightmare has been people trying to put things into something like xml...
edit: not that there aren't huge problems with the csv/excel/database software and interaction: generally I just end up having to assume everything is text and defining type information my end afterwards on successful parsing
Spreadsheets haven't exactly gone away either.
> CSV is a mess. One quote in the wrong place and the file is invalid.
That breaks the other formats too, why pick on CSV? I can imagine a format designed to be friendly to syntax errors, but contra Postel's Law I'm not sure it would be an improvement over a strict, fail-fast syntax.
That's CSV/TSV's real shortcoming: about the only generic validation they allow is to make sure the column count is the same for all rows.
> That breaks the other formats too, why pick on CSV?
I think it's perhaps badly worded, but the implied (and more important) criticism seems to me to be that CSV makes this kind of error much more likely, with its handling of quotes. Having worked with CSV files that had commas in the data (and sometimes quotes too), I quickly learned that I should `set readonly` on my editor and only interact with the file through programmatic tools, and give up any notion of it being a plaintext hand-editable data format.
Every csv files is a text file, but not every text file is a csv file, but people use text editors to edit them.
A csv editor would forbid you from entering that quote, automatically add a matching one, ask you for your intentions, or whatever, but it shouldn’t silently corrupt your file.
A spreadsheet-like UI, but without formulas, automatic single header row, etc. would be a better UI for csv files.
If you use zip to edit a .xlsx or .docx file, you easily get an invalid file, too.
VisiData is an interactive multitool for tabular data. It combines the clarity of a spreadsheet, the efficiency of the terminal, and the power of Python, into a lightweight utility which can handle millions of rows with ease.
https://www.visidata.org/
At work, we explicitly define data structuring process, converting CSV to Parquet with strict schema and technical/structural validation. We assign interns and new grad engineers for this, which is nicely within their capabilities too with minimal training.
Once upon a time, when I was doing a lot of data interchange between a wide variety of systems (OS'es, applications, etc.) I considered proposing an "enhanced CSV" (ECSV) where the values did not start on the second row in the file, but instead the second row would be regular expressions that could be used to validate the contents of the columns that followed, and data would start on row 3.
In other words, you might have:
``` ID,NAME,DATE
```(Newlines added because HN doesn't speak Markdown, sigh.)
In the end, I think the solution was far simpler: we just exchanged a separate descriptor file that had column names and their corresponding regexp patterns for validation as a separate (versioned) file, in order to save a few bytes inside each file transmission, which was a real savings when you paid per-byte over an EDI network.
But now I just use plain old spreadsheets to do things - I obsoleted my own tech, although I like certain things about it. The editing and storage encoding isn't really the problem so much as the integrity of the literals, which a solution like the regex idea could accommodate.
I do think that CSV would benefit by having a header area that described the encoding of breaks in cells and lines. Maybe that's the only thing that really needs fixing in it. And if it included arbitrary break levels like my thing and dropped the rectangular row-column shape, it would cover a huge number of documents.
https://news.ycombinator.com/formatdoc
Most of my complaints about CSV relate to trying to determine the types used to parse or import as, not how commas are escaped. Excel, for example, actually warns you about this if you try to save a native Excel file as CSV: you are effectively throwing away information.
Pandas also gets things wrong (eg give it a column of unix timestamps with one missing value and there's a good chance it will treat them as float) and it took me a while to adapt to its vector math approach. But once I got comfortable with it working with a new pile of messy data began to feel pleasant rather than annoying.
Or, how about columns separated by \u002C, and rows separated by \u000A. And, for bonus points, we can even define unambiguous ways of escaping those two characters so that they CAN appear within column values, if we wanted to, and not tell people that our encoding format is totally stupid and that they need to use a different format.
OP's proposal is equally "highly sub-optimal for the job" for exactly the same imaginary reasons they dislike the currently available encoding formats, but they don't seem to realize it.
This is a really unfair appraisal in a bunch of different ways.
Removing the ability to embed record delimiters, for example, means you can process the records in parallel. That’s a massive improvement all by itself.
Stating that their reasons are “imaginary” is just a needless insult, apart from being wrong. Why be like that?
I can process records from a csv in parallel just fine. Lexing isn't slow.
.csv stream -> tokens -> records (-> aggregate into ~100ms blocks) -> work queue with N workers
Doesn’t sound like you’re processing the file in parallel to me.
TFA’s way would allow you to start at any arbitrary file position, just advance until you find a record separator then start parsing from there without having to worry about starting in the middle of a quoted string.
It's not an insult, let alone a needless one: it is a statement of fact.
The reasons OP cites for criticizing CSV are creations of OP's imagination, their subjective beliefs, and not based in objective truth.
For example:
> "CSV is a mess."
Actually, CSV is quite orderly and predictable. OP only imagines it's a mess because of some skewed experience they've personally had.
> "One quote in the wrong place and the file is invalid."
Any file format that is encoded improperly renders it invalid. Imagining that this is strictly limited to CSV, is just that: a creation of OP's imagination.
> "It is difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file)."
This is absolutely untrue. Anyone who's written a high-volume streaming CSV parser that is multi-process and multi-threaded should be giving OP the side-eye here.
Yes, you can absolutely start parsing from any arbitrary point in the file, and yes, in the degenerate worst-case scenario, you may need to rewind back to the beginning of the file if the data exists in such a way that your arbitrary starting point requires it, but that is true of ANY file format: it is possible to craft a worst-case scenario and specific starting position within that data that would require this. CSV is no exception.
Hope this helps you better understand what I had written.
Each line is separate object there.
To that end, I suggest that putting the tabular data file, along with a metadata descriptor file, inside an archive format (zip, tarball, etc.); that would put just the right size speed-bump to encourage accessing the data through tools and libraries (though if someone is just a bit determined, reading and editing the contained data directly isn't actually impossible or forbidden).
All that said, if you want a better tabular data interchange format badly enough that you're considering devising one, you should probably look at using something even more featureful, like SQLite:
https://www.sqlite.org/appfileformat.html
https://github.com/secretGeek/csvz
It’s a pretty powerful concept.
SimonW’s preferred technique of using sqlite as the means of exchange is also very powerful. Particularly when combined with all of the utils he maintains.
Having an optional associated meta data file would be useful though.
[{row1}, {row2}, {row3}]
The fact that it can do more is in no way a negative. Can even make a limited JSON parser with reduced capabilities. And with JSON can do more definitions like header names vs column names vs just arrays of arrays.
I think there was some off-spec dialog called something like “appendable xml” (only snappier) that said the closing tag of the root element was optional, but it was clearly a hack.
I wanted there to be no root element at all, and the filename (or, the last part of the url) would act as the root element. Kind of weird idea in hindsight, but kind of useful too.
One downside is that there’d be no obvious way to specify attributes on the root element.
>> XML and Javascript are tree structures and not suitable for efficiently storing tabular data (plus other issues).
And since it doesn’t require one record per line it can be a hassle to read without having to parse it.
It’s really nice to be able to do “head -n 5”
[{"col1":"val1", "col1":"val2"}]
If you can do anything, it becomes hard to do the right thing. If you can only do the right thing, it becomes trivial to decide.
I could see a specialized form of JSON using jagged arrays
in that way the keys aren't repeated. It wouldn't be coherent useful JS objects when deserialized, but it would be trivial to convert js table object into a true array of Javascript objects.json is absolutely horrid for this type of hack-in jobs. Tabular data just not well structure data, easy to cut and paste.
And in terms of versioning, I have seen people commit diffs of csvs before, and they're equally unreadable.
CSV is a plain text format, but that basically buys you nothing. As long as you're going to be loading it into excel or whatever anyway, might as well just pick a good binary format like parquet.
If you grab a text file from a Windows machine and bring it to a mac, you’ll see that txt is far from perfect.
This is a long way of saying that if we develop both the format and the tooling then the distinction of text vs “binary” tabular data goes away.
also you could write an efficient parser for US/RS separated data in 5 minutes. For parquet you would have to integrate with a library (with all it's dependencies and design choices) or spend days writing your own parser.
What could you do instead? Use appropriate integers, fixed point, or floating point representations. Everybody here knows these things. Nobody is pretending anything.
To the point of portability: IEEE-754 is the same everywhere.
> It would be reasonably compact, efficient to parse and easy to manually edit > (Notepad++ shows the unit separator as a ‘US’ symbol).
Is it me or it won't be human readable because of the lack of new lines?
I think it’s a think where having the option for the .1% of times when you need it keeps me using it.
Sometimes it helps a lot to eyeball what you have before doing one off scripts to filter/massage your data. Had a recent case where the path of least resistance was database to csv to one off python to statistic tools with a gui and tabular display.
Could have probably done some enterprise looking export infrastructure but it was a one off and not worth it.
Does Excel support it? No? Then that's the end of that.
Excel is tabular data to all non developers. The formats supported by Excel are the whole thing.
And if we're inventing a CSV-like format that uses a more convenient character than quotes and commas, maybe jumping to a non-displayable non-typeable character isn't the best?
Honestly, if I were inventing a table format, I'd use a strict subset of HTML5 tables. Strip it down as hard as possible to the bare minimal elements - no unneeded close tags. The only real flaw is that their encoding of whitespace and escaped chars are verbose and ugly.
I've seen a lot of CSV where everything is quoted, meaning that the cell separator is effectively
which is only one character less than and still beats the pants off of JSON or XML. imho, it would be a good compromise, in that there's already partial tooling and GUI support.It’s also harder to read.
It's already a universal format and as dtech mentions below, you get Excel compatibility.
I would also add headers and an optional type attribute...
If the file is too big then you probably need compress it or use a more appropriate format.From an XML purist's perspective that's abhorrent, but if we're trying to make a terse table format skipping the close tags is good.
(I had considered <html application="tabular-data">; my reason was that it seems to work better with user CSS, and might be simpler to parse in other cases too.)
Here's the GitHub repo for what I like to call HSV5: https://github.com/elcritch/hsv5/blob/main/README.md ;)
And an example of the format, pretty similar to yours:
edit: wait, the stuff after <tr> is implicitly the first cell? That's new to me.
Similar to Parquet:
* TileDB is columnar and comes with a lot of compressors, checksum and encryption filters.
* TileDB is built in C++ with multi-threading and vectorization in mind
* TileDB integrates with Arrow, using zero-copy techniques
* TileDB has numerous optimized APIs (C, C++, C#, Python, R, Java, Go)
* TileDB pushes compute down to storage, similar to what Arrow does
Better than Parquet:
* TileDB is multi-dimensional, allowing rapid multi-column conditions
* TileDB builds versioning and time-traveling into the format (no need for Delta Lake, Iceberg, etc)
* TileDB allows for lock-free parallel writes / parallel reads with ACID properties (no need for Delta Lake, Iceberg, etc)
* TileDB can handle more than tables, for example n-dimensional dense arrays (e.g., for imaging, video, etc)
Useful links:
* Github repo (https://github.com/TileDB-Inc/TileDB)
* TileDB Embedded overview (https://tiledb.com/products/tiledb-embedded/)
* Docs (https://docs.tiledb.com/)
* Webinar on why arrays as a universal data model (https://tiledb.com/blog/why-arrays-as-a-universal-data-model)
Happy to hear everyone’s thoughts.
All the standards I’ve seen haven’t been worth the effort to implement. So since csv, with all its flaws, is good enough it crowds out other open standards.
People complain about it, but it’s not really much of a challenge to use csv. I’d also prefer it over the crap (rdf, xml, even schemad json) proposed by people who value more structure. It’s easier for me to just make clean production and consumption programs than to spend time on a really structured table format.
Although I would love a simple, tabular format so I’d never have to use csv again.
For almost all scenarios I've had to work with, I'd have been perfectly happy with TSV where literal Tab was a disallowed character. No escaping histrionics required.
Sometimes it works, sometimes it doesn't, can be a real pain in the butt.
I think that was the motivation for multi-character delimiters like <% %> or {{ }} in templating languages.
But having your delimiter not be allowed in the record (as in \t is disallowed), makes parsing so much easier. CSV is a bear to parse because you have to read each value from a buffer to handle the quoting.
Is so handy. Batteries included are one thing, but I don’t want to pull in a library just to parse CSV files.You really do want to use a library to parse CSV since there are a number of corner cases. For example, your example code may not read a whole row since rows can have newlines in them.
Right… you absolutely need to use a library. I’ve written the parsers for CSV which handle the edge cases (at least RFC edge cases). But I don’t want to include a library to be able to do it. Most of my scripts are small, and adding libraries makes them more difficult to move around. So, I shy away from CSV as a format as a result.
I’m okay with not allowing new lines and tabs in my fields. It’s a valid trade off for my data.
Yes, many of my scripts are Python, but not all, so adding new libraries for different languages is more difficult for me to remember as opposed to just splitting a line by a record separator.
Also, many of my files require having comments in the file’s header. Another reason why CSV can’t be easily used, as this isn’t part of the RFC.
> Most of my scripts are small, and adding libraries makes them more difficult to move around. So, I shy away from CSV as a format as a result.
The CSV library I speak of is part[0] of the python standard library. Unless you're working with a restricted subset of the language or something it should have no impact on the size of your script or its portability between platforms/locations.
[0] https://docs.python.org/3/library/csv.html
https://www.rfc-archive.org/getrfc.php?rfc=4180
If you don't, then split each line on ",". Problem solved.
If you find tab delimited easier to read (as I do), then check out the IANA spec for TSV files:
https://www.iana.org/assignments/media-types/text/tab-separa...
It's easier to parse than CSV. Unfortunately, you have to decide how to handle newlines and tabs yourself (probably as \n, \t, with \\ for backslashes).
And the millionth bad CSV parser is born.
But, yeah, I prefer tab delimited, since "no commas" is painful, and data that contains quotes leads to scenarios where the crappy CSV I generated can't be parsed by RFC 4180 parsers.
What? Real data contains '"' quote character too. That doesn't work.
If it would have used ANYTHING other than half the world's decimal separator as a value separator, it would be a no brainer. If it had been .psv (pipe separated values) the edge cases would be so few that we could just ignore them.
For example, if a value looks like a formula, then the only way to get excel to treat it as text is to put a single quote in front of it, "'=not a formula". This in turn means that its common for values in a CSV to begin with a single quote, but for that single quote to not be intended as part of the value.
I think a decent format could be like CSV, but tab separated, where the header row is mandatory, and with explicit support for data types. Tabs and newlines (along with any other unicode character) can be included via backslash escaping.
eg. (imagine the spaces are tabs)
Applications can use their own type specifiers (eg. "formula:") to avoid needing to introduce special rules. The value of a field (before interpretation) is the exact string written in the file. The type specifier indicates how that string should be interpreted as a typed value by the application. For example, the `str` type specifier says that backslash escape sequences should be converted to the corresponding characters when interpreting the value.This has the following properties:
- Machine and human readable and writable.
- Does not place artificial restrictions on values.
- Can be extended as needed with new type specifiers.
- Can be processed losslessly even if an application does not understand all type specifiers in use.
- Is not ambiguous about whether data is part of a header or not.
- Can store common data patterns (eg. all values in a column have the same type) efficiently without being restrictive (values can still have different types even in the same column).
- Can easily jump to a line or field (newlines and tabs not allowed except as separators - they must be escaped when in a value).
- Easy to remember.
Csv isn’t perfect, but I’d rather live with its weaknesses than use anything else.
The reason I still don't like this is because most of the time I'm sharing csv is to a jira ticket or an email, or to customer and customer insists it needs to be csv.
When customer wants csv there is nothing I can do.
But in other cases I see this CSV file. So it's visual clarity is important. CSV has all the problems you listed, but TSV (and similar, such as your format) has the short coming of relying on an invisible separator. And worse, the width of tab is undefined, undecided.
I don't know what the best trade-of... Maybe we need better tooling so frontends show CSV/TSV et al as separated by cells.
You need to export to csv. But that doesn't mean you can't use an actually usable format for anything else.
That's often not a good thing, at least if that allowance is given to third parties, as it wildly opens up the format to effective incompatibilities (fragmenting the ecosystem) and is a never ending source of security issues.
There's a reason the vast majority of file formats do have some capacity for extension. By providing this capability you avoid much worse hacks (the devil you know and all that...) and you can ensure certain invariants are upheld (such as applications being able to process records losslessly even if they don't understand the format specifier)
Except it's true. For a trivial example, json which does not offer these capabilities doesn't suffer from incompatibilities or security issues anywhere close to how much yaml does. Because a json file which is not POD requires a lot of additional documentation and code.
> There's a reason the vast majority of file formats do have some capacity for extension.
Yes, naïvety (if not outright brain damage): for a long long time it's been assumed that flexibility and extensibility were innocuous, and while we've known that was not at all the case for at least a generation, these ideas remain attractive nuisances.
More modern formats have tended to stay away from these, and those few which have not (like, once again, yaml) are case studies in why they should have.
What about France you say? The decimal point is a coma, and to avoid confusion, the list separator is a semicolon. So "1.5, 1.6" becomes "1,5; 1,6" when localized. And if you think it is terrible for CSVs, well, it is worse than that.
If we all used RFC4180 consistently, it would work, but of course, that would be too simple. Some software insist on localization. So unless you need to know the software and locale that goes with that CSV. In the most simple case: you can treat both comas and semicolons as separators and it will work for reading, for writing, you have to pick one that the target software hopefully understands.
Hell starts when you have decimal numbers, because sometimes, you get exported CSVs where the coma is used both as a decimal point and as a separator. It makes the number ambiguous and your file becomes useless. It can result in data loss (personal experience). For example "1,2,3" may be [1.2, 3] or [1, 2.3]. And even without that, you still have to guess if you are using points and comas or comas and semicolons.
And of course, there the quoting/escaping issues every country has. Good thing the RFC has clear rules, I heard you can even find software where they are properly implemented. Of course, in France we also have the added bonus of questioning whether or not comas should be quoted since they are not the localized separator.
And character encoding? Of course it is a mess, as with every text file, but CVS makes no effort to help, is it UTF-8? Latin1? I have seen both, and I have seen files mangled because they were opened with the wrong encoding. Also, line endings should be CRLF, for those who care.
CSV as defined by the RFC is good enough, the problem is how it is used in practice.
Excel supports a lot more tabular formats these days, but you have to search the ribbon for their importers. It's JSON support is actually surprisingly good, for instance.
Sometimes I think the best thing that the Excel team could do to make the state of the world of tabular formats for us better as developers is just to add a bunch of new file extensions that you can rename files to. For instance, if you could just rename a JSON file to end with something like .XLJSON and a Business Analyst or Accountant can just double click to open in Excel, that would open up a lot more nicer tabular format options beyond CSV with better developer ergonomics.
It's compact, encodes all the common data types well, does int/float distinction (thanks for teaching us about how important that is json), stores null records with a mask instead of a special value, row major order, has compression, speedy random access... it has it all. And it isn't bogged down with legacy cruft (yet).
Since you need to look at tabular data outside of a text editor anyway[0], I don't see a ton of benefit to making it a plaintext format. Especially not with the author's suggestion of un-typeable ascii delimiters. If I can't type it on my keyboard, I may as well be looking at a hex dump of a binary file because I can't really edit it.
[0] Who among us hasn't experienced the joy of a pull request updating a checked in csv file? A mess of ,,,,,,,"Birmingham",,,AL, etc.
Parquet isn't trivial to parse / write but that's probably good imo. CSV is really easy to write, and... that just means everybody does it slightly differently. Being somewhat difficult to interact with encourages people to use a library to centralize a bit, but it's not so complex that someone motivated couldn't write a new implementation in a reasonable amount of time.
As long as you're dealing with text, and you're writing and parsing the data yourself, and you never need to use tools other than things that can be modified to set the item and line delimiters, then it's great. I haven't used the approach since then.
Mostly I haven't used it because half my use cases need binary numerical data (floats) and the others can be worked with entirely in Excel or as CSVs. But I like the idea, even if the support just isn't there.
There are three main variations of tab-separated-values files: 1) Those that don't allow tab and line endings. 2) Those that replace tab and newline characters with escaped values (\n for newline, \t for tab, \r for carriage return, \\ for backslash). 3) Those that follow the CSV convention of quoting fields as defined in RFC4180
The third option is by far the best and is what Microsoft Excel uses. Microsoft Excel has a save file type called "Unicode Text (.txt)" which saves the data as a tab-separated file using RFC4180 quoting/escaping with the UTF-16LE character encoding. In older versions of Excel, "Unicode Text (.txt)" was the only way to export any values containing Unicode characters since the "CSV (Comma delimited) (.csv)" export format uses the ANSI encoding(Windows-1252 on Western/US computers) corrupting any characters not contained in the ANSI character set. In late 2016, Microsoft finally added the "CSV UTF-8 (Comma delimited) (.csv)" option for exporting a CSV file containing Unicode characters.
https://en.wikipedia.org/wiki/Tab-separated_values
See also this page on the SQLite website (they're understandably very proud of this): https://www.sqlite.org/locrsf.html
I think it's a fantastic format for archiving and distributing data.
https://www.loc.gov/preservation/digital/formats/fdd/fdd0004...
Simply a modern marvel of engineering. A thing to put on the next Voyager and send to aliens as a representative of what human minds and hands can do.
[1] https://www.sqlite.org/mostdeployed.html
[2]https://www.sqlite.org/amalgamation.html
[3] https://www.sqlite.org/testing.html
[4]https://www.sqlite.org/lts.html
[1]https://github.com/paulfitz/catsql
$ echo "<sqlite3 commands / sql queries>" | sqlite3 FILE
That's a nightmare to try to edit yourself in a text editor?
I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes.
I deal with CSVs all day every day. I'm known for these two posts
https://donatstudios.com/Falsehoods-Programmers-Believe-Abou...
https://donatstudios.com/CSV-An-Encoding-Nightmare
Some friends and I actually started an RFC about 11 years ago for a CSV enhancement with an HTTP inspired header section with metadata including encoding. UTF-8 wasn't as clear of a winner back then. Never went anywhere.
Some systems ban embedded record delimiters, for this reason.
Btw, I’ve (previously) included at least one of your essays in “awesome csv” list at GitHub. https://github.com/secretGeek/AwesomeCSV#essays
There’s a few specs mentioned there too — is one of those the spec you worked on?
I have found these "unusual" separators are useful, e.g., I use ASCII file separator (FS). I use tr and sed to add/remove/change separators. If I had to edit a table interactively I would change the separator to something visible before editing. Thats said, in nvi(1) or less(1), US is displayed as highlighted ^_ and RS as highlighted ^^. It is not difficult to work with if the data is just text, as it is for me. One could also use a hex editor like bvi.
On large tables, I prefer to make edits non-interactively. I use ed(1) scripts.
Unfortunately, the UNIX sort command -t option will only support a limited range characters as separators. US, RS and FS are not among them. If I want to use UNIX sort I have to change the separator to one that sort accepts as a separator before sorting.
The complaints about CSV I read on HN and elsewhere seem to be complaints about what people put into them, i.e., lack of enforced rules about what is acceptable, not about the format itself.
You just need a text editor that can support thia format.
However, using a proper ASCII text editor would be better, if you do not want Unicode, to avoid many of the problems with Unicode if you are loading an unknown file or copying unknown stuff using clipboard, etc, which may be homoglyphs, reverse text direction override, etc.
(I use vim with exclusively ASCII-only mode, and do not use a Unicode locale, on my computer.)
As an example of what I mean, if someone wrote a vim plugin that allowed a user to interact with a sqlite file and change the schema or edit raw row values from vim, it could be a really valuable and useful plugin. But the presence or absence of a plugin for some given text editor doesn't change whether a given format is generally considered a format suitable for being edited in a text editor. What it does instead is convert vim into an editor of non-text files.
I admit that the proposed file format is much closer to being editable in a text editor than a binary format such as sqlite, but the fact that the characters cannot be typed without special functionality suggests that the format is not suitable for a text editor.
Vim doesn't require plugins (as long as you are editing an existing file, and not trying to create a new one).
0101010010000111101010101
(I happen to use tab delimited files to store data that can’t fit into memory, but that’s okay too)
In retrospect, the idea of random binary data enclosed in quotes is what I’m mainly responding to — which I think we can all agree is a bad idea. (If you need to do that, encode it!)
If you have a copy of the said RFC, would like to refer.
>
> That's a nightmare to try to edit yourself in a text editor?
It is. When I needed to edit a file with field separators (a little tool I made used field separators) I found that Vim was great, because I could copy an existing character into a specific register, and then never use that register for anything else.
Encode rather than escape, such as encoding an arbitrary byte as %xx where xx is two hex digits. Use this encoding for any %s in the values, as well as any field separators and record separators and any bytes that have special meaning in your format.
Encoding rather than escaping means that given a record I can split it into fields using the built-in string splitting method of whatever language I'm using. Dealing with a format that can have the field separators escaped in the values will usually present less opportunity to use the language's efficient built-in string functions.
I fully support this format just for this.
+++
But Excel as a tool is on so many desktops that you can assume access, and so it gets used.
I have a strong sense that this may be the biggest reason why no other format has taken over. It's "good enough" that it steals oxygen from any competing idea.
https://news.ycombinator.com/item?id=25014721
FWIW I have designed an upgrade to TSV as part of https://www.oilshell.org. Oil actually emits it now from 'pp proc' to pretty print "functions" and their docstrings as a table. It will be used in many parts of Oil, most of which aren't implemented yet.
It's called QTT -- Quoted, Typed Tables (formerly QTSV)
It's built on top of QSN, which is just Rust string literal notation -- https://www.oilshell.org/release/latest/doc/qsn.html
There is a stub doc which links to a wiki proposal -- http://www.oilshell.org/release/latest/doc/qtt.html
------
I think the only way that such a format becomes popular is if Oil itself becomes popular, so I haven't really emphasized it as a spec. (Similar to how JSON wouldn't be popular if the browser / JS weren't popular)
But it is a pretty easy spec -- just take TSV, add types to the column header, and specify that any cell that begins with a single quote is QSN.
A nice property is that every almost TSV file is a valid QTT file -- the exception being TSV files where a cell just a single quote, or some malformed QSN.
Note that TSV officially can't represent fields with tabs, but QTT can with '\t'.
As always feel free to contact me if you want to work on any of this
----
The ASCII field separators don't make sense -- they break tools and take you off the narrow waist of Unix. https://www.oilshell.org/blog/2022/02/diagrams.html
A key design point of QTT is that it's a special case of the "lines of text" narrow waist. CSV does not have that property, because fields can contain embedded newlines. And unlike TSV, QTT cells can contain arbitrary values.
QSN isn't intended to be tied to Rust in any way (and isn't), while RSON says it uses the Serde data model.
This gets at an issue I have been having a hard time explaining, mentioned here:
http://www.oilshell.org/blog/2022/03/backlog-arch.html
That is, narrow waists are necessarily a COMPROMISE. JSON is a compromise, and Rust users will be equally unhappy as Lua or Erlang users. That is a feature and not a bug for something meant of interoperability. You are "stuck with" the lowest common denominator, but that's what enables interop.
(The lowest common denominator of them all is Unix byte streams.)
I contrast "monoglot" serialization formats like Python pickle an Go .gob with language-independent formats like JSON, TSV, and HTML. The wisdom of JSON is that Crockford specified it independently of JavaScript.
But both are useful.
It's not clear if RSON is meant to be monoglot or polyglot, but it's a huge difference and it seems more monoglot. QSN on the other hand is definitely a polyglot design like JSON, despite being derived from Rust.