sqliter

How to import from SQLite database?

I have an SQLite database file exported from Scraperwiki with .sqlite file extension. How do I import it into R, presumably mapping the original database tables into separate data frames?

Solution

  • 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=""))
}

dbDisconnect(con)