News for hackers. Stuff that matters.
Re:How to share your data effectively by onemorepassword on Monday April 08, 2013 @03:43PM (#5514026)
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 could be read in any software except client's Excel versions.

Re:How to share your data effectively by eli on Monday April 08, 2013 @04:51PM (#5514506)
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 by niggler on Monday April 08, 2013 @05:01PM (#5514587)
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
Re:How to share your data effectively by UnoriginalGuy on Monday April 08, 2013 @05:14PM (#5514655)
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

Re:How to share your data effectively by niggler on Monday April 08, 2013 @05:21PM (#5514704)
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 by vacri on Monday April 08, 2013 @09:46PM (#5515987)
If you start using metacharacters, it's no longer a universal .csv, but an excel-specific one.
Re:How to share your data effectively by eli on Monday April 08, 2013 @05:32PM (#5514776)
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 by vidarh on Monday April 08, 2013 @08:21PM (#5515656)
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..
Re:How to share your data effectively by eli on Monday April 08, 2013 @10:27PM (#5516105)
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 by niggler on Monday April 08, 2013 @10:43PM (#5516144)
"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 by qw on Monday April 08, 2013 @05:05PM (#5514613)
You can specify the field types when opening the file.
Re:How to share your data effectively by onemorepassword on Tuesday April 09, 2013 @03:06AM (#5516828)
Except that when users click on or download a csv-file, it Excel opens it automatically, bypassing any options and not even bothering to check if the contents of the csv file matches its defaults.

This is 90% of use cases with regular users.

For the other 10%, the interface for specifying the field types sucks, doesn't cover certain options and you can't set your preferences.

Imagine being a user and having to do that every day in order to import your daily whatever report, and having to start all over if you make one little mistake.

And that's just for the options Excel supports. Don't even get me started on all the other quirks. Ever tried to read a csv file who's first line starts with "ID"?

Re:How to share your data effectively by acdha on Monday April 08, 2013 @08:26PM (#5515674)
… and precisely how many users do you think know to do this? They might if it's happened before, been correctly diagnosed and explained before and it's obvious as soon as you open the file. It's more common, however, that the Excel bug only causes data loss or invalid results in a percentage of records and, when people notice it, it's corrected by hand.

If you need to do this a lot, generate xlsx - it's not that hard and there are decent libraries for most languages. Relying on users correctly setting column types every time is like making your daily commute depend on having airbags.

Re:How to share your data effectively by pielud on Monday April 08, 2013 @08:12PM (#5515612)
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.

Re:How to share your data effectively by azadder on Monday April 08, 2013 @08:27PM (#5515678)
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 by mirkules on Monday April 08, 2013 @05:58PM (#5514910)
Indeed. I don't know what version of Excel the parent is using, but Excel for Mac 2010 handles csv files just fine, even ones that have weird delimiters. I'd love to see an example of a non-readable csv file.

Edit: I haven't tried different Unicodes yet, so granted that may not work.

Re:How to share your data effectively by niggler on Monday April 08, 2013 @03:59PM (#5514125)
'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 by jrajav on Monday April 08, 2013 @03:51PM (#5514067)
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 by thomasz on Monday April 08, 2013 @04:31PM (#5514362)
"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 by justincormack on Monday April 08, 2013 @04:55PM (#5514540)
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 by UnoriginalGuy on Monday April 08, 2013 @05:16PM (#5514673)
Re:How to share your data effectively by justincormack on Monday April 08, 2013 @05:30PM (#5514764)
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 by anonydsfsfs on Tuesday April 09, 2013 @12:11AM (#5516404)
The HTTP 1.0 RFC says the same thing:

http://tools.ietf.org/html/rfc1945#page-4

"This specification describes the features that seem to be consistently implemented in most HTTP/1.0 clients and servers."

Is HTTP not a real specification either?

Re:How to share your data effectively by justincormack on Tuesday April 09, 2013 @03:40AM (#5516898)
And then laid down which ones would be used and exactly how, and how to specify that you conform. Http is just describing process of how the spec came about. Because there is no metadata in csv you cannot even say how or if you conform.
Re:How to share your data effectively by merlincorey on Monday April 08, 2013 @05:17PM (#5514683)
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 by npsimons on Monday April 08, 2013 @08:01PM (#5515559)
I guess the Office team would rather work on the metro ribbon.

Of course they would! They've already achieved their goal with Excel: make it a lock-in product. In this case, they did it by breaking CSV compatibility, thereby forcing everyone to use XLSX.

Re:How to share your data effectively by niggler on Monday April 08, 2013 @04:45PM (#5514463)
'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 by bjhoops1 on Monday April 08, 2013 @04:49PM (#5514493)
> I guess the Office team would rather work on the metro ribbon.

Zing! Thank you for that.

Re:How to share your data effectively by Someone on Monday April 08, 2013 @04:14PM (#5514241)
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 by shrikant on Monday April 08, 2013 @04:18PM (#5514258)
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 by prodigal_erik on Monday April 08, 2013 @05:57PM (#5514906)
Then you run into ASCII art some end user made, or pre-Unicode text from Scandinavia where 0x7C was the code point of a letter. Commas make it pretty obvious which tools are unusably broken, where very rare characters let these bugs go undetected far too long.
Re:How to share your data effectively by coldtea on Monday April 08, 2013 @04:46PM (#5514469)
And then you get sent the file:

Statistics on 100000 UNIX shell one liners!

Re:How to share your data effectively by kyllo on Monday April 08, 2013 @04:48PM (#5514487)
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.

Re:How to share your data effectively by jleader on Monday April 08, 2013 @08:41PM (#5515730)
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 by r00fus on Monday April 08, 2013 @05:28PM (#5514753)
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 by grannyg00se on Tuesday April 09, 2013 @12:39AM (#5516471)
I also find the C in CSVs to be needlessly limiting. Especially since ASCII already comes defined with characters for record separator (30), unit separator(31), and group separator(29).
Re:How to share your data effectively by qw on Monday April 08, 2013 @05:15PM (#5514665)
Excel and other tools can handle commas if you use quoted fields. Unfortunately this generates a new problem when you need to escape quotes inside a quoted field. There is no universal escape method.
Re:How to share your data effectively by eksith on Monday April 08, 2013 @04:54PM (#5514531)
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 by nickm12 on Monday April 08, 2013 @03:58PM (#5514114)
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.