RE: [office-comment] Implicit conversion and medical data

["Dennis E. Hamilton" <dennis.hamilton@acm.org>]

I know this is not to the point, and I don't think it impacts OpenFormula,
but it may assists the curious.

The way I have always ensured that Excel interprets an entry as text is to
pre-pend a single "'" to it.  The "'" will not be displayed and there is no
ambiguity.  I have done it that way for a very long time and continue to do
so simply because it works.  

Here's one as it appears in OOXML (via Excel 2003):

<c r="I18" s="137" t="s">
  <v>38</v> 
  </c>

And shared string 38 (0-origin, I think) is

<si>
  <t>2/2</t> 
  </si>

So the "'" does not appear in the document file but everything worked.  What
it accomplished here, though, was to avoid it producing "02-Feb" in the cell
for some reason.

I haven't tried that with an import, say in a CSV file. I haven't read the
rules for that in a long time. 

 - Dennis

Dennis E. Hamilton
------------------
NuovoDoc: Design for Document System Interoperability 
mailto:Dennis.Hamilton@acm.org | gsm:+1-206.779.9430 
http://NuovoDoc.com http://ODMA.info/dev/ http://nfoWorks.org 
 

-----Original Message-----
From: David A. Wheeler [mailto:dwheeler@dwheeler.com] 
Sent: Tuesday, June 03, 2008 15:07
To: patrick@durusau.net
Cc: office-comment@lists.oasis-open.org
Subject: Re: [office-comment] Implicit conversion and medical data

[ ... ]

You can see the basic issue here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214233

Here's the issue:
* When you type text into Excel, it tries to automatically figure out what
   kind of data it is (date, number, ordinary string, etc.). By itself,
that's fine;
   there are ways to override that when you type in data directly into
Excel, or
   when you manually invoke an "Import" of data (by identifying specific
columns
   as "text").  BUT....
* If a second program invokes Excel and gives it an "import" command,
   telling Excel to read in data from other data formats, it uses
   those same automatic conversions, and there's no easy way to disable it.
   Corrupting the data.

The only reasonable solution is to pre-process the data BEFORE it's loaded
into
Excel (e.g., by inserting a space before the data).

I don't see how OpenDocument's spec can control the functionality of an
app when reading in _NON_ OpenDocument files.  We could give a warning
about the issue somewhere, but it's really about the functionality of
loading in
NON-OpenDocument files.

--- David A. Wheeler

[ ... ]