Convert ODBC to dataframe for using R packages


I am trying to find a way to use R packages like dplyr, summarytools in R, but my data is the ODBC format. I am trying to convert the ODBC into dataframes, however facing some issues.

x <-
Error in
Cannot coerce class 'structure ("OdbCResult", package = "odbc")' to a data.frame


Welcome to the RStudio community!

It is not clear from your post what the exact issues is, but you can start by reading these tips on using ODBC driver within R

If still stuck, please create a reprex. A reprex consists of the minimal code and data needed to recreate the issue/question you're having. You can find instructions how to build and share one here:

Good luck,

Hi, the reason is that the res variable contains not the results of the query, but a SQL statement. In order to pull down the actual data from the database, you would have to use the collect() function, that will convert the data transmitted by the DB to R into a tibble. So, you can try: res %>% collect(). Please keep in mind that that operation will bring back everything, so if the result of your query contains 1,000's of rows, that is what you will get. If you have exploring at this time, you can consider using res %>% head(100) %>% collect(), that will bring back first 100 results.

Thank you edgararuiz,

I understand that res is nothing but obtained from an SQL output from an ODBC database as follows:
res <- dbSendQuery(con, "select * from X.Y.Z")

Is there a way to obtain a data frame from the odbc database X.Y.Z directly ?

Thank you for your suggestion to use res %>% head(100) %>% collect(), however it is giving me the following broad error:
"Error in x[seq_len(n)] : object of type 'S4' is not subsettable"

Thank you and any help is appreciated.

There are 2 ways of reading from ODBC with that package:

  switchdbGetQuery = TRUE
  switchdbGetQuery = FALSE
  if (switchdbGetQuery) {
    DataRead = dbGetQuery(dbcon,QueryString)
  } else {
    QueryRS = dbSendQuery(dbcon,QueryString)
    DataRead = dbFetch(QueryRS)

+1 to @mikecrobp 's response, for that you would use dbGetQuery() . In dbplyr, you would use tbl(), as in tbl(con, "xyz"), and that would become your pointer to that table that then you can use to pass more dplyr commands: tbl(con, "xyz") %>% group_by(field1) %>% summarise(x = sum(field2))

Thank you edgararuiz,

The dbGetQuery() function worked for me. Appreciate your help with this

Thank you mikecropbp,

The dbGetQuery() function worked for me. Appreciate your help with this

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.