Skip to content

ms access --> mysql --> R in Linux

7 messages · Anne York, Brian Ripley, Mateusz Loskot +2 more

Original
Anne York
# 
I am trying to use some ms access databases in R (version 
1.9.1 or 2.0 on a Debian system).  In searching the net for 
promising software to do this, I found mdbtools. Mdbtools 
claims the ability to convert schemas and tables in MS 
Access to MySQL and other databases.

http://mdbtools.sourceforge.net/

I'm wondering if anyone in the R community has tried using 
this software to use MS-Access databases in R with a Linux 
system. If so, Were you successful? What kind of problems 
did you encounter?

Thank-you for your attention.

Anne York
Brian Ripley
# 
mdbtools claims to have an ODBC driver, so no conversion should be 
necessary, just use RODBC.

Both last time I tried and just now I could not get it to compile. The
problems were in its Gtk front end, but that appears not to be optional.
On Mon, 1 Nov 2004, Anne York wrote:

            

      
      
      
    

        

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Mateusz Loskot
    

    

      
      #
    

  


  

      
01.11 2004 r., on 19:14 Anne York wrote:

            

      
      
      
    

        
I tried mdbtool on my linux box and I can say that
it supports MDB format quite well but I would
like to say that there is no _good_ software
which provides GUI for basic operations on MDB files
like table management, querying etc.

So, if you want to be sure that your data won't
be damaged I'd not recommend you to use MDB files
on Linux. Personally, I use Linux and I'm its funboy, so
don't call me Microsoft's man ;-) but that is a fact.

I have many databases in MDB files and I converted them
to sqlite or mysql databases on Linux to be able to work with
them on Linux. 
For sqlite (which I strongly recommend) there is no problem, because
you can use ODBC drivers for sqlite and export data
using MS Access. sqlite wors very well on both Linux & Windows,
so you don't have to do any conversion.

MDB to Mysql is a little more complex.
I did it that way
1. install ODBC drivers for mysql on Windows machine
2. configure ODBC (DSN) connection to mysql database on Linux
(I assume you have mysql on linux configured and you have ethernet
network connecting both machines).
3. open Microsoft Access, open ODBC conn to mysql in access,
4. import access databas to that connected remotely from linux (mysql)

Another way is to use freeware software called DBTools for Windows
(search for dbtools by google, use first link).
This software provides export/import functionality
between mysql/postgresql/sqlite/access.
Somtimes I use it and it works well.

I believe it will help you.

Greets

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Marc Schwartz
    

    

      
      #
    

  


  

      
On Mon, 2004-11-01 at 14:31, Mateusz Loskot wrote:

            

      
      
      
    

        
Just as an FYI, you might want to review what the OO.org folks are doing
by integrating the mdbtools functionality into OpenOffice:

http://dba.openoffice.org/drivers/mdb/index.html

A goal of the integration would be to provide a GUI-like interface to
Access tables, such as can be done under Windows using ODBC:

http://www.openoffice.org/FAQs/ms-access/ms-access.html

or what has been done with MySQL:

http://www.unixodbc.org/doc/OOoMySQL.pdf

The issue at this point (among many I suspect) is that the mdbtools
package is read-only to Access, though there are longer term plans to
enable write functionality. So for the time being at least, there is no
actual management ability to modify existing tables and queries.

Lastly, you can also get a feel for what the OO.org folks are doing with
their own internal applications in the area of DBMS:

http://dba.openoffice.org/miscellaneous/dba20.html

HTH,

Marc Schwartz

  
  


  


      

    

    

      
      

        


  

        

      David Whiting
    

    

      
      #
    

  


  

      
Anne York <york at zipcon.net> writes:

            

      
      
      
    

        
I have used it several times. The ODBC driver is not really (at all?)
working but the command line tools seem to work well enough.  I
modified mdb-export and created a script that worked like mysqldump
(to produce CREATE TABLE and INSERT statements) so that I was able to
get the tables into MySQL to use with R.  The latest version is quite
a bit more recent than the one I have been using and I believe it has
more features. 

Here is something that I have just tried and seems to work and avoids
the need to take your data into another database:

x <- read.table(pipe('mdb-export -d "\t" databasename.mdb tableName'), sep="\t", header=TRUE)

mdb-export exports the contents of a table from an Access database
(fields separated with commas by default). The -d option specifies the
delimiter (I prefer to use a tab).  This seems to work well on my
relatively small test database. I guess it would not take much work to
write a little set of functions to get the table names (using
mdb-tables) and do some other useful things. Not as good as having a
working ODBC driver, but quite nice all the same.

I'll have spend a little more time playing with this...

Dave

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      David Whiting
    

    

      
      #
    

  


  

      
Marc Schwartz <MSchwartz at medanalytics.com> writes:

[...]

            

      
      
      
    

        
I've just checked some recent mailing list posts and it seems that
write/update support for Jet3 (access 97) databases is underway. At
least parts of it are described as "primitive", but there is hope...

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      David Whiting
    

    

      
      #
    

  


  

      
David Whiting <david.whiting at ncl.ac.uk> writes:

            

      
      
      
    

        
Well, here's a simple start.  There's more checking that needs to be
done, but this makes the process a little more straight forward:


mdbTables <- function(dbname) {
  system(paste("mdb-tables -d '\t' -S", dbname), intern=TRUE)
}


mdbExport <- function(dbname,tableName) {
  tableName <- dQuote(tableName)
  read.table(pipe(paste("mdb-export -d '\t' ", dbname,  tableName)), sep="\t", header=TRUE)
}


With these functions you can now do:

mytabs <- mdbTables("myAccessDB.mdb")

to get the list of tables in the DB. The first 5 seem to be
information about the database and the tables seem to start from the
6th element of the vector.  If the table you want is the one named in
the 9th element of the vector:

mytab <- mdbTables("myAccessDB.mdb")[9]

and you want that table in R:

x <- mdbExport("myAccessDB.mdb", mytab)


At the moment you have to specify the extension (.mdb) otherwise
mdb-export cannot find the file.