[R] RODBC, missing values, and Excel

[Sundar Dorai-Raj]

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"