[R] porting an access database to sqlite

[Jeff Newmiller]

Counting rows is not something RODBC is supposed to do. That is a very basic SQL query that you can use RODBC to execute:

SELECT COUNT(*) FROM yourtablename

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

On November 25, 2014 9:41:38 AM PST, ravi <rv15i at yahoo.se> wrote:
>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.