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

31–40 of 127 posts

Re: How to share your data effectively

#31

Post by thomasz »

jrajav wrote: post #21
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.

"Save as CSV" produces text encoded in the users ANSI codepage which is atrocious, Excel 2007+ at least has "Save as Unicode Text" which produces \t separated UTF16 output.

Re: How to share your data effectively

#32

Post by wiggity »

this advice is pertinent even within excel, especially for those who rely on any of its summary features (pivot charts, data analysis, etc). essentially an illustration of the benefits of separating content from presentation.

Re: How to share your data effectively

#33

Post by itafroma »

kimagure wrote: post #30
itafroma wrote: post #25

Earlier quoted context omitted.

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

That's for variable names, not run-of-the-mill text. Variable names (and, in certain situations, dictionary keys) generally can't have spaces, so if you're generating code off of the data set, having to generate variable names (or dictionary keys) without spaces is an additional step.

Re: How to share your data effectively

#34

Post by rayiner »

ambiate wrote: post #15
mrtriangle wrote: post #5

Earlier quoted context omitted.

I agree with you, I actually had a client who demanded that the data in their project be done in excel (with multiple files ranging from 1-3 gb in size!). Both myself and my partner tried exhaustively to use a database or even JSON/XML solutions but the client kept repeating "in the real world business is done in excel!" Needless to say it was a nightmare.

This is seen all the time in Accounting departments. GAAP and STAT financials in spreadsheets with millions of linked documents and macros. A large amount of this deals with business logic being tied directly in Excel. Their data and the constraints around it are embedded into the spreadsheet. Segregating the data from the spreadsheet means adapting the business logic from Excel and maintaining it in two places. (Sou…

Excel is absolutely standard in accounting circles. It has the benefit of allowing accountants to do all sorts of things without knowing how to program.

Re: How to share your data effectively

#35

Post by a_p »

ProPublica recently published an article (with an unseemly title) about the nightmare of scraping data from pdfs and putting it into tables. [1] They wrote some custom software to do the scraping. At the end of the article they mention some software released recently by a Knight-Mozilla OpenNews Fellow, Manuel Aristarán, called Tabula, which is a PDF data table scaper.[2] I haven't tested it yet.

[1] http://www.propublica.org/nerds/item/heart-of-nerd-darkness-...

[2] http://source.mozillaopennews.org/en-US/articles/introducing...

Re: How to share your data effectively

#36

Post by LukeShu »

JustARandomGuy wrote: post #6

Use a flexible file format. My preference is .csv, because it can be read by almost any program. I'll tolerate .xls, but I'm not pleased with .xlsx (not everyone uses Excel!). And please, please, please do not use pdf. Why is .xls tolerated but not .xlsx? XLS and XLSX are both Excel formats, but XLS is a binary blob while XLSX is just a zip containing xml files. I'd much rather parse xml files (XLSX) than the binary…

Third party spreadsheet programs (at least used to) have poor support for XLSX. That's from when it was new, I'd think that popular programs have improved, but that the reputation stuck.

Re: How to share your data effectively

#37

Post by cookiecaper »

JustARandomGuy wrote: post #6

Use a flexible file format. My preference is .csv, because it can be read by almost any program. I'll tolerate .xls, but I'm not pleased with .xlsx (not everyone uses Excel!). And please, please, please do not use pdf. Why is .xls tolerated but not .xlsx? XLS and XLSX are both Excel formats, but XLS is a binary blob while XLSX is just a zip containing xml files. I'd much rather parse xml files (XLSX) than the binary…

My experience is that open-source implementations like LibreOffice have much better support for the older file formats. docx/xlsx/etc may be a "zip of XML files" but it doesn't really matter if the original data is stored as XML or a blob when it comes to the end user's rendering experience -- it's about how the system can interpret that data. And the interpretations of the new file formats are mostly severely lacking.

Re: How to share your data effectively

#38

Post by niggler »

jrajav wrote: post #21
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.

'CSV save as format does not properly handle Unicode'

[MS-XLS] ends up using special codepages (e.g. http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOW...) for representing characters, so you need to make sure the same codepage is used through all of your applications.

Example: For those that have access to windows and macintosh excel, CHAR(240) is the apple logo on mac but something different on windows.

Re: How to share your data effectively

#39

Post by coldtea »

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…

And then you get sent the file:

Statistics on 100000 UNIX shell one liners!

Re: How to share your data effectively

#40

Post by kyllo »

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…

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.

Post reply on HN