On Tue, 24 Mar 2009 12:43:08 +0100, Mads Ruben Rennemo <madsru...@gmail.com> wrote:
MICROSOFT EXCEL. You edit my data. Did you consider that maybe, MAYBE, "8-16" wasn't a date and that you shouldn't convert it to August 16th?
It is worse even. If you have a value like 20090307, obviously an ISO date format, depending on the way it ends up in Excel (open, open by double-click, import, or read), it will be interpreted as number, date (probably wrong) or string. Also depends on the user locale. Very unpredictable. "8-16" in my perception is obviously data. Excel, please don't mess that up. "8/16" /might/ be a date, but then please *ask* me if it is to be converterted, but why on earth "2009-03-05" *WITH* quotes end up as May 03, 2009 - even in the Dutch locale - is way beyond my level of understanding.
Or perhaps, not edit the DATA so0 that I could choose to reformat the cell to "text" and get back to 8-16 instead of 42853? Where the HELL did 42853 come from anyways?
Also, when I save you as a CSV, I expect a line break inside a cell to be removed or converted. I don't expect to see two 8-column lines in my 16-column CSV file.
As others already said, M$'s interpretation of CSV is way besides the standard. I must admit the standard is sometimes a bit vague, but the few references that are available are pretty clear: --8<--- While no formal specification for CSV exists, RFC 4180 1) describes a common format and establishes "text/csv" as the MIME type registered with the IANA. Many informal documents exist that describe the CSV format. How To: The Comma Separated Value (CSV) File Format 2) provides an overview of the CSV format in the most widely used applications and explains how it can best be used and supported. 1) http://tools.ietf.org/html/rfc4180 2) http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm The basic rules are as follows: CSV is a delimited data format that has fields/columns separated by the comma character and records/rows separated by newlines. Fields that contain a special character (comma, newline, or double quote), must be enclosed in double quotes. However, if a line contains a single entry which is the empty string, it may be enclosed in double quotes. If a field's value contains a double quote character it is escaped by placing another double quote character next to it. The CSV file format does not require a specific character encoding, byte order, or line terminator format. · Each record is one line terminated by a line feed (ASCII/LF=0x0A) or a carriage return and line feed pair (ASCII/CRLF=0x0D 0x0A), however, line-breaks can be embedded. · Fields are separated by commas. · Allowable characters within a CSV field include 0x09 (tab) and the inclusive range of 0x20 (space) through 0x7E (tilde). In binary mode all characters are accepted, at least in quoted fields. · A field within CSV must be surrounded by double-quotes to contain a the separator character (comma). -->8--- Microsoft is a real loner there: --8<--- The import/export from Microsoft Excel is a risky task, according to the documentation in "Text::CSV::Separator". Microsoft uses the system's default list separator defined in the regional settings, which happens to be a semicolon for Dutch, German and Spanish (and probably some others as well). For the English locale, the default is a comma. In Windows however, the user is free to choose a predefined locale, and then change every individual setting in it, so checking the locale is no solution. -->8--- How hard would it have been for those fuckheads to just use a Comma, where the specs tell it to use a Comma and not use any character the current environment happens to have defined? -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00, 11.11, 11.23, and 11.31, SuSE 10.1, 10.3, and 11.0, AIX 5.2, and Cygwin. http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
