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

101–110 of 127 posts

Re: How to share your data effectively

#101

Post by azadder »

pielud wrote: post #96
qw wrote: post #52

Earlier quoted context omitted.

You can specify the field types when opening the file.

Any numbers with leading zeroes, i.e. 0000123, excel will interpret as the integer 123 even if you change the column format to "text". It's infuriating. The only way I found to get around this was to open a new workbook, change the column type to "text" and then paste the data in. I believe this was excel 2010 on windows.

You can add an apostrophe before the leading zeros, and the cell will keep the zeros and still be a number type.

Re: How to share your data effectively

#103

Post by zdw »

crazygringo wrote: post #29

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

Even better, use `pdftotext` from the Xpdf package to turn it into text, so you skip the manual steps.

I do this will all my banking data - dump their PDF's in a folder, a script converts to CSV, then to ledger (cli accounting program) format.

Re: How to share your data effectively

#104

Post by jleader »

kyllo wrote: post #40
shrikant wrote: post #28

Earlier quoted context omitted.

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…

Yes, pipe-delimited format is great, and much easier to parse than comma-delimited. Tab-delimited is also better than comma-delimited, I think. Commas just have far too high a probability of appearing within the data itself, and when you hit a comma that was supposed to be escaped, it adds an extra column to the row, screwing everything up.

Tabs are great, until you try to explain to a non-programmer why your tool doesn't parse their file correctly, even though in the word-processor they're using as a text editor it looks identical to another file that parses correctly.

Re: How to share your data effectively

#106

Post by vacri »

niggler wrote: post #64
UnoriginalGuy wrote: post #54

Earlier quoted context omitted.

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`)

If you start using metacharacters, it's no longer a universal .csv, but an excel-specific one.

Re: How to share your data effectively

#107

Post by eli »

vidarh wrote: post #99
eli wrote: post #68

Earlier quoted context omitted.

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.

There's a slight difference between having to strip "=" from the column values for other applications to handle it, and require those other applications to be able to handle XLSX..

At least XLSX is an open and documented format. Are the rules for Excel CSV parsing written down anywhere?

Re: How to share your data effectively

#108

Post by moron4hire »

I was really hoping to come to this comment page and see a dozen top-level comments claiming you could easily parse a PDF with XYZ tool, because the PDF represented the table with reasonable data, and how could you not know that, you poor excuse for a computer geek, in true HN (and Reddit and 4chan and whatever you your flavor of BBS) fashion.

But no, we are well and truly fucked.

Re: How to share your data effectively

#109

Post by niggler »

eli wrote: post #107
vidarh wrote: post #99

Earlier quoted context omitted.

There's a slight difference between having to strip "=" from the column values for other applications to handle it, and require those other applications to be able to handle XLSX..

At least XLSX is an open and documented format. Are the rules for Excel CSV parsing written down anywhere?

"At least XLSX is an open and documented format."

The documentation diverges from the excel implementation in so many places that there's a special document (which itself is incomplete): http://msdn.microsoft.com/en-us/library/dd922181(v=office.12...

"Are the rules for Excel CSV parsing written down anywhere?"

They are loosely described in ECMA-376 section 18.8.30 under the "General" format code. But like with most details surrounding XLS and XLSX, the documentation is barely coherent and incorrect in some places.

Re: How to share your data effectively

#110

Post by rurounijones »

bloaf wrote: post #105

I suppose I should mention Wolfram's expedition into this domain-the CDF format: http://www.wolfram.com/cdf/compare-cdf/how-cdf-compares.html http://en.wikipedia.org/wiki/Computable_Document_Format

"It is a closed format created by Wolfram Research"

"CDF files can be read using a proprietary CDF Player with a restrictive license"

No thank you.

Post reply on HN