On Mon, Nov 15, 2010 at 1:28 PM, Anthony Damico <ajdam...@gmail.com> wrote: > Hi Gabor, > > Thank you for your willingness to help me through this. The code you sent > works on my machine exactly the same way as it does on yours. > Unfortunately, when I run the same code on the 1.3GB file, it creates the > table structure but doesn't read in a single line [confirmed with > sqldf("select * from mytab",dbname="mydb")] Though I don't expect anyone to > download it, the file I'm using is ss09pusa.csv from > http://www2.census.gov/acs2009_1yr/pums/csv_pus.zip. I tested both sets of > code on my work desktop and personal laptop, so it's not machine-specific > (although it might be Windows- or 64 bit-specific). > > Do you have any other ideas as to how I might diagnose what's going on > here? Or, alternatively, is there some workaround that would get this giant > CSV into a database? If you think there's a reasonable way to use the > IMPORT command with RSQLite, that seems like it would import the fastest, > but I don't know that it's compatible with DBI on Windows. > > Thanks again! > Anthony > >
I tried it and the sqlite driver issued an error. Seems like a driver or sqlite issue. Here are some other approaches: 1. Create an sqlite sql script using this R code and then the shell command runs it using sqlite3 command line processor. fn <- "ss09pusa.csv" # read first line DF <- read.csv(fn, nrow = 1) # create statement s <- sprintf(" create table %s (%s); .separator , .import %s %s ", "mytab", toString(names(DF)), fn, "mytab") # output sql file fn.out <- paste(fn, "sql", sep = ".") cat(s, file = fn.out) # run import using generated sql file # Use sqlite3 command line processor. shell(paste("sqlite3 mydb <", fn.out)) 2. Try sqldf with the h2 database rather than sqlite. See H2 section on sqldf home page. library(sqldf) library(RH2) sqldf("create table mytab as select * from CSVREAD('ss09pusa.csv')", dbname = "mydb2") -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ______________________________________________ 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.