[R] Excel

[J.delasHeras at ed.ac.uk]

Quoting J Dougherty <jwd at surewest.net>:

> On Monday 27 August 2007 22:21, David Scott wrote:
>> On Tue, 28 Aug 2007, Robert A LaBudde wrote:
>> > If you format the column as "Text", you won't have this problem. By
>> > leaving the cells as "General", you leave it up to Excel to guess at
>> > the correct interpretation.
>>
>> Not true actually. I had converted the column to Text because I saw the
>> interpretation as a date in the .xls file. I saved the .csv file *after*
>> the column had been converted to Text. Looking at the .csv file in a text
>> editor, the entry is correct.
>>
>> I have just rechecked this.
>>
>> On reopening the .csv using Excel, the entry AUG2699 had been interpreted
>> as a date, and was showing as Aug-99. Most bizarre is that the NHI value
>> of AUG1838 has *not* been interpreted as a date.
>>
> Actually, in Excel 2000, he's right.  What you have to is be sure of is that
> the "'" that denotes a text entry precedes EVERY entry that can be confused
> with a date.  Selecting the entire column and setting the format to "text"
> *before* data is entered does this. It will also create an  
> appropriate *.csv file.

Does this mean that Excel 2000 adds the apostrophe character? If so,  
that's not good. Yes, it can make teh right .CSV removing the  
apostrophe... but if you read the file again... you have teh issue  
coming back, because it does it automatically on opening the file,  
doesn't it?

> Excel is notable too because it will automatically convert "date-like"
> entries as you type.  In a column of IDs or similar critical data, that
> behaviour is really bad.  I have never tried the MS site, but I haven't been
> able to find any entry about how to turn that particular automatic behaviour
> off.

NOt sure in Excel 2000, but on whatever version it is you get in  
Office 2003, you can't. I pursued this and I eventually got the word  
from MS itself: you can't disable that "feature".

> PS, I quit using Excel for most important work after it returned a negative
> variance on some data I was collecting descriptive statistics on.

you can detect errors in your data... but how can you be sure that  
when you receive data processed by collaborators, for instance, there  
hasn't been a mistake?


-- 
Dr. Jose I. de las Heras                      Email: J.delasHeras at ed.ac.uk
The Wellcome Trust Centre for Cell Biology    Phone: +44 (0)131 6513374
Institute for Cell & Molecular Biology        Fax:   +44 (0)131 6507360
Swann Building, Mayfield Road
University of Edinburgh
Edinburgh EH9 3JR
UK

-- 
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.