$ cat test.csv
="12.34567890124312341234123412341234",="1-5"
$ open -a Microsoft\ Excel test.csvJUST TREAT THE NUMBERS LIKE TEXT YOU STUPID PROGRAM.
000001 === 000001 != 1
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`)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.
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"?
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.
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.
Edit: I haven't tried different Unicodes yet, so granted that may not work.
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/CRUJZ57This 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/
The RFC is a useful collection of anecdotes...
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?
(Yes, it's not an internationally recognized standard, but neither is HTTP or IRC)
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.
[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.
Zing! Thank you for that.
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)
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..
Statistics on 100000 UNIX shell one liners!
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.
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.