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.