Hacker News Reader

I used to be a data analyst at a Big 4 management consultancy, so I've seen an awful lot of this kind of thing. One thing I never understood is the inverse correlation between "cost of product" and "ability to do serialisation properly".

Free database like Postgres? Perfect every time.

Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting, escaping or the difference between \n and \r and who thinks it's clever to use 0xFF as a delimiter, because in the Windows-1252 code page it looks like a weird rune and therefore "it won't be in the data".

> Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting...

It's because about a certain size, system projects are captured by the large consultancy shops, who eat the majority of the price in profit and management overhead...

... and then send the coding work to a lowest-cost someone who has never heard of quoting, etc.

And it's a vicious cycle, because the developers in those shops that do learn and mature quickly leave for better pay and management.

(Yes, there's usually a shit hot tiger team somewhere in these orgs, but they spend all their time bailing out dumpster fires or landing T10 customers. The average customer isn't getting them.)

Just a nitpick about consultancy shops -- I've had a chance of working in one in eastern europe and noticed that it's approach to quality was way better than client's. It also helped that client paid by hours, so consultancy company was incentivized to spend more time on refactorings, improvals and testing (with constant pushback from client).

So I don't buy the consultancy company sentiment, it always boils down to engineers and incentives.

How big was the one you worked for?

In my experience, smaller ones tend to align incentives better.

Once they grow past a certain size though, it's a labor arbitrage game. Bill client X, staff with resources costing Y (and over-represented), profit = X-Y, minimize Y to maximize profit.

PwC / IBM Global Services wasn't offering the best and brightest. (Outside of aforementioned tiger teams)

I agree with you in general, although my case was the other way around. My company was 10k+ people. But my client was probably the most technically advanced company at that time, with famously hard interviews for their own employees. My employer also didn't want to lose the client (it was beginning of collaboration), and since everyone wanted to work there (and move to US+California) my shop applied pretty strong filter for their own heads, even before sending them to client's vendor-interview.

And client was very-very happy with the quality, and that we didn't fight for promotions and could maintain very important, but promotion-poor projects. Up to the point that client trusted to completely gave couple of projects fully to my shop. When you don't need to fight for promotions, code quality also improves.

"Enterprise software" has been defined as software that is purchased based on the decisions of people that will not use it. I think that explains a lot.

Yep, we had a constant tug of war between techies who wanted to use open-source tools that actually work (Linux, Postgres, Python, Go etc.) and bigwigs who wanted impressive-sounding things in Powerpoint decks and were trying to force "enterprise" platforms like Palantir and IBM BigInsights on us.

Any time we were allowed to actually test one of the "enterprise" platforms, we'd break it in a few minutes. And I don't mean by being pathologically abusive, I mean stuff like "let's see if it can correctly handle a UTF-8 BOM...oh no, it can't".

Try to live in a country where "," is the decimal point. Of course this causes numerous interoperability issues or hidden mistakes in various data sets.

There would have been many better separators... but good idea to bring formatting into it as well...

There was a long period of my life that I thought .csv meant cemicolon separated because all I saw was cemicolon separated files and I had no idea of the pain.

Although it is spelled "semicolon," so that doesn't quite fit.

CMYK -- Cyan, Magenta, Yellow, blacK :)

(of course it originally stood for "key", but you don't see that much anymore)

Not sure if they still do this, but Klarna would send us ", " separated files. If there wasn't a space after the comma then it was to be read as a decimal point. Most of the CSV parser don't/didn't allow you to specify multi-character separators. In the end I just accepted that we had one field for krona and for öre and most fields would need to have a leading space removed.

Microsoft did this very extensively. Many Non-English versions of Excel do save CSV-files with a semicolon as a separator and it probably was handled differently too in normal Excel files. But it goes even further, it affected their scripting languages even to this day with newer languages like their BI script (forgot the name of the language). For example, parameters of function calls aren't separated by ',' anymore and ';' is used instead. But only in the localized versions.

That of course means that you have to translate these scripts depending on the locale set in your office suite, otherwise they are full of syntax errors...

Many English languages use ';' to end statements instead of '.'.

Many European languages use '.' to end statements, Prolog (France) for example, but use ';' to separate arguments.

TSV should do it for you. Been there done that.

There are better separators included in ASCII, but not used as often: 28 File Separator, 29 Group Separator, 30 Record Separator and 31 Unit Separator.

> Then there will be your data, but they will format currency values as prettified strings, for example "34 593,12 USD", instead of producing one column with a number and another with currency.

To be fair, that's not a problem with CSV but with the provider's lack of data literacy.

Yeah, you can also use Parquet/JSON/protobuf/XLSX and store numbers as strings in this format. CSV is just a container.

But somehow CSV is the PHP of serialization formats, attracts the wrong kind of developers and projects.

I definitely wouldn't say that. I saw a lot of weird stuff in Excel files, and there's the whole crowd only giving you data as PDFs.

I worked in a web shop which had to produce spreadsheets which people wanted to look at in Excel. I gave them so many options, and told each client to experiment and choose the option which worked for them. In the end, we had (a) UTF-8 CSV, (b) UTF-8 CSV with BOM, (c) UTF-16 TSV, (d) UTF-8 HTML table with a .xlsx file extension and a lying Content-Type header which claimed it was an Excel spreadsheet.

Option a worked fine so long as none of the names in the spreadsheet had any non-ASCII characters.

Option d was by some measures the worst (and was definitely the largest file size), but it did seem to consistently work in Excel and Libre Office. In fact, they all worked without any issue in Libre Office.

> I can't point to a strict spec and say "you are doing this wrong", because there is no strict spec.

Have you tried RFC 4180?

https://www.ietf.org/rfc/rfc4180.txt

I've written a commercial, point and click, data wrangling tool (Easy Data Transform) that can deal with a lot of these issues:

-different delimiters (comma, semi-colon, tab, pipe etc)

-different encodings (UTF8, UTF16 etc)

-different line ending (CR, LF, CR+LF)

-ragged rows

-splitting and merging columns

And much more besides.

However, if you have either:

-line feeds and/or carriage returns in data values, but no quoting

or

-quoting, but quotes in data values aren't properly handled

Then you are totally screwed and you have my sympathies!

I agree and as a result I have completely abandoned CSV.

I use the industry standard that everyone understands: ECMA-376, ISO/IEC 29500 aka .xlsx.

Nobody has any problems producing or ingesting .xlsx files. The only real problem is the confusion between numbers and numeric text that happens when people use excel manually. For machine to machine communication .xlsx has never failed me.

Off the top of my head:

https://learn.microsoft.com/en-us/office/troubleshoot/excel/...

Now you might argue that ECMA-376 accounts for this, because it has a `date1904` flag, which has to be 0 for 1900-based dates and 1 for 1904-based dates. But what does that really accomplish if you can’t be sure that vendors understand subtleties like that if they produce or consume it? Last time I checked (maybe 8 years ago), spreadsheets created on Windows and opened on Mac still shifted dates by four years, and the bug was already over twenty years old at that time.

And the year-1904 issue is just the one example that I happen to know.

I have absolutely zero confidence in anything that has touched, or might have touched, MS Excel with anything short of a ten-foot pole.

Parsing Excel files in simple data interchange use cases that don't involve anyone manually using spreadsheets is an instance of unnecessary complexity. There are plenty of alternatives to CSV that remain plaintext, have much broader support, and are more rigorous than Excel in ensuring data consistency. You can use JSON, XML, ProtoBuf, among many other options.

But everyone already has a GUI installed for editing xlsx files...

Which introduces even more problems when manually editing files is out of scope.

I was recently writing a parser for a weird CSV. It had multiple header column rows in it as well as other header rows indicating a folder.

This RFC maybe?

https://www.ietf.org/rfc/rfc4180.txt

The RFC explicitly does not define a standard

I hate CSV too. If I have to use it, I'll live with TSV or some other special-charter delimited format.

I'd much rather it be something that is neither used in normal* text/numbers, nor whitespace, thus non-printable delimiters wins for me.

* Don't mind me extending 'normal' here to include human-written numbers with thousand seperators.

If only there were character codes specifically meant to separate fields and records.... we wouldn't have to worry so much about quoted commas or quoted quotes.

There's just no such thing as a delimiter which won't find its way into the data. Quoting and escaping really are the only robust way.

You can disallow all control characters (ASCII < 32) other than CR/LF/TAB, which is reasonable. I don't know of any data besides binary blobs which uses those. I've never heard of anyone inlining a binary file (like an image) into a "CSV" anyway.

If you disallow control characters so that you can use them as delimiters, then CSV itself becomes a "binary" data format - or to put it another way, you lose the ability to nest CSV.

It isn't good enough to say "but people don't/won't/shouldn't do that", because it will just happen regardless. I've seen nested CSV in real-life data.

Compare to the zero-terminated strings used by C, one legacy of which is that PostgreSQL doesn't quite support UTF-8 properly, because it can't handle a 0 byte in a string, because 0 is "special" in C.

Nested CSVs as you've seen in real-life data are a good counterexample, thanks for providing it.

So have a way to escape those control characters.

Right, but the original point I was responding to is that control characters are disallowed in the data and therefore don't need to be escaped. If you're going to have an escaping mechanism then you can use "normal" characters like comma as delimiters, which is better because they can be read and written normally.

But a comma is much more likely to need to be escaped.

It's good for a delimiter to be uncommon in the data, so that you don't have to use your escaping mechanism too much.

This is a different thing altogether from using "disallowed" control characters, which is an attempt to avoid escaping altogether - an attempt which I was arguing is doomed to fail.

That isn't solving anything, just changing the problem. If I want to store a string containing 0x1C - 0x1F in one of the columns then we're back in the exact same situation while also losing the human readable/manually typeable aspect people seem to love about CSV. The real solution is a strict spec with mandatory escaping.

Not for text data. Those values are not text characters like , or " are, and have only one meaning. It would be like arguing that 0x41 isn't always the letter "A".

For binary files, yeah but you don't see CSV used there anyway.

There are several ways how a control character might inadvertently end up inside a text corpus. Given enough millions of lines, it’s bound to happen, and you absolutely don’t want it to trip up your whole export because of that one occurrence. So yes, you have to account for it in text data, too.

Sure, but that's orders of magnitude less likely to happen than a comma ending up inside a text corpus.

So in addition to losing human readability, we are also throwing away the ability to nest (pseudo-)CSVs? With comma delimiters, I can take an entire CSV document and put it in 1 column, but with 0x1C-0x1F delimiters and banning non-text valid utf-8 in columns I no longer can. This continues to be a step backwards.

No reason you can't escape those special characters.

Then we're back to my original response of it doesn't solve anything: https://news.ycombinator.com/item?id=43495217

The idea that binary data doesn't go in CSVs is debatable; people do all sorts of weird stuff. Part of the robustness of a format is coping with abuse.

But putting that aside, if the control chars are not text, then you sacrifice human-readability and human-writability. In which case, you may as well just use a binary format.

I've dealt with a few cases of CSVs including base64-encoded binary data. It's an unusual scenario, but the tools for working with CSVs are robust enough that it was never an issue.

True, but very few people compose or edit CSV data in Notepad. You can, but it's very error-prone. Most people will use a spreadsheet and save as CSV, so field and record separator characters are not anything they would ever deal with.

There are lots of 8-bit mostly-ASCII character sets that assign printable glyphs to some or all of the codepoints that ASCII assigns to control characters. TeX defined one, and the IBM PC's "code page 437" defined another.

CSV is a data-exchange format.

But it is terrible at that because there is no widely adhered to standard[1], the sender and receiver often disagree on the details of what exactly a CSV is.

[1]: yes, I know about RFC 4180. But csvs in the wild often don't follow it.

Agreed that that CSV construction isn't consistent even with standards.

That footprint seems to be dozens of variations to work with to find a library for?

CSV are universal though, text kind of like markdown, and that is my intended main point.

The only times I hated CSV was when it came from another system I had no control over. For example Windows and their encodings, or some other proprietary BS.

But CSV under controlled circumstances is very simple.

And speaking of Wintendo, the bonus is often that you can go straight from CSV to Excel presentation for the middle management.