[R] importing excel-file

[Hans-Peter]

Hi Alberto,


> I can't call read.xls while Excel is opening the xls file.
> R crashes and must be aborted.

Could not reproduce here (Win2000, Excel 2003, R 2.4.1 and
2.5.0.alpha). Who knows... but I cannot think of anything that could
cause such a problem: xlsReadWrite works on the plain file (Excel is
not even needed), doesn't lock the file and, if there were problems,
handles eventual exceptions before leaving the DLL. If you have a
reproducible test case I would be very interested in it.

[Unrelated with this, I found that read.xls doesn't work any longer
with R versions <= 2.3.1. Reason: I used 'default.stringsAsFactors()'
in the stringsAsFactor argument.  => DESCRIPTION-Depends field updated
in v1.3.3].


> I have a spreadsheet where in the first line A1..ZZ1 there are the
> names of some products. However, A1 is empty,
                                   ~~~~~~~~~~~~
This is the problem. With rowNames=NA (the default) the first Excel
column will be *considered* (under certain conditions) to contain the
*rownames*. The first element beeing empty is such a condition. It's
in the help, but not 100 % clear and some conditions have been
missing. [=> help text updated in v1.3.3]

'rowNames = FALSE' prevents the auto-row-consideration.


> I will lose the first column: test1[1,1] will be B2.

The first column has been taken for the rownames.


> If I do:
> test3 <- read.xls(filename, sheet = "sheet name", colNames = FALSE)
> I will get A8..A311 (but these are not numbers, and I can't convert them to the dates.

If you have a title row in Excel, you should skip this row (i.e. in
your case use 'from=2') because otherwise the columns will be
recognized as character columns which is not what you want.


> I get A8..A311 as something remotely similar to dates (I can even
> display them as dates:
> as.POSIXlt("1899-12-30", "GMT") + test4[8:17, 1] * 86400

If you set dateTimeAs="isodatetime" or use the colClasses entries
"isodate", "isotime" or "isodatetime" you can get them directly as
character date strings.

Another option is to retrieve the numbers and use the new conversion
functions dateTimeToStr or dateTimeToIsoStr.


> will return a vector of dates!), but then I will lose the meaning
> of the columns, because test4[,1] is no longer the list of the
> product names.
> So it seems that none of the four possibilities is entirely satisfactory.

Well, supreme satisfaction can definitely only be gained with the pro
version...

...but the following should make you happy too:

test9 <- read.xls( filename, sheet = "sheet name", rowNames = FALSE,
dateTimeAs = "isodatetime" )

;-)


-- 
Regards,
Hans-Peter