Hi Gabor,
Thank you very much for your guidance and help.
I could run the following code successfully on a 500 mb test data file. A
snapshot of the data file is attached herewith.
********code start***************
library(sqldf)
library(RSQLite)
iFile<-"Test100.txt"
con <- dbConnect(SQLite(),dbname = "myTest100")
dbWriteTable(con, "TestDB100", iFile, sep = "\t") #, eol = "\r\n")
nms <- names(dbGetQuery(con, "select * from TestDB100 limit 0"))
nRec<-fn$dbGetQuery(con, "select count(*)from TestDB100")
aL1<-1;
while (aL1<=nRec){
res1<-fn$dbGetQuery(con, "select * from (select * from TestDB100 limit
'$aL1',1)")
istn<-res1[1,1]
res1<-fn$dbGetQuery(con, "select * from TestDB100 where `nms[1]` = '$istn'")
icount<-dim(res1)[1]
oFile<-paste(istn,"_Test.txt",sep="")
write.table(res1, oFile, sep = "\t", quote = FALSE, col.names= FALSE,
row.names = FALSE)
aL1<-aL1+icount
}
dbDisconnect(con)
********code end***************
However, the actual data file that I want to handle is about *160 GB*. And
when I use the same above code on that file, it gives following error for
dbWriteTable(con, ...) statement
********error start**************
dbWriteTable(con, "TestDB", iFile, sep = "\t") #, eol = "\r\n")
Error in try({ : RS-DBI driver: (RS_sqlite_getline could not realloc)
[1] FALSE
********error end**************
I am not sure about the reason of this error. Is this due to the big file
size? I understood from sqldf webpage that SQLite can work for even a larger
file than this and is only restricted by the disc space and not RAM. I have
about 400GB free space on the PC I am using, with Windows 7 as the operating
system. I am assuming that the about dbWriteTable command is using the disc
memory only and is not the issue.
In fact this file has been created using MySQLdump and I do not have access
to the original MYSQL database file.
I want to know the following:
(1) Am I missing something in the above code that is preventing handling of
this big 160 GB file?
(2) Should this be handled outside of R, if R is becoming a limitation in
this? And if yes then what is a possible way forward?
Thank you again for your quick response and all the help.
HC
http://r.789695.n4.nabble.com/file/n4353362/Test100.txt Test100.txt
--
View this message in context:
http://r.789695.n4.nabble.com/sqldf-for-Very-Large-Tab-Delimited-Files-tp4350555p4353362.html
Sent from the R help mailing list archive at Nabble.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.
