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

41–50 of 127 posts

Re: How to share your data effectively

#41

Post by coldtea »

mrtriangle wrote: post #5
yebyen wrote: post #3

There is lots of bad advice in this article. OK maybe the article is shorter than I thought. Here, this is bad: Use short variable names with no whitespace. Underscores are usually a safe bet, so instead of "Number of new tuberculosis cases" use "incident_tb" While it might be clear to the domain experts, I had no idea that "incident_tb" meant "Number of new tuberculosis cases" other than that it was placed next to t…

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.

Obligatory "Excel as a database" link (you'll thank me for it):

http://wyorock.com/excelasadatabase.htm

Re: How to share your data effectively

#42

Post by bjhoops1 »

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.

> I guess the Office team would rather work on the metro ribbon.

Zing! Thank you for that.

Re: How to share your data effectively

#44

Post by eli »

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…

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"

Re: How to share your data effectively

#45

Post by krschultz »

cookiecaper wrote: post #37
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 lackin…

The newest versions of LibreOffice are a lot better at handling xlsx, and it is getting better all the time. I still see some issues, but lately I have had a much better time importing.

Re: How to share your data effectively

#47

Post by eksith »

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…

Same for me. CSVs would be far less likely to mess up the data if they were PSVs instead.

Re: How to share your data effectively

#48

Post by justincormack »

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.

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

Re: How to share your data effectively

#50

Post by niggler »

eli wrote: post #44
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…

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
Post reply on HN