[R] porting an access database to sqlite

[ravi]

Hi,Thanks for the replies. My problem is that the tables (at least, a few of them) are too large. I cannot directly export the tables to get text files. I tried the following :
c1<-sqlQuery(ch,"SELECT COUNT(*) FROM table1")
I got the following error (changed the text to alter table names) :[1] "AB000 -2001 [Microsoft][ODBC MicrosoftAccess Driver] ODBC--call failed."                [2] "[RODBC] ERROR: Could not SQLExecDirect'SELECT COUNT(*) FROM Table1'"

But the following works (with about 5 minutes for each step) :con<-odbcConnect("TestDB")
d1<-sqlFetch(ch,"table1",max=1e6,as.is=TRUE)
d2<-sqlFetchMore(ch,max=1e6,as.is=TRUE)

I could successively retrieve 6 million rows (with 52 columns). After that (with the next sqlFetchMore command), I got the following error : 
Error : cannot allocate vector of size 7.6 Mb
When this happened, I had saved all the data frames obtained with the saveRDS (found this to be the easiest method of limiting the size of the file on disk)  command and then deleted the data frames from memory. So, I do not know why the last error turned up.
Though I am not an expert in databases, I am quite sure that the database has not been put together in a sensible way. I am now trying to work with my amateurish methods and trying to see what I can. 

I would like to know if there is any sql query by which I can continue in retrieving data from 6e6 row onwards. That is, instead of starting from row 1, can I fetch rows from row number, 6e6+1?How do I know that I am near the tail end? There seems to be no simple method for finding the size of the table.
Thanks,Ravi
>>> ravi <rv15i at yahoo.se> 11/25/14 12:42 PM >>>


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 at 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.


  Confidentiality Statement:   This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.  

  
	[[alternative HTML version deleted]]