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

[Frank E Harrell Jr]

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

-- 
Frank E Harrell Jr   Professor and Chair           School of Medicine
                      Department of Biostatistics   Vanderbilt University