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

How to share your data effectively

caitlinrivers.com

Post reply on HN
Print view View original

21–30 of 127 posts

Re: How to share your data effectively

#21

Post by jrajav »

onemorepassword wrote: post #20

My preference is .csv, because it can be read by almost any program. Except the one most used for it: Excel. Excel sucks at csv, especially if the recipients have various internationalized versions. It can't deal properly with multi-line strings and spaces, but especially the internationalization is hell (semi-colons instead of commas, decimal separators, date formats). Many times have I dealt with CSV-exports that c…

From someone currently dealing with Excel's CSV writing for a business application, I heartily concur. There is a decade old bug (at least) where the CSV save as format does not properly handle Unicode, and there's no simple way to handle it down the pipeline. I guess the Office team would rather work on the metro ribbon.

Re: How to share your data effectively

#22

Post by kimagure »

Maybe it's a naive question, but why shouldn't you use quotes to encapsulate things that are meant to be text so that you can catch things like white space?

Also that do people use formats other than comma and tab delimited?

My own experience with CSV is dealing with my own data and instrument readouts (which are mostly always tab delimited with a header section) so I don't know.

Re: How to share your data effectively

#23

Post by nickm12 »

onemorepassword wrote: post #20

My preference is .csv, because it can be read by almost any program. Except the one most used for it: Excel. Excel sucks at csv, especially if the recipients have various internationalized versions. It can't deal properly with multi-line strings and spaces, but especially the internationalization is hell (semi-colons instead of commas, decimal separators, date formats). Many times have I dealt with CSV-exports that c…

Agreed, CSV sucks if you data contains punctuation. UTF-8 encoded tab-delimited tables are preferred. These are kind of a pain to import into Excel (on Mac at least) because you have to specify exactly what format they are in, but it can be done and there is no worry about quoting conventions and so forth unless your data contains literal tab characters that are important.

Re: How to share your data effectively

#24

Post by niggler »

onemorepassword wrote: post #20

My preference is .csv, because it can be read by almost any program. Except the one most used for it: Excel. Excel sucks at csv, especially if the recipients have various internationalized versions. It can't deal properly with multi-line strings and spaces, but especially the internationalization is hell (semi-colons instead of commas, decimal separators, date formats). Many times have I dealt with CSV-exports that c…

'It can't deal properly with multi-line strings and spaces'

Works fine in excel 2011 for mac:

    $ cat test.csv
    abc,"def
    
    ghi",jkl
    mno,pqr,stu
    $ open -a Microsoft\ Excel test.csv
http://imgur.com/CRUJZ57

This is an example of a poorly laid-out menu structure. To force it to do the right thing, you have to wrap text (right click -> format cells -> alignment -> "Wrap text" on).

Basically, the default rendering is supposed to be a single space for any whitespace (tab, newline, space) and "wrap text" forces it to do the right thing.

Shameless plug: I'm actually working on an XLSX parser: http://niggler.github.io/js-xlsx/

Re: How to share your data effectively

#25

Post by itafroma »

kimagure wrote: post #22

Maybe it's a naive question, but why shouldn't you use quotes to encapsulate things that are meant to be text so that you can catch things like white space? Also that do people use formats other than comma and tab delimited? My own experience with CSV is dealing with my own data and instrument readouts (which are mostly always tab delimited with a header section) so I don't know.

Where did you read that you shouldn't use quotes to encapsulate text? RFC 4180 (http://tools.ietf.org/html/rfc4180) says you may.

Many CSV generators and CSV parsers don't conform to RFC 4180, however, but if you're planning on transmitting data using CSV, it's simple enough to say "use an RFC 4180-compliant parser."

Re: How to share your data effectively

#26

Post by Someone »

onemorepassword wrote: post #20

My preference is .csv, because it can be read by almost any program. Except the one most used for it: Excel. Excel sucks at csv, especially if the recipients have various internationalized versions. It can't deal properly with multi-line strings and spaces, but especially the internationalization is hell (semi-colons instead of commas, decimal separators, date formats). Many times have I dealt with CSV-exports that c…

Nice example: http://www.biomedcentral.com/1471-2105/5/80 "Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics"

I am fairly sure that other spreadsheets will make such errors, too, though. I recently saw one in Numbers that depended on the amount of data pasted (pasting hundreds of rows seemed to disable the smartness)

Re: How to share your data effectively

#28

Post by shrikant »

onemorepassword wrote: post #20

My preference is .csv, because it can be read by almost any program. Except the one most used for it: Excel. Excel sucks at csv, especially if the recipients have various internationalized versions. It can't deal properly with multi-line strings and spaces, but especially the internationalization is hell (semi-colons instead of commas, decimal separators, date formats). Many times have I dealt with CSV-exports that c…

I've always found the C in CSVs to be needlessly limiting. I'm not aware of any tool that contextualise commas in addresses and other "free text" fields that might form part of the data dump/extract.

For this reason, I am strongly in favour of pipe-separated values in such files. The probability that a data extract has a pipe symbol in any data field is quite small (in my experience, it's been 0 so far).

The first thing I do in any Windows system I have to use on a regular basis is to change the the system separator to '|' in the Regional Settings..

Re: How to share your data effectively

#29

Post by crazygringo »

As long as the PDF contains text (and isn't just scans of paper), it's usually not TOO hard to select and copy the relevant tables/text, paste into a text editor, and use regex's to transform it into whatever you want (CSV, SQL insert statements, etc.)

Since there's no universal data format (e.g. the data equivalent of PDF), I pretty much assume that whatever format data comes in, I'll have to be doing regex transforms to it in a text editor, in order to import it wherever I want.

So as long as it's copy-pastable as text, PDF doesn't seem appreciably worse than pretty much any other format.

Re: How to share your data effectively

#30

Post by kimagure »

itafroma wrote: post #25
kimagure wrote: post #22

Maybe it's a naive question, but why shouldn't you use quotes to encapsulate things that are meant to be text so that you can catch things like white space? Also that do people use formats other than comma and tab delimited? My own experience with CSV is dealing with my own data and instrument readouts (which are mostly always tab delimited with a header section) so I don't know.

Where did you read that you shouldn't use quotes to encapsulate text? RFC 4180 ( http://tools.ietf.org/html/rfc4180 ) says you may. Many CSV generators and CSV parsers don't conform to RFC 4180, however, but if you're planning on transmitting data using CSV, it's simple enough to say "use an RFC 4180-compliant parser."

yeah, i was just wondering why the author kind of explicitly recommended against using white space when most parsers (including MS excel in my experience) handle white space encapsulated in quotes just fine
Post reply on HN