Thanks yet another time, Gabor - I think I am slowly understanding - particularly I was confused by persistence of connections.
So starting with some parts of your example 12, ## gc() rm(list=ls()) unlink("mydb") sqldf("attach 'mydb' as new") irishead <- file("irishead.dat") iristail <- file("iristail.dat") If I just wanted to merge the two files within SQL and return some part of the result, I would do sqldf('select count(*) from (select * from irishead union select * from iristail)',dbname="mydb") and the tables exist in mydb only for the duration of the computation > sqldf("select * from sqlite_master",dbname="mydb")$name NULL (but why is the size of mydb > 0 afterwards, if it contains no tables...?) ...is the above the same as sqldf('select count(*) from (select * from irishead union select * from iristail)',dbname=tempfile()) except that I don't create 'mydb'? If I wanted to save the merged table (for use in a later session): sqldf('create table fulliris as select * from irishead union select * from iristail',dbname="mydb") > sqldf("select * from sqlite_master",dbname="mydb")$name [1] fulltable Levels: fulltable If I want copies of all three tables, sqldf(dbname="mydb") sqldf('create table fulltable as select * from irishead union select * from iristail') sqldf() > sqldf("select * from sqlite_master",dbname="mydb")$name [1] irishead iristail fulltable Levels: fulltable irishead iristail ? ...I'll try to go figure a few more things out in the in the meantime (like using sep="\t" ?) and using connections with sqldf(). But thanks for the help! Stephen ----- Original Message ---- From: Gabor Grothendieck <ggrothendi...@gmail.com> To: Stephen Tucker <brown_...@yahoo.com> Cc: R-help <r-h...@stat.math.ethz.ch> Sent: Friday, February 20, 2009 5:22:09 AM Subject: Re: [R] importing data to SQLite database with sqldf Have just added an example 12 on the home page: http://sqldf.googlecode.com that shows an example. Note use of notation main.mytable to refer to an existing table in the main database (as opposed to a data frame in R). On Thu, Feb 19, 2009 at 11:55 PM, Stephen Tucker <brown_...@yahoo.com> wrote: > Hi all, > > I am attempting to learn SQL through sqldf... > > One task I am particularly interested in is merging separate > (presumably large) files into a single table without loading these > files into R as an intermediate step (by loading them into SQLite and > merging them there). > > Taking a step back, I've considered these alternatives: > > 1) I know if I use straight SQLite commands I might use the 'IMPORT' > or 'INSERT INTO' command, which is not terribly flexible... (I can > read large files line-by-line in Python and use the 'INSERT INTO' > command, which is reasonably fast; I could do this in R as well but my > experience with R's input/output is that it's much slower...? and > sometimes setting up the table column definitions can be tedious if > there are many variables). > > 2) dbWriteTable() with append=TRUE is very convenient except that it > requires I load the data into R first... > > 3) sqldf's capability to put data directly into a database is > something I'd like to work out. > > So in this case I have a series of tab-delimited text file with the > first line being a header. > > For some reason I cannot seem to get it working. Combining examples 6 > and 9 from the Google Code page (and R-help archives), I tried > > source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R") > (do I need it for SQLite?) > ## > sqldf("attach 'mydb.db' as new") > f <- file("myexample.txt") > attr(f,"file.format") <- list(header=TRUE,sep="\t") > sqldf("create table myexample as select * from f", > stringsAsFactors=FALSE, > dbname="mydb.db") > ## or > f <- file(fi) > sqldf("create table myexample as select * from f", > stringsAsFactors=FALSE,file.format=list(header=TRUE,sep="\t"), > dbname="mydb.db") > ## > sqldf("select * from myexample",dbname="mydb.db") > gives me tables with 0 rows and 0 columns... > > So in any case I have a few questions: > > === 1 ==== > Would this be scalable to files with few GBs of data in them (I guess > I am uncertain of the underlying mechanism for transporting data from > the .txt file to the .db file... I see there is a call the > dbWriteTable() internally in sqldf but through the connection)? And > is there anything obviously doing wrong above? > > === 2 === > Since I cannot 'append' rows to existing tables in SQLite (or any SQL > database), I think what I would have to do is to load each of the > files into the database and then 'rbind' them into a new table using > 'union all'? The following code is what I have (the part where I read > in each file before dumping into the database file would hopefully be > replaced by the method above, if it can be made to work). > > ## (1) create a database file > sqldf("attach 'alltables.db' as new") > ## (2) convenience function > sql <- function(...) sqldf(...,dbname="alltables.db") > ## (3) load data as separate tables > for( fi in list.files(".","txt$") ) { > mydata <- read.delim(fi) > sql(sprintf("create table %s as select * from mydata",sub("\\.txt","",fi))) > } > rm(fi,mydata) > ## (4) merge tables > tablenames <- as.character(sql("select * from sqlite_master")$name) > sql(paste("create table myfulltable as", > paste(sprintf("select * from %s",tablenames), > collapse=" union all "))) > ## (5) delete separate tables since we have a merged one > for( nm in tablenames ) sql(sprintf("drop table %s",nm)) > > === 3 === > The following command > sqldf("attach 'mydb.db' as new") > DF <- read.delim(fi) > sqldf("create table myexample as select * from DF",dbname="mydb.db") > > will usually create a .db file twice the size of the .txt file (for > now I am playing with a files ~500KB so the .db files are around > ~1MB). When I create a .db file using SQLite's 'import' command, > RSQLite's dbWriteTable(), or inserting values from the same .txt file > from Python's SQLite interface, I get .db files that are approximately > the same size as the .txt file (~500KB). Is the larger file size for > sqldf's method expected? > > Many thanks in advance! > > Stephen Tucker > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.