3 ms·

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

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.

jwilliams 13y ago
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?
- JulianMorrison 13y ago
  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.
  - jwilliams 13y ago
    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.
- JulianMorrison 13y ago
  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.
zbanks 13y ago
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!).
- IgniteTheSun 13y ago
  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/18907b37-503e-4d9f-a27a-8ee4c6248e9c/excel-2010-wont-open-tsv-files http://social.technet.microsoft.com/Forums/office/en-US/1890...
mattdeboard 13y ago
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.
mbq 13y ago
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.

jwilliams 13y ago

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?

JulianMorrison 13y ago
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.
- jwilliams 13y ago
  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.
JulianMorrison 13y ago
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.

zbanks 13y ago

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!).

IgniteTheSun 13y ago
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/18907b37-503e-4d9f-a27a-8ee4c6248e9c/excel-2010-wont-open-tsv-files http://social.technet.microsoft.com/Forums/office/en-US/1890...

mattdeboard 13y ago

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.

mbq 13y ago

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.