R user-defined function to import ACCESS table via ODBC

Question

this might be something simple, but I couldn't figure out a viable solution.

My setup is:

 R version 3.3.1 (2016-06-21) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200) locale: [1] LC_COLLATE=Portuguese_Brazil.1252 LC_CTYPE=Portuguese_Brazil.1252 LC_MONETARY=Portuguese_Brazil.1252 LC_NUMERIC=C [5] LC_TIME=Portuguese_Brazil.1252 attached base packages: [1] stats graphics grDevices utils datasets methods base loaded via a namespace (and not attached): [1] tools_3.3.1 rpart_4.1-10

I'm building a function to import files from ACCESS DB via ODBC as folow:

importa.sql <- function(someFile) { library(RODBC) con <- odbcConnect("someTable") qry<-paste("(","SELECT * FROM ",someFile,")") someFile <- sqlQuery(con,qry,stringsAsFactors=FALSE) }

I've tested each line and the code is working as expected. The problem is: when I run the function, it seems to work perfectly, but there's no file imported!!!

Does anyone could help me?

djhurio · Accepted Answer · 2017-01-11 13:40:58Z

1

Your function return NULL because the last statement of the function ir assignment to the local object someFile. It would be good to close the connection. Try this function.

importa.sql <- function(someFile) { library(RODBC) con <- odbcConnect("someTable") qry <- paste("(", "SELECT * FROM ", someFile, ")") df <- sqlQuery(con, qry, stringsAsFactors = FALSE) close(con) return(df) }

answered Jan 11, 2017 at 13:40

djhurio

5,5364 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

blongworth Over a year ago

An explicit return is optional; you can call sqlQuery without assigning the output or just change the final line to df. Depending on who you ask, it's the preferred style for the final or non-error return of a function. Check out: adv-r.had.co.nz/Functions.html#return-values. Its a great guide to writing functions.

Marcelo Over a year ago

Thanks @djhurio for the answer. I've already tested it with {return}, but I didn't want it to show me the result on the console, just to generate the data.frame. I've found a solution which I'm just writting below.

Marcelo · Accepted Answer · 2017-01-11 15:33:20Z

In case of someone has the same doubt, the solution is really really simple.

Just call the function this way:

file <- importa.sql(someFile)

Collectives™ on Stack Overflow

R user-defined function to import ACCESS table via ODBC

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related