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

[RINNER Heinrich]

Hello Marc,

thanks very much for your answer!
I had tried setting options("dec") before doing anything else, but that didn't solve the problem. Reading section "Internationalization Issues" didn't make me much wiser, so I'll search the archives of the e-mail list you mentioned, maybe I find something there.

Best wishes,
Heinrich.

> -----Ursprüngliche Nachricht-----
> Von: Marc Schwartz [mailto:marc_schwartz at me.com]
> Gesendet: Freitag, 22. Oktober 2010 14:53
> An: RINNER Heinrich
> Cc: r-help at stat.math.ethz.ch
> Betreff: Re: [R] RODBC: data base with decimal point ","
>
>
> 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;L
> C_MONETARY=German_Austria.1252;LC_NUMERIC=C;LC_TIME=German_Aus
> tria.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