[R] RODBC: data base with decimal point ","

[Marc Schwartz]

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