3.3 Importing data from databases
If you have data in an Access database, you can read it in pretty easily using
the RODBC
package. To import one particular table from Access, you can use
library(RODBC) # activate package, case-sensitive
channel <- odbcConnectAccess('C:/Documents/Name_of_Access_Database') # change to your
mydata <- sqlQuery(channel, paste("select * from Name_of_table_in_database"))
For other databases, the connection can be made using the odbc
package. You can connnect
to a MySQL database, for example, using
library(odbc)
con <- dbConnect(odbc(),
Driver = "[your driver's name]",
Server = "[your server's path]",
Database = "[your database's name]",
UID = rstudioapi::askForPassword("Database user"),
PWD = rstudioapi::askForPassword("Database password"),
Port = 1433)
and you can load a table into R using
dat <- dbGetQuery(con, 'select * from <table name>')
You’ll notice that it is a bit more complicated to call data from databases, though once it’s set up, it works beautifully. For more details about this process for different databases, see RStudio’s tutorial.