R help - Oct 2010 - RODBC: data base with decimal point ","

Dear R-users,

I am working with R version 2.10.1 and package RODBC Version: 1.3-2 under
windows.

Say I have a table "testtable" (in an Access data base) with 3 columns
and 1 row that looks like this:
X               Y       Z
0012345 42      42,1
The columns are of these types: X - character, Y - Long Integer, Z - Decimal.

I use RODBC to get these data into R:
> library(RODBC)
> channel <- odbcConnectAccess("C:\\temp\\testdb.mdb")
> x <- sqlFetch(channel, "testtable", as.is = TRUE) # need as.is
so that X won't be converted to numeric
> summary(x)
      X                   Y           Z
 Length:1           Min.   :42   Length:1
 Class :character   1st Qu.:42   Class :character
 Mode  :character   Median :42   Mode  :character
                    Mean   :42
                    3rd Qu.:42
                    Max.   :42

So the problem seems to be the decimal point, as I am on an Austrian
locale:
> Sys.getlocale()
[1]
"LC_COLLATE=German_Austria.1252;LC_CTYPE=German_Austria.1252;LC_MONETARY=German_Austria.1252;LC_NUMERIC=C;LC_TIME=German_Austria.1252"

I tried
> options("dec")
$dec
decimal_point
          "."
> options(dec = ",")
, but that doesn't change anything.

How can I "tell" RODBC that the decimal point in my data base is
","?

Your advice would be appreciated;
kind regards
Heinrich.

On Oct 22, 2010, at 1:53 AM, RINNER Heinrich wrote:
> Dear R-users,
> 
> I am working with R version 2.10.1 and package RODBC Version: 1.3-2 under
windows.
> 
> Say I have a table "testtable" (in an Access data base) with 3
columns and 1 row that looks like this:
> X               Y       Z
> 0012345 42      42,1
> The columns are of these types: X - character, Y - Long Integer, Z -
Decimal.
> 
> I use RODBC to get these data into R:
>> library(RODBC)
>> channel <- odbcConnectAccess("C:\\temp\\testdb.mdb")
>> x <- sqlFetch(channel, "testtable", as.is = TRUE) # need
as.is so that X won't be converted to numeric
>> summary(x)
>      X                   Y           Z
> Length:1           Min.   :42   Length:1
> Class :character   1st Qu.:42   Class :character
> Mode  :character   Median :42   Mode  :character
>                    Mean   :42
>                    3rd Qu.:42
>                    Max.   :42
> 
> So the problem seems to be the decimal point, as I am on an Austrian
locale:
>> Sys.getlocale()
> [1]
"LC_COLLATE=German_Austria.1252;LC_CTYPE=German_Austria.1252;LC_MONETARY=German_Austria.1252;LC_NUMERIC=C;LC_TIME=German_Austria.1252"
> 
> I tried
>> options("dec")
> $dec
> decimal_point
>          "."
>> options(dec = ",")
> , but that doesn't change anything.
> 
> How can I "tell" RODBC that the decimal point in my data base is
","?
> 
> Your advice would be appreciated;
> kind regards
> Heinrich.

Heinrich,

Check Section 6 "Internationalization Issues" in:

  vignette("RODBC")

for guidance on dealing with decimal point character issues.

The potential issue with options("dec") is that it needs to be set
before RODBC is loaded. So if you changed if after using require(RODBC) or
library(RODBC), it will be ineffectual. There are also other system locale
related settings and the settings in the ODBC driver and perhaps Access itself,
referenced in the above.

Also, as an FYI, there is an e-mail list focused on R and database issues. More
info at:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

HTH,

Marc Schwartz