RODBC import to keep "NA"

Question

When I import country data using RODBC I often use ISO2 codes. The unfortunate part is Namibia with ISO2 "NA" always gets set to missing. There are various ways around this of course, but I wonder if anyone has found a way to just import correctly. I've tried

df <- sqlFetch(ch,"data_from_database", na.strings="")
df <- sqlFetch(ch,"data_from_database", as.is)

to no avail. Any tips?

Answer 1

Most of the database loaders eventually call read.table(). So as a first step, let's address that.

With a data file tom.csv as

country,value
GB,55
NA,77
CH,23 

I get

R> read.csv("/tmp/tom.csv")
  country value
1      GB    55
2    <NA>    77
3      CH    23
R> read.csv("/tmp/tom.csv", na.string="#")
  country value
1      GB    55
2      NA    77
3      CH    23
R> 

so simple setting na.string to a different value was sufficient.

Try that along with sqlQuery() which is more directly useful than sqlFetch().

Answer 2

HA! Well as.is=T doesn't work, na.string="" doesn't work, but with their powers combined...

> ch <- odbcConnectExcel("m.xls")
> s <- sqlFetch(ch, "m", as.is=T)
> close(ch)
> s 
  iso2 value
1   GB    87
2 <NA>   456
3   IN   423
> ch <- odbcConnectExcel("m.xls")
> r <- sqlFetch(ch, "m", na.string="")
> close(ch)
> r 
  iso2 value
1   GB    87
2 <NA>   456
3   IN   423
> ch <- odbcConnectExcel("m.xls")
> n <- sqlFetch(ch, "m", as.is=T, na.string="")
> close(ch)
> n 
  iso2 value
1   GB    87
2   NA   456
3   IN   423