How to share your data effectively • Hacker News Board

Re: How to share your data effectively

#61

Post by merlincorey » Mon, Apr 08, 2013, 9:17 PM UTC

justincormack wrote: post #48

jrajav wrote: post #21

Earlier quoted context omitted.

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.

Well CSV has no real specification, or way to store metadata eg about encoding. It is a terrible format.

Meet RFC 4180 - http://www.ietf.org/rfc/rfc4180.txt

(Yes, it's not an internationally recognized standard, but neither is HTTP or IRC)

Re: How to share your data effectively

#62

Post by acqq » Mon, Apr 08, 2013, 9:18 PM UTC

hmottestad wrote: post #49

RDF, RDF, RDF, RDF, RDF, RDF, RDF. Yes. Please use RDF. And please use a common vocabulary. If you want to know how to do this? Have a look at the UK governments open data portal (which uses RDF): data.gov.uk And their SPARQL (query) endpoint: http://data.gov.uk/sparql

Is that a joke? RDF and SPARQL look exactly like something even mother has problem to love.

http://en.wikipedia.org/wiki/SPARQL

My personal preference is, whenever there's a lot of records to be processed, a tab separated UTF-8 text file. (I admit I am biased as I like to use Perl for first passes).

Re: How to share your data effectively

#63

Post by minimize_me » Mon, Apr 08, 2013, 9:20 PM UTC

http://source.mozillaopennews.org/en-US/articles/introducing... Wasn't this on here a few days ago?

Re: How to share your data effectively

#64

Post by niggler » Mon, Apr 08, 2013, 9:21 PM UTC

UnoriginalGuy wrote: post #54

niggler wrote: post #50

Earlier quoted context omitted.

If you are generating the CSV yourself, save yourself some agony and just wrap the text in ="" $ cat test.csv ="12.34567890124312341234123412341234",="1-5" $ open -a Microsoft\ Excel test.csv

Where did that = sign come from? That isn't in the standard... Quotes are, but wtf? An equals sign? Fucking Excel. JUST TREAT THE NUMBERS LIKE TEXT YOU STUPID PROGRAM. 000001 === 000001 != 1

It uses another auto trick: if the leading character is '=', the result is treated as a formula.

Thus, you can actually write formulas in your CSV and excel will interpret appropriately:

    $ cat test.csv
    =1+1,="1-5"
    $ open -a Microsoft\ Excel test.csv

You should see the value '2' (with content `=1+1`)

Re: How to share your data effectively

#65

Post by r00fus » Mon, Apr 08, 2013, 9:28 PM UTC

shrikant wrote: post #28

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

Tab-seperated is also very common, and I find better - how often are you storing text-formatting within cell for a data interchange? Similar to pipes, tabs are much less common than commas.

Re: How to share your data effectively

#66

Post by justincormack » Mon, Apr 08, 2013, 9:30 PM UTC

UnoriginalGuy wrote: post #59

justincormack wrote: post #48

Earlier quoted context omitted.

Well CSV has no real specification, or way to store metadata eg about encoding. It is a terrible format.

http://tools.ietf.org/html/rfc4180

As I said, no real specification. It reads "This section documents the format that seems to be followed by most implementations". Another part reads "Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all)."

The RFC is a useful collection of anecdotes...

Re: How to share your data effectively

#67

Post by Lambdanaut » Mon, Apr 08, 2013, 9:32 PM UTC

Some "import from PDF" functionality would be immensely valuable. It's not impossible.

Re: How to share your data effectively

#68

Post by eli » Mon, Apr 08, 2013, 9:32 PM UTC

niggler wrote: post #50

eli wrote: post #44

Earlier quoted context omitted.

You left out the absolute worst bit: Excel tries to "helpfully" guess the correct data format for each cell in a CSV file. This can lead to silent data loss. Long strings of numbers are converted to a Numeric type that only stores a certain number of significant figures. The string "1-5" is converted to a Date field with the value "5-Jan"

If you are generating the CSV yourself, save yourself some agony and just wrap the text in ="" $ cat test.csv ="12.34567890124312341234123412341234",="1-5" $ open -a Microsoft\ Excel test.csv

Sure, it's a nice trick, but now it's not really a CSV file any more. If I have to create a file that can only be read by Excel it might as well be XLSX.

Re: How to share your data effectively

#69

Post by hmottestad » Mon, Apr 08, 2013, 9:33 PM UTC

jessriedel wrote: post #51

hmottestad wrote: post #49

RDF, RDF, RDF, RDF, RDF, RDF, RDF. Yes. Please use RDF. And please use a common vocabulary. If you want to know how to do this? Have a look at the UK governments open data portal (which uses RDF): data.gov.uk And their SPARQL (query) endpoint: http://data.gov.uk/sparql

Is this what you're talking about? http://en.wikipedia.org/wiki/Resource_Description_Framework I'm interested in knowing a little more about why you like it so much.

RDF is a graph based data format (serialised to XML). It is based on triples. Such as: Peter hasParent Paal.

To query RDF, we use the graph query language SPARQL. You create a graph with variables in it, and the query engine will find subgraphs that match the graph.

On top of this we can do reasoning (based on 1st order logics and datalog). With RDFS (simple) and OWL (advanced).

And to bring all this together there are many open vocabularies and open data sets linked together. One notable one: DBpedia (based on wikipedia).

A grand view of linked data: http://lod-cloud.net/versions/2011-09-19/lod-cloud.html

A sample SPARQL query on DBpedia listing software by date:

http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbp...

The actual query:

  select ?a min(?b) (min(?date) as ?date2) where {

  ?a a dbpedia-owl:Software;
     rdfs:label ?b;
     dbpedia-owl:latestReleaseDate ?date.

  } 
  group by ?a
  order by ?date2
  LIMIT 100

Re: How to share your data effectively

#70

Post by hmottestad » Mon, Apr 08, 2013, 9:37 PM UTC

acqq wrote: post #62

hmottestad wrote: post #49

RDF, RDF, RDF, RDF, RDF, RDF, RDF. Yes. Please use RDF. And please use a common vocabulary. If you want to know how to do this? Have a look at the UK governments open data portal (which uses RDF): data.gov.uk And their SPARQL (query) endpoint: http://data.gov.uk/sparql

Is that a joke? RDF and SPARQL look exactly like something even mother has problem to love. http://en.wikipedia.org/wiki/SPARQL My personal preference is, whenever there's a lot of records to be processed, a tab separated UTF-8 text file. (I admit I am biased as I like to use Perl for first passes).

Nope. Not a joke. Lots of love from all over the world: http://lod-cloud.net/

The number one difference between RDF and CSV (TSV in you case) is that it's understandable by a computer. CSV is simple parsable, while RDF comes with all the meta data the computer needs.