Hi,All my data is presently locked in a Microsoft access database. This has
huge data in a number of large tables. Using RODBC and connecting to it takes
too long a time, sometimes making the system to hang up.
To make things more manageable, I have tried to transfer the data to manageable
.RData or .csv files. But I am not able to do this with some of the larger
files. I am currently stuck in the one of the preliminary steps. I am not able
to find the number of rows in a table. If I know this, I can transfer chunks of
the tables to a sqlite database.
I am able to connect to connect to the access data base with :
library(RODBC)
con<-odbcConnect("TestDB")d1<-sqlFetch(ch,"table1",max=1e5,as.is=TRUE)d2<-sqlFetchMore(ch,max=1e5,as.is=TRUE)
d3<-rbind(d1,d2)I wanted to develop this into a loop to get a concatenated data
frame, which I wanted to save either as a binary file, or transfer it to a
sqlite data base. I would like to have some help on the simplest route to
follow. But first, I will proceed in describing my immediate problem. In some
of the tables, I find that the sqlFetchMore returns a value of -1L, meaning
that the end has been reached. In RODBC, I find no command for getting the row
count in a table. I have found this in DBI. But I have not been able to figure
out how I should specify the connection to an acccess database (con in the
following).
while(!dbHasCompleted(con)){ print(dbGetRowCount(con))}
I would appreciate help on the following points :1. How can I get the row count
(and size) of a table in an access data base? With RODBC, DBI or any other
way.2. I have found that saving the tables as .RData files reduces the file
size and and reduces the reading time. Is there some way of appending to an
already saved data frame with this method?3. I have come across alternative
ways of saving data - using writeBin and packages like saves, rhdf5 etc. Would
they be useful alternatives?
4. Is there an advantage in combining binary files and databases like sqlite?
Or, are files already saved in a binary format in databases like sqlite?5. What
is the simplest method of porting from the access to the sqlite database? With
RSQlite and RODBC, can I have connections to the access and sqlite databases
open at the same time? Or, should I close one and then open the other? It would
help if I can get a detailed bit if code for doing this in a simple way.
I would appreciate all help that I can get.Thanks,Ravi
[[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.
