[R] reading in mdb and outputting to sql (GIS application)

Frank E Harrell Jr f.harrell at vanderbilt.edu
Sat Aug 15 16:09:28 CEST 2009


stephen sefick wrote:
> tables=TRUE does return a list of tables with no error.  The problem
> is that I can't get into the database because I am on a macintosh.  I
> would send you the database, but it is for a military base and I don't
> believe that I am allowed.  I'll see if I can't figure it out.  Thanks
> for all of the help.
> 
> Stephen Sefick

Then you'll need to get mdb-tools to work outside of R on your Mac 
before proceeding.  Don't send any real database; only send a fake test 
database that fails.

Frank

> 
> On Sat, Aug 15, 2009 at 7:44 AM, Frank E Harrell
> Jr<f.harrell at vanderbilt.edu> wrote:
>> stephen sefick wrote:
>>>> library(Hmisc)
>>>> # Read all tables in the Microsoft Access database blabla.mdb
>>>> #   perhaps better change to Locale in which the .mdb file was
>>>> #   generated to avoid that accented table column names are
>>>> #   getting lost.file first
>>>> Sys.setenv(PATH=paste(Sys.getenv('PATH'),'/sw/bin',sep=':'))
>>>> d <- mdb.get('/Users/sefick/Desktop/FtBragg_GeoDataBase_AuburnUNV.mdb')
>>> This is what I get when I try the above code
>>>
>>> Error in type.convert(data[[i]], as.is = as.is[i], dec = dec,
>>> na.strings = character(0L)) :
>>>  invalid multibyte string at '<ff><ff><fa><ff>'
>> If you can e-mail me a relatively small mdb file that fails for you I'll
>> debug.  Also try specifying tables=TRUE to mdb.get to see if it can return
>> the list of tables in the database without an error.
>>
>> Frank
>>
>>> On Fri, Aug 14, 2009 at 12:27 PM, stephen sefick<ssefick at gmail.com> wrote:
>>>> thank you all- I will report back when I have tried on my computer at
>>>> home.
>>>>
>>>> On Fri, Aug 14, 2009 at 12:24 PM, Phil Spector<spector at stat.berkeley.edu>
>>>> wrote:
>>>>> Stephen -
>>>>>  You can modify your path on the fly with Sys.setenv:
>>>>>
>>>>> Sys.setenv(PATH=paste(Sys.getenv('PATH'),'/sw/bin',sep=':'))
>>>>>
>>>>> should make executables installed in /sw/bin available to R.
>>>>>
>>>>>                                       - Phil Spector
>>>>>                                        Statistical Computing Facility
>>>>>                                        Department of Statistics
>>>>>                                        UC Berkeley
>>>>>                                        spector at stat.berkeley.edu
>>>>>
>>>>>
>>>>> On Fri, 14 Aug 2009, stephen sefick wrote:
>>>>>
>>>>>> Frank,
>>>>>> I have tried to use this package, but because I am using fink to
>>>>>> install packages the mdbtools binary they are not accessible by the
>>>>>> function from within R.  I checked this by just trying to call it with
>>>>>> the system function and R returned /bin/sh not found.  I can access
>>>>>> the binary from the terminal, so the /sw/bin directory where mdbtools
>>>>>> is located is in my PATH- at least at a terminal.
>>>>>> thanks for the help,
>>>>>>
>>>>>> Stephen Sefick
>>>>>>
>>>>>> On Fri, Aug 14, 2009 at 11:27 AM, Frank E Harrell
>>>>>> Jr<f.harrell at vanderbilt.edu> wrote:
>>>>>>> The R Hmisc package interfaces these tools - see its mdb.get function.
>>>>>>>
>>>>>>> Frank
>>>>>>>
>>>>>>>
>>>>>>> Marc Schwartz wrote:
>>>>>>>> On Aug 14, 2009, at 10:29 AM, stephen sefick wrote:
>>>>>>>>
>>>>>>>>> I have a geodatabase and would like to import it into GRASS, but it
>>>>>>>>> is
>>>>>>>>> in .mbf.  I use mac and linux, and don't have access to access on
>>>>>>>>> the
>>>>>>>>> machines that I normally use.  I do have access to access at school,
>>>>>>>>> but I would like to find a way around this if possible.  Any
>>>>>>>>> thoughts,
>>>>>>>>> comments, or suggestions would be welcome.
>>>>>>>>> thanks,
>>>>>>>> I think that on Linux and OSX, from an open source perspective, the
>>>>>>>> only
>>>>>>>> option is to use MDB Tools, though I have seen mixed reports of
>>>>>>>> success,
>>>>>>>> including some threads in the R-Help archives. It also appears that
>>>>>>>> there
>>>>>>>> has been no development on the package for about 5 years, as the
>>>>>>>> current
>>>>>>>> version (0.6pre1) dates from 2004. That may suggest limited, if any,
>>>>>>>> compatibility with recent versions of Access file formats.
>>>>>>>>
>>>>>>>> You can download the source tarball here:
>>>>>>>>
>>>>>>>>  http://sourceforge.net/projects/mdbtools/
>>>>>>>>
>>>>>>>> For OSX, it is available via MacPorts:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>  http://trac.macports.org/browser/trunk/dports/databases/mdbtools/Portfile
>>>>>>>>
>>>>>>>> and for major Linux distributions, there are typically pre-compiled
>>>>>>>> binaries available via the standard repos and installation tools (eg.
>>>>>>>> yum
>>>>>>>> and apt-get).
>>>>>>>>
>>>>>>>> There was also some work by the OO.org folks a while back to embed
>>>>>>>> MDB
>>>>>>>> Tools in OpenOffice, but I am not aware of the current state of the
>>>>>>>> project
>>>>>>>> or if it is even active any longer.
>>>>>>>>
>>>>>>>> There is a commercial option from Actual Technologies:
>>>>>>>>
>>>>>>>>  http://www.actualtechnologies.com/product_access.php
>>>>>>>>
>>>>>>>> which appears to provide an ODBC driver for Access on OSX. The page
>>>>>>>> focuses on the use of either Excel or FileMaker Pro with the driver.
>>>>>>>> However, you may be able to get it to work with RODBC.
>>>>>>>>
>>>>>>>> I use Actual's ODBC driver for Oracle on OSX via RODBC, since Oracle
>>>>>>>> has
>>>>>>>> not seen fit to provide a free one as they do for Linux and have been
>>>>>>>> very
>>>>>>>> pleased. It was easy to install and configure, so take that for what
>>>>>>>> it's
>>>>>>>> worth.
>>>>>>>>
>>>>>>>> HTH,
>>>>>>>>
>>>>>>>> Marc Schwartz
>>>>>>>>
>>>>>>>> ______________________________________________
>>>>>>>> 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.
>>>>>>>>
>>>>>>> --
>>>>>>> Frank E Harrell Jr   Professor and Chair           School of Medicine
>>>>>>>                    Department of Biostatistics   Vanderbilt University
>>>>>>>
>>>>>>
>>>>>> --
>>>>>> Stephen Sefick
>>>>>>
>>>>>> Let's not spend our time and resources thinking about things that are
>>>>>> so little or so large that all they really do for us is puff us up and
>>>>>> make us feel like gods.  We are mammals, and have not exhausted the
>>>>>> annoying little problems of being mammals.
>>>>>>
>>>>>>                                                               -K.
>>>>>> Mullis
>>>>>>
>>>>>> ______________________________________________
>>>>>> 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.
>>>>
>>>> --




More information about the R-help mailing list