Hacker News Reader

Comment by DeusExMachina | Hacker News Reader

You can certainly reject the file, but that's not going to make your user happy. The (sad) reality is that you have often to deal with these edge cases in software.

The user does not care that the CSV is not proper. He just wants to open it, and if it doesn't work, they will blame your program, not the source of the CSV.

And you only control the former. I have had to deal with a CSV export from a web service that was "not proper". I notified their support, but that problem was never fixed.

Luckily, the user in that case was just me. But if I was making software for someone else, blaming the right source of the problem would have not taken me anywhere.

View on HN

There are different use cases. What you say is not really CSV specific and true about most data sets: you receive data sets that are not compliant to whatever spec you expect. Different projects must handle it in different ways, either making some efforts to correct errors or not.

In some cases data users wield enormous power over data submitters (e.g., many for-record safety analyses or real-time systems). If any part of the data collection process is suspect the sample is likely to get tossed. And for spec compliance, if a data set does not pass perfectly through a validator the sender will likely get some sort of a "shame on you" nastygram.

If you put a phone number form and the user put letters inside, you will just say "invalid number". You won't try to parse it.

Same for invalid CSV.

There is nothing wrong with this. You can't accept all the garbage from the user.

The real problem is when the user complains "but it works fine in Excel." If it does work elsewhere then in the user's eyes it's your app's fault that it cannot read a working file.

Yes, it does work in Excel but it's because of 40 years of various edge case fixes that you just cannot replicate in their entirety in your app.

Really what this comes down to is that the CSV "standard" is in actuality an undocumented standard with one reference implementation - Excel. If it works in Excel, it's valid CSV, if it doesn't, then it's not. It's a sad state of affairs, and probably one reason to avoid working with CSV unless absolutely necessary, but there's no getting around that fact.

This is so wrong it’s hard to figure out where to start. Excel doesn’t make proper CSVs and it’s not a reference for anything. I can change the extension of an html file to CSV and open it in excel, it’s still an html file.

Excel makes proper CSVs insofar as when you say that you allow people to import CSV files into your application, the vast vast majority will be using Excel to create / manipulate those CSV files (and if they don't, whatever tool they DO use will have made efforts to work consistently with Excel).

What any other spec actually says is irrelevant. For most non-technical users, anything Excel outputs as a CSV is a CSV file (and anything you output that doesn't work with Excel is not)

Excel is certainly a reference for what your end-users expect your software to be able to do.

The main reason you'd choose CSV is precisely that you can open it in Excel.

I use CSV (really tsv) because big data tools can open them extremely easily, because they're human readable, and because they're easy for my software stack to parse and use. I do most of my data processing for local consumption on a repl-like tool. We have a defined configuration for TSV we use internally that is lossless in terms of storing and extracting a number of complex data types. Embedded JSON with embedded separators in that JSON? No problem.

Originally the xls format was the only open format in the office family and it was actually much easier to create xls files than to deal with the stupidity of csv

They're all open now, but the spec is monstrously large and difficult to work with. Definitely not easier to do than CSV.

.xlsx is not .xls

Isn't xls a binary format? I can't imagine that's more pleasant to work in.

I take a defined format like .xls over an unspecified de facto ‘standard’ like .csv anytime. In many locales .csv files don’t even contain comma separated values because the comma is the decimal symbol already. It’s a mess, half of the time Microsoft can’t make it work either even when you only use their software.

You can use sep for that.

> Yes, it does work in Excel but it's because of 40 years of various edge case fixes that you just cannot replicate in their entirety in your app.

So given its impossible to compete totally with Excel, how do we decide which of these edge cases are worth our time to implement?

The problem is that for many products, if it can't integrate with an Excel workflow, it is dead-on-arrival.

I think his point is that it's not garbage from the user but garbage that the user needs to use.

It's not as simple as invalid input or a bad copy/paste. The user likely cannot correct the CSV.

But every piece of software that accepts csv files cannot be expected to include an editor/data Wrangler mode that will allow the user to resolve encoding errors, ambiguities and inconsistencies, nor can it be expected to resolve those problems automagically

Not only can they be, they are. Or they stop accepting CSV.

> If you put a phone number form and the user put letters inside, you will just say "invalid number". You won't try to parse it.

Wait, what? Phone numbers could always include letters. Many businesses get phone numbers that include or consist of words relevant to their business, to make them memorable. And I've known people whose personal number happens to contain a word or words, so they give the letter version out because it's easy to remember. If a phone number field doesn't include simple logic to map letters into the proper numbers, that's just lazy programming.

That's an american way of seing things (and is only a thing in very few countries), because all your letters are on the num pad. But 555-فودكا is not going to work.

And what next, do you accept exotic unicode numbers ? Letters that you casefold to something that can make to a number ?

Again, you draw the line somewhere. There is no right place for the line, it depends of your business, but there is always a line of what you accept as a user input.

Being offended won't help you on HN, and calling out people names either.

The letters all correspond to numbers. I don't think most applications with phone numbers really need to support putting 1-800-FLOWERS instead of the real number.

Wait what? There are countries that put letters into phone NUMBERS?

This is what the average phone bad looks like in the United States:

http://www.pachd.com/free-images/technology-images/phone-dia...

There is a standard, universal mapping of letters onto numbers. This allows companies to advertise their phone number as something memorable like 1-800-CASH-4-AU and everyone knows how to dial it. People don't even need to remember the mapping of letters, because they're printed on every keypad.

In the US, phone numbers started with 2 letters well into the 1960s. They were usually the first 2 letters of the street name the nearest telephone exchange was on. My grandfather's address book was full of numbers still written that way.

The numbers all have three or four letters above them and people (mostly businesses, really) will sometimes get a number that spells out a memorable mnemonic. That's all he's talking about.

Well there are the A B C and D DTMF tones not sure if those technically count as numbers and from memory they are without the ITU spec for a phone numbers.

Used to be common in the US. You'd have two letters for the exchange, then five digits for the telephone number.

You're right, but non-technical clients don't care. It's a little different to them.

With a ZIP code in the US, it consists of 5 digits that form a unique number for a geographical area with 4 more digits that represents a sub-area within the 5 digit ZIP. But there are no instances where there can be a letter. That's a hard fact. If you put 4308A-65XK as the ZIP, the post office or delivery service will not know how to deliver the mail without trying to cross reference address with possible valid ZIP codes.

Invalid CSV is murky, because CSV is a serialization format. That is, invalid CSV may not play well with a program that accepts well-formed CSV, but a human may still be able to parse it with no problem. Returning "invalid ZIP Code" makes sense. The client would understand that letters do not belong in a ZIP Code.

If you've never had to deal with a nightmare client of that sort, hats off to you.

Perhaps the problem is with the client relationship.

Why not explain to the client that you will need to put in manual or custom work to clean up the CSV or batch of CSVs and charge for the extra time writing a script or cleanup?

Literally the only time this may not apply is if you are writing a general purpose program.

You can also have clients that don't have a working OS. But you can't fix that. You need to choose a limit after which you say "no, I won't do that, get you s* together"

Well, it all depends on the money, doesn't it?

The thing is, a lot (all?) of these assumptions are not part of the CSV standard. Things you view as "garbage" might still be technically valid CSV.

Out of interest what do you regard as a valid phone number?

as defined in E.164 (https://en.wikipedia.org/wiki/E.164)

How could you possibly handle a single file that mixes and matches encodings? I'd think opening that in any software is going to result in some garbled characters.

Why not create a transformation function which uses some heuristics to take a non-compliant sort-of-CSV and turn it into a CSV.

Doing so doesn't mean that it is CSV, it just means you can parse it.

That non-compliant sort-of-CSV can be cleanly (i.e. without errors) decoded as several "dialects", producing multiple different results, of which only one (or none) will be correct.

Why would you do that?

I guess I would do it, because I could then be explicit about the differences between the two, and explicit about how I would decode something if it was indeterminate.

Reply on news.ycombinator.com