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]] ______________________________________________ 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.
