Importing FIles with Extension .sqlite into R

Question

I have a SQLite database exported (as a sqlite format 3 file?) from Scraperwiki with the .sqlite file extension/file suffix.

How do I import it into R, presumably mapping the original database tables into separate data frames?

Answer 1

You could use the RSQLite package.

Some example code to store the whole data in data.frames:

library("RSQLite")  ## connect to db con <- dbConnect(drv=RSQLite::SQLite(), dbname="YOURSQLITEFILE")  ## list all tables tables <- dbListTables(con)  ## exclude sqlite_sequence (contains table information) tables <- tables[tables != "sqlite_sequence"]  lDataFrames <- vector("list", length=length(tables))  ## create a data.frame for each table for (i in seq(along=tables)) {   lDataFrames[[i]] <- dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables[[i]], "'", sep="")) } 

Answer 2

To anyone else that comes across this post, a nice way to do the loop from the top answer using the purr library is:

lDataFrames <- map(tables, ~{   dbGetQuery(conn=con, statement=paste("SELECT * FROM '", .x, "'", sep="")) }) 

Also means you don't have to do:

lDataFrames <- vector("list", length=length(tables))