Importing FIles with Extension .sqlite into R - and reading header names

Question

I have recently started using SQLite to store my raw biological data (I have several forms of raw data output and find it is a very useful way of querying and linking tables relating to the same trial). I would now like to pull out the raw data from across different files, combine it into a dataframe and then work on it (for example, I want to turn my dates into Julian dates and create new variables based on those I already have).

This link gave an excellent example of how to combine files that are in the same database:

Importing FIles with Extension .sqlite into R

and I managed to use this to create the dataframe I wanted. However (and here is where my lack of experience shows) the resulting dataframe didn't have recognisable headers:

> con <- dbConnect(drv, dbname="...PreliminaryFlightTrial.db")
> tables <- dbListTables(con)
> tables.excl <- tables[tables != "sqlite_sequence"]
> lDataFrames <- vector("list", length=length(tables.excl))
> for (i in seq(along=tables.excl)) {
+   lDataFrames[[i]] <- dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables.excl[[i]], "'", sep=""))
+ }
> names(lDataFrames)
NULL

I'd be really grateful if someone could point out my error. I did try adding header=T and the code ran but I still couldn't access the names:

for (i in seq(along=tables.excl)) { + DataFrames[[i]] <- dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables.excl[[i]], "'", sep="",header=T)) + } names(DataFrames) NULL

I did try googling but didn't get very far - maybe I am searching the wrong terms?

Many thanks

Answer 1

No need to read the whole table in R just to get the colnames you can use dbListFields instead

library(RSQLite)
drv <- dbDriver("SQLite")
con <- dbConnect(drv, dbname = ":memory:")
df1 <- data.frame(a = 1L, b = "hello")
df2 <- data.frame(c = 1:10, d = letters[1:10])
dbWriteTable(con, name = "df1", value = df1, row.names = FALSE)
dbWriteTable(con, name = "df2", value = df2, row.names = FALSE)

tables <- dbListTables(con)
lapply(tables, dbListFields, conn = con)
## [[1]]
## [1] "a" "b"

## [[2]]
## [1] "c" "d"