Hacker News Reader

Comment by JulianMorrison | Hacker News Reader

CSV is the biggest pile of nuisance you'd never expect from a seemingly simple data format.

- Header line or none?

- "\n" or "\r\n"?

- Is there a newline at the last line? How about two?

- Escape quotes with doubling or backslash? How about both in the same file? How about both, inconsistently, in different fields? How about quotes including a newline and commas?

- Strings always quoted? Only if necessary? Is ,, a null or an empty string, or an error?

- How about mixed line lengths? Are missing trailing entries nulls? How about multiple data types in a file, with the first field being type, and line length only fixed per type?

I have generally found "TSV with a rule that data cannot represent tabs or newlines, period" as vastly superior.

View on HN

Chartio actually will accept TSV files because it will detect most of these settings. It'll detect the delimiter, the presence of a header line, file encoding, and newline character(s).

I agree TSV is a lot nicer, and has the bonus that Excel will open a TSV file with an .xls extension without any problems (great for sharing!).

Back in the '80s, TSV was the standard way to work with data for us - and for many years Lotus, Quattro Pro and Excel (when it came out) could easily save directly to TSV and open TSV without problems.

Don't know why Excel and Windows no longer recognize the TSV extension as a unique file format, but it is easily fixed without having to go through the .xls extension route [which can be a bit of a pain since it requires identifying delimiters every time one opens a file].

The quickest solution I found is a 2 line batch file for Windows described here [1]. I've used this solution without issues on multiple computers. TSV is my preferred file format for data work. [I generally analyze data in Python and R and use Excel for looking at results or formatting a pretty version to send to others that prefer Excel.]

[1] http://social.technet.microsoft.com/Forums/office/en-US/1890...

TSV has all those same issues. However, you just have less frequent need of a tab, so most of the escaping edge cases never come up.

Those aspects are defined in RFC 4180 - just a lot of systems don't bother. How would you define a simpler data format?

It's a lot easier to administratively ban tabs/newlines in your data than it is to ban commas, and TSV doesn't have escape mechanisms or quoting. So you actually can parse it with line.chomp().split("\t"), and that doesn't break horrendously.

TSV is streamable and minimally wasteful, I rather approve of it. Netstrings are better though if having sized data and nested data is needed. They are proof against all the ills of quoting and escapes.

Largely agree. I was just picking up on "vastly superior". All the issues of line breaks, nulls, column lengths, headers still remain.

You're adding the constraint that you can't use tabs or newlines in your data (to use a newline in your String, you'd need to escape it). In all other cases, you need escaping, and once you've assumed escaping then CSV and TSV aren't really any different.

Also, I should have known there was a RFC for CSV. Too bad that the main call for CSV data is from cranky old versions of Excel in the hands of non technical customers.

Oh God line endings created such havoc for me using Django's FileField model fields. I had to finally subclass it and do a replace of \r\n with \n to simulate opening in universal newline mode. Btw that whole area in Django needs a good reactor.

I should probably package it up for submission upstream.

There is something called ARFF which is basically a CSV with a header that defines how to interpret data, a bit of documentation and a reference implementation.

Reply on news.ycombinator.com