CODEWITHSUNDEEP
mysqlrms-access
Miguel
Miguel

Reputation: 3

Import data into R from access

I'm attempting to pull data into R from access which, I've successfully done.

Now what I'm looking to do is pull in all the needed tables with one line of code opposed to one at a time.

Example:

I have 5 tables: 3/15 Policy Details 6/15 Policy Details 9/15 Policy Details 12/15 Policy Details 3/16 Policy Details

As you can see, all the tables end with "Policy Details" but begin with a different date.

My orginal solution: 

library(RODBC)
db<-file.path("C:\\Path\\To\\Database.accdb")
db
channel<-odbcConnectAccess2007(db)

sqlTables(channel,tableType = "TABLE")$TABLE_NAME ##List all table names

Q1.15<-sqlFetch(channel,"3/15 Policy Details")
Q2.15<-sqlFetch(channel,"6/15 Policy Details")
close(channel)

I had to use sqlFetch for each quater. What I'm looking to do is bring in all the tables with one string of code oppossed to doing a seperate line of code for each quarter.

Upvotes: 0

Views: 143

Answers (1)

Parfait
Parfait

Reputation: 107587

Consider using grep() on returned list of table names. Then bind table fetches into a list with lapply() and then out to separate dataframe objects with list2env:

library(RODBC)

db <- file.path("C:\\Path\\To\\Database.accdb")

channel<-odbcConnectAccess2007(db)

accTables <- sqlTables(channel,tableType = "TABLE")$TABLE_NAME
accTables <- accTables[grep(".*Policy Details$", accTables)]

dfList <- lapply(accTables, function(t) sqlFetch(channel, t))

close(channel)

# NAME EACH DF ELEMENT THE SAME AS TABLE NAME
dfList <- setNames(dfList, accTables)

# OUTPUT EACH DF TO INDIVIDUAL OBJECT
list2env(dfList, envir=.GlobalEnv)

Upvotes: 2

Related Questions