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")


I've also tried using the SQL IMPORT command, which I couldn't get working
properly, even on a tiny two-field, five-row CSV file.

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


Any advice would be sincerely appreciated.

Thanks!

Anthony Damico
Kaiser Family Foundation

	[[alternative HTML version deleted]]

