Hi Gabor,

Thank you for the prompt reply.  I definitely looked over all of the
examples on the code.google.com sqldf page before sending, which is why I
wrote the code

read.csv.sql("ss09pusa.csv" , sql="create table ss09pusa as select * from
file" , dbname="sqlite")

directly pulled from their code -- read.csv.sql("~/tmp.csv", sql = "create
table mytab as select * from file",dbname = "mydb")

..but I don't understand why this helps me around the memory problem, since
I think it still all gets read into memory.  Is there a way to do this line
by line?

I would prefer to use SQLite than sqldf, but I could not get the IMPORT
command (or .IMPORT) functioning at all.  I tried these with both dbGetQuery
and dbSendQuery.

library(RSQLite)
setwd("R:\\American Community Survey\\Data\\2009")
out_db <- dbConnect(SQLite(), dbname="sqlite.db")
dbGetQuery(out_db , "create table test (hello integer, world text)")
dbGetQuery(out_db , "mode csv")
dbGetQuery(out_db , "import test.csv test")

When I hit the mode and import commands, it gives me an error that makes me
think it's handling these files in a completely different way.

> dbGetQuery(out_db , "mode csv")
Error in sqliteExecStatement(con, statement, bind.data) :
  RS-DBI driver: (error in statement: near "mode": syntax error)


I suppose I could just run sqlite3 commands from the system() function, but
I was hoping there might be a way to accomplish this task entirely within R?



Thanks again!



On Mon, Nov 15, 2010 at 10:41 AM, Gabor Grothendieck <
ggrothendieck@gmail.com> wrote:

> On Mon, Nov 15, 2010 at 10:07 AM, Anthony Damico <ajdamico@gmail.com>
> wrote:
> > Hi, I'm working in R 2.11.1 x64 on Windows x86_64-pc-mingw32.  I'm trying
> to
> > insert a very large CSV file into a SQLite database.  I'm pretty new to
> > working with databases in R, so I apologize if I'm overlooking something
> > obvious here.
> >
> > I'm trying to work with the American Community Survey data, which is two
> > 1.3GB csv files.  I have enough RAM to read one of them into memory, but
> not
> > both at the same time.  So, in order to analyze them, I'm trying to get
> them
> > into a SQLite database so I can use the R survey package's
> database-backed
> > survey objects capabilities (
> > http://faculty.washington.edu/tlumley/survey/svy-dbi.html).
> >
> > I need to combine both of these CSV files into one table (within a
> > database), so I think that I'd need a SQL manipulation technique that
> reads
> > everything line by line, instead of pulling it all into memory.
> >
> > I've tried using read.csv.sql, but it finishes without an error and then
> > only shows me the table structure when I run the final select statement.
> > When I run these exact same commands on a smaller CSV file, they work
> fine.
> > I imagine this is not working because the csv is so large, but I'm not
> sure
> > how to confirm that or what to change if it is.  I do want to get all
> > columns from the CSV into the data table, so I don't want to filter
> > anything.
> >
> > library(sqldf)
> > setwd("R:\\American Community Survey\\Data\\2009")
> > sqldf("attach 'sqlite' as new")
> > read.csv.sql("ss09pusa.csv" , sql="create table ss09pusa as select * from
> > file" , dbname="sqlite")
> > sqldf("select * from ss09pusa limit 3",dbname="sqlite")
> >
>
> What the above code does, which is unlikely to be what you intended,
> is to create an sqlite database called 'sqlite' and then read in the
> indicated file into sqlite, read it in into R from sqlite (clearly
> this step will fail if the data is too big for R but it its not then
> you are ok) and then delete the table from the database so your sqldf
> statement should give an error since there is no such table or else if
> you have a data frame in your R workspace called ss09pusa the sqldf
> statement will load that into a database table and the retrieve its
> first three rows and then delete the table.
>
> This sort of task is probably more suitable for RSQLite than sqldf
> but if you wish to do it with sqldf you need to follow example 9 or
> example 10 on the sqldf home page:
>
> In example 9,
>
> http://code.google.com/p/sqldf/#Example_9.__Working_with_Databases
>
> its very important to note that sqldf automatically deletes any table
> that it created after the sqldf or read.csv.sql statement is done so
> to not have the table dropped is to make sure you issue an sql
> statement that creates the table, "create table mytab as select ..."
> rather than sqldf.
>
> In example 10,
>
> http://code.google.com/p/sqldf/#Example_10._Persistent_Connections
>
> persistent connections are illustrated which represents an alternate
> way to do this in sqldf.
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
>

	[[alternative HTML version deleted]]

