Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

CSV as a Data Source

chartio.com

Post reply on HN
Print view View original

41–50 of 53 posts

Re: CSV as a Data Source

#41

Post by zbanks »

JulianMorrison wrote: post #34

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 nu…

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

Re: CSV as a Data Source

#42

Post by zbanks »

funstr wrote: post #35

CSV format does not define encoding. Also customers usually think about CSV as export/import format from/to Microsoft Excel. Unfortunately each Office localization uses different output encoding and delimiters (and date format). And you cannot suppose that the encoding is UTF-8. The most complete importer of CSV file which I have seen is in Open Office. It is also worth mentioning that Excel can import CSV with other…

This also detects the file encoding & delimiters.

Re: CSV as a Data Source

#43

Post by steveklabnik »

nemetroid wrote: post #38
steveklabnik wrote: post #26

Earlier quoted context omitted.

text/csv is defined in RFC 4180: https://tools.ietf.org/html/rfc4180

Strictly, the definition in RFC 4180 mandates ASCII which makes it unusable for many purposes. I guess there's nothing practical stopping you from using another encoding though.

  >      Common usage of CSV is US-ASCII, but other character sets defined
  >      by IANA for the "text" tree may be used in conjunction with the
  >      "charset" parameter.
http://www.rfc-editor.org/rfc/rfc6657.txt

https://www.iana.org/assignments/character-sets/character-se...

Re: CSV as a Data Source

#44

Post by voltagex_ »

guard-of-terra wrote: post #28

Data isn't "locked up" in CSV, rather it is available in CSV. CSV is one of the most open things possible. As a programmer I do a lot of one-time makeshift data reports for other people, and I always use CSV (or precisely, tab separated) because that's what every program happily emits and consumes. If it does not, it's trivial to transform thanks to UNIX sort, awk and uniq.

OK, here's one to do with "just" sort, awk and uniq ;)

http://naa.gov.au/naaresources/govhack-2013/PassengersArriva...

Re: CSV as a Data Source

#45

Post by mattdeboard »

JulianMorrison wrote: post #34

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 nu…

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.

Re: CSV as a Data Source

#46

Post by jwilliams »

JulianMorrison wrote: post #39
jwilliams wrote: post #36

Earlier quoted context omitted.

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 ill…

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.

Re: CSV as a Data Source

#47

Post by sopooneo »

guard-of-terra wrote: post #33
zbanks wrote: post #30

Earlier quoted context omitted.

Once your CSV (or TSV) files start having quoted fields, they become very tricky to parse using standard multi-purpose tools like sort, awk, & uniq. It's hard enough when you have delimiters in quoted fields, but dealing with quoted newlines starts to become unreasonable, especially for line-based tools. CSV files, as you say, are absolutely wonderful to create. Problems come up when you try to parse files other peop…

With tab separated values this is not a big problem in practice. On the other hand, you can sort tsv, but you can't sort quoted csv.

Why not? I ask in earnest.

Re: CSV as a Data Source

#48

Post by Ecio78 »

jeremysmyth wrote: post #8

MySQL has supported CSV as a data source for quite some time: http://dev.mysql.com/doc/refman/5.6/en/csv-storage-engine.ht... You can run full SQL queries directly against a text file as if it was a table.

Microsoft SQL Server has a couple of methods for querying CSV/XLS(X) files (I remember at least OPENROWSET and BULK INSERT), but sometimes it's a bit tricky to use/configure (have the right OLEDB driver, use INI files for configuration etc..) or the behaviour is not very consistent/well-documented

Edit: of course with BULK INSERT you don't directly query the file, you must load it to a (temp) table

Re: CSV as a Data Source

#50

Post by funstr »

zbanks wrote: post #42
funstr wrote: post #35

CSV format does not define encoding. Also customers usually think about CSV as export/import format from/to Microsoft Excel. Unfortunately each Office localization uses different output encoding and delimiters (and date format). And you cannot suppose that the encoding is UTF-8. The most complete importer of CSV file which I have seen is in Open Office. It is also worth mentioning that Excel can import CSV with other…

This also detects the file encoding & delimiters.

Automatic file encoding detection is only heuristic and cannot be in principle exact. But I do not want to criticize the import functionality. I just wanted to say that CSV is an obsolete format and should not be used for data interchange.
Post reply on HN