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.:
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
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?
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.
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.
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.
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.
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.
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...
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.
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.
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, ...).
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.
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.)
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
"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.
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?
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).
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.
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...
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:
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
106 comments
[ 3.3 ms ] story [ 166 ms ] threadAlthough in my day-to-day, the overhead of field names is rarely problematic.
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.:
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 dataIf 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.
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.
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.
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.
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
Also some editors do show the ascii codes, like notepad++.
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.
And if you're going to use stupid separators, at least use US (0x1F), that's the entire point of that thing.
This is how vim renders an NSV file. And I honestly don't remember the last time I edited a csv file by hand.
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...
But the whole issue with alternative formats is getting widespread software support.
0: http://www.fileviewer.com
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.
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
Examples? (Of libraries and bugs)
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, ...).
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.
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, 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...
Is there a file extension and MIME type for ASCII-delimited files? ASV?
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 :)
It's literally as simple as selecting "csv utf-8" when saving. When opening excel also handles it automatically if it has BOM.
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.
(Without BOM, you heretic.)
Please tell me that you're talking about Excel 2003 ?
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
(How do browsers deal with that ?)
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.
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...
If it helps, for database data (eg not spreadsheet / excel), SQLite is a good alternative single-file format.
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 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.
https://en.wikipedia.org/wiki/SSV
All Delimiters Matter !
That's great. I generate my data using either ZSVs or sometimes ΩSVs
I wonder if a "typed csv" standard could emerge, like `.tscv` eg;
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.
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....
I hope you're wrong about most people not knowing about the movie.
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.
https://arrow.apache.org/docs/python/ipc.html
More info: https://www.confluent.io/blog/avro-kafka-data/
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).
Or if you want column information embedded into the csv itself:
https://www.w3.org/TR/tabular-data-model/#embedded-metadata
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/