[R] Creating a data table (frame?) from a SQL Statement?

Jeff Newmiller jdnewmil at dcn.davis.ca.us
Tue Oct 24 18:40:45 CEST 2017


Your question desperately needs a reproducible example (a.k.a. "reprex"), because you have to be using contributed packages to do any of this. You also need to clarify whether you are intending to access data already in an external database, or are planning to load it using R and manipulate it with SQL.

I suggest that you install the "reprex" and "sqldf" packages and read their documentation and try out their examples. Then when you ask a question use reprex to be sure we see all the steps needed to get to where you are stuck. In particular we need the library calls that preceeded your sample code, but the reprex package will verify that you have it all there before you send the question. 

FWIW, note that the data function has a specific purpose of pulling data sets by name out of packages into your workspace, and giving it the output of randomly selected functions is not likely to work. Start reading help files soon... they may seem dense at first but the only way to get past that is to get started soon. Type

?data

at the console to start learning about that function.

Note that csv files are outside the world of standard SQL, so some of what you are doing may be very tightly linked with the particular SQL engine you are using.
-- 
Sent from my phone. Please excuse my brevity.

On October 24, 2017 7:01:36 AM PDT, Morkus via R-help <r-help at r-project.org> wrote:
>Hello,
>
>I'm new to R so this is probably a simple question for somebody.
>
>I have an RScript that reads a CSV on the disk using read.table(...).
>It then does a boxM test using that data.
>
>However, I'm now trying to load the same data via an SQL command, but I
>can't seem to get the data structure defined so R will like it -- using
>the included "iris" dataset.
>
>I've tried these ways of loading the SQL statement into a compatible R
>Structure:
>
>irisQuery <- data(dbGetQuery(conn, "select * from iris"))
>
>irisQuery <- data.frame(dbGetQuery(conn, "select * from iris"))
>
>irisQuery <- table(dbGetQuery(conn, "select * from iris"))
>
>.
>.
>.
>Followed by:
>
>boxM(irisQuery[,-5], irisQuery[,5])
>
>Nothing works work.
>
>For example, if i use ...
>
>irisQuery <- data.frame(dbGetQuery(conn, "select * from iris"))
>
>I get this error from R on the boxM test:  Error: is.numeric(x) ||
>is.logical(x) is not TRUE
>
>The SQL Statement is returning results, but their not CSV. Not sure if
>that matters.
>-------------------
>
>So, how do I read a SQL statement into an R structure like I read the
>CSV using "read.table" so the boxM test will work?
>
>Thanks very much in advance.
>
>- M
>
>Sent from [ProtonMail](https://protonmail.com), Swiss-based encrypted
>email.
>	[[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>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.



More information about the R-help mailing list