[R] RODBC, missing values, and Excel

From: Sundar Dorai-Raj <sundar.dorai-raj_at_pdf.com>
Date: Thu 13 Jul 2006 - 08:28:49 EST


Hi, all,

I'm trying to use RODBC to read data from Excel. However, I'm having trouble converting missing values to NA and rather perplexed by the output. Below illustrates my problem:

## DATA - copy to Excel and save as "tmp.xls"
## tmp.xls!Sheet1

x
0.11
0.11
na
na
na
0.11

## tmp.xls!Sheet2

x
0.11
0.11
na
na
na
na
0.11

## R Code

read.xls <- function(file, sheet = "Sheet1", ...) {

   require(RODBC)
   channel <- odbcConnectExcel(file)
   sheet <- sprintf("select * from `%s$`", sheet)    x <- sqlQuery(channel, sheet, ...)
   odbcClose(channel)
   x
}

read.xls("./tmp.xls", "Sheet1", na.strings = "na")
## works as expected
# x
#1 0.11
#2 0.11
#3 NA
#4 NA
#5 NA
#6 0.11

read.xls("./tmp.xls", "Sheet2", na.strings = "na")
## Huh? What happened?
# x
#1 NA
#2 NA
#3 NA
#4 NA
#5 NA
#6 NA
#7 NA

 > sessionInfo()
Version 2.3.1 (2006-06-01)
i386-pc-mingw32

attached base packages:
[1] "methods" "stats" "graphics" "grDevices" "utils" "datasets" [7] "base"

other attached packages:

   RODBC
"1.1-7"



R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html Received on Thu Jul 13 08:37:15 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Thu 13 Jul 2006 - 12:13:56 EST.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.