[R] importing numeric types via sqlQuery

[jim holtman]

Must be your datrabase interface:

> require(sqldf)
> # don't have MySql, but will use sqlite as example
> myData <- data.frame(cat = c('abc', 'def'), num=c(123.456, 7890.1234))
> myData
  cat      num
1 abc  123.456
2 def 7890.123
> sqldf('select cat, num from myData')  # now make sql request
  cat      num
1 abc  123.456
2 def 7890.123
>

# works fine here

On Mon, Oct 11, 2010 at 4:51 PM, E C <mmmraspberries at hotmail.com> wrote:
> Thanks for the quick reply! Hmm, I did not know about the options default.
> However, after I set options, it seems like it's still not displaying
> correctly. I've tried an even simpler example table with only 6 digits (much
> fewer than 20):
> category num\n
> abc 123.456\n
> def 456.789\n
> Then in R:
> options(digits = 20)
> data<-sqlQuery(channel, "select category, num from temp;")
> But "data" looks like this:
> category num\n
> abc 123\n
> def 456\n
> I suspect it's something with sqlQuery that chops off the digits and
> wondering if there's a way of turning it off. Thanks!
>
>
>> Date: Mon, 11 Oct 2010 16:28:25 -0400
>> Subject: Re: [R] importing numeric types via sqlQuery
>> From: jholtman at gmail.com
>> To: mmmraspberries at hotmail.com
>> CC: r-help at r-project.org
>>
>> I would assume that the digitis are not being chopped off. It is just
>> that R will typically print data to 7 significant digits:
>>
>> > x <- 54469517.307692307692
>> > x
>> [1] 54469517
>> > options(digits=20)
>> > x
>> [1] 54469517.3076923
>> >
>>
>> Your data it there and you can set 'options' to show it if you want
>> to. Also with floating point, you will only get about 15 digits of
>> accuracy (see FAQ 7.31).
>>
>>
>> On Mon, Oct 11, 2010 at 4:19 PM, E C <mmmraspberries at hotmail.com> wrote:
>> >
>> > Hi everyone,
>> > I am using the sqlQuery function (in RODBC library) to import data from
>> > a database into R. My table (called temp) in the database looks like this:
>> > category        numabc  54469517.307692307692def
>> >  36428860.230769230769
>> > I used the following R code to pull data into R:data <-sqlQuery(channel,
>> > "select category, num from temp;")
>> > However, the result is that "num" gets all its decimal places chopped
>> > off, so "data" looks like this instead in R:category      numabc
>> >  54469517def     36428860
>> >
>> > I've tried various alternative approaches, but none have fixed the
>> > problem. When I cast the variable to a numeric type like this (data
>> > <-sqlQuery(channel, "select category, num::numeric from temp;"), it still
>> > gave me the same result. Casting to a real type like this (data
>> > <-sqlQuery(channel, "select category, num::real from temp;") resulted in
>> > scientific notation that also rounded the numbers.
>> > Any suggestions? Much appreciated!
>> >        [[alternative HTML version deleted]]
>> >
>> > ______________________________________________
>> > R-help at r-project.org mailing list
>> > https://stat.ethz.ch/mailman/listinfo/r-help
>> > PLEASE do read the posting guide
>> > http://www.R-project.org/posting-guide.html
>> > and provide commented, minimal, self-contained, reproducible code.
>> >
>>
>>
>>
>> --
>> Jim Holtman
>> Cincinnati, OH
>> +1 513 646 9390
>>
>> What is the problem that you are trying to solve?
>



-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?