Search code examples
rdatabasereadr

Can you parse a RODBC query through readr?

I am trying to improve my analytics script for a database and want to use a direct output from the query in my Access DB instead of outputting to a linked excel file where I refreshed it and then saved to a csv file. I can get the table into R, but I would like it parsed through readr. Is there a way to do this?

I have tried outputting to a csv straight away then using read_csv to reimport but it pulled errors on the parsing.

dbdata <- sqlQuery(db , "SELECT * 
                              FROM qRoutput", stringsAsFactors = FALSE)

Is how I currently import my query.

I would just like to parse that query output through the readr parsing function - everything being taken as characters, doubles and the class coming back as "[1] "spec_tbl_df" "tbl_df" "tbl" "data.frame"

Solution

  • This should already give the output as data.frame. May be you could try df = sqlGetResults(db) after your sqlQuery.

    Alternatively you can go for dbplyr approach. Below is an example. This approach is valid for RODBC connections too. Intro to dbplyr

    library(RSQLite)

# Create a db
db = dbConnect(SQLite(), "./Data/TestDB.db")

# write data to the db
dbWriteTable(db, "testtable", mtcars)

# Check whether the table is present
dbListTables(db)

# use the dplyr syntax to query
tbl(db, "testtable") %>% select(mpg, cyl)