[R] importing excel-file

Alberto Monteiro albmont at centroin.com.br
Thu Apr 19 16:38:38 CEST 2007

Hans-Peter wrote:
>> This works in most cases.
>                 ~~~~~~~~~~~~
> Huu? (the package is supposed to work in *all* cases...!). Do you use
> the newest version (v1.3.1 or v1.3.2)? If there are any bugs/issues,
> please report them to me and they - most likely - will get fixed.
Here are the problems I noticed.

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

I have a spreadsheet where in the first line A1..ZZ1 there are the
names of some products. However, A1 is empty, but A8..A311 store dates.
The other fields, like B8..B311, store numbers.

So, if I do:

test1 <- read.xls(filename, sheet = "sheet name")

I will lose the first column: test1[1,1] will be B2. Also, the numbers
aren't recovered as numbers, but as strings, in _local_ format (which
the evil geniuses of M$ decided should be represented with commas)

(BTW: what are those "Levels" that appear when I type test[x,y]?)

If I do:

test2 <- read.xls(filename, sheet = "sheet name", colClasses = "double")

I will get the numbers correctly, but I will still lose the A8..A311

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. Probably they represent the number of days
since 1900-01-01, take a day or two, because Excel programmers
were stupid and didn't know that 1900 was not a leap year).

Finally, if I do:

test4 <- read.xls(filename, sheet = "sheet name", 
  colNames = FALSE, colClasses = "double")

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
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.

Alberto Monteiro

More information about the R-help mailing list