CODEWITHSUNDEEP
rdplyrdata.tablesqldfrmysql
overflowStack345
overflowStack345

Reputation: 13

r - SQL on large datasets from several access databases

I'm working on a process improvement that will use SQL in r to work with large datasets. Currently the source data is stored in several different MS Access databases. My initial approach was to use RODBC to read all of the source data into r, and then use sqldf() to summarize the data as needed. I'm running out of RAM before I can even begin use sqldf() though.

Is there a more efficient way for me to complete this task using r? I've been looking for a way to run a SQL query that joins the separate databases before reading them into r, but so far I haven't found any packages that support this functionality.

Upvotes: 1

Views: 1004

Answers (2)

ASH
ASH

Reputation: 20302

The link you posted looks promising. If it doesn't yield the intended results, consider linking one Access DB to all the others. Links take almost no memory. Union the links and fetch the data from there.

# Load RODBC package
library(RODBC)

# Connect to Access db
channel <- odbcConnectAccess("C:/Documents/Name_Of_My_Access_Database")

# Get data
data <- sqlQuery(channel , paste ("select * from Name_of_table_in_my_database"))

These URLs may help as well.

https://www.r-bloggers.com/getting-access-data-into-r/

How to connect R with Access database in 64-bit Window?

Upvotes: 1

Jindra Lacko
Jindra Lacko

Reputation: 8719

Should your data be in a database dplyr (a part of the tidyverse) would be the tool you are looking for.

You can use it to connect to a local / remote database, push your joins / filters / whatever there and collect() the result as a data frame. You will find the process neatly summarized on http://db.rstudio.com/dplyr/

What I am not quite certain of - but it is not a R issue but rather an MS Access issue - is the means for accessing data across multiple MS Access databases.

You may need to write custom SQL code for that & pass it to one of the databases via DBI::dbGetQuery() and have MS Access handle the database link.

Upvotes: 1

Related Questions