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 =""
[ Parent
| on HN ]
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
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.
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?
"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.