[R] Import from excel 2007

[Prof Brian Ripley]

Now my Windows machine is back online again, I have been able to do some 
work on this.

You can download the latest ODBC drivers from download.microsoft.com 
(select Office as the product, search for ODBC and you will get to 
AccessDatabaseEngine.exe).  You don't need any version of Office installed 
to make use of this.

RODBC_1.2-2, which will be available shortly, has convenience wrappers
odbcConnectExcel2007 and odbcConnectAccess2007 to make use of those 
drivers.  However, if you use this sort of thing at all frequently I 
suggests setting up a UserDSN with the paths and file types you use, and 
using odbcConnect on that.

On Wed, 17 Oct 2007, Marc Schwartz wrote:

> On Wed, 2007-10-17 at 20:53 +0200, kees wrote:
>> For me, it works. That is to say, not the simple way, which gave the error:
>> odbcConnectExcel("C:\\Users\\Kees\\Desktop\\Map1.xlsx")
>>
>> But it does when you ask the correct driver
>> odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
>> *.xlsb);DBQ=C:\\Users\\Kees\\Desktop\\Map1.xlsx; ReadOnly=False")
>>
>> It works correctly.
>>
>> Cheers,
>> Kees
>
> <snip>
>
> Thanks Kees.
>
> If I am reading the RODBC source correctly, in file win.R, there is the
> following:
>
> if(.Platform$OS.type == "windows") {
>    ## originally based on suggestions from xiao.gang.fan1 at libertysurf.fr
>    odbcConnectExcel <- function(xls.file, readOnly = TRUE, ...)
>    {
>        full.path <- function(filename) {
>            fn <- chartr("\\", "/", filename)
>            is.abs <- length(grep("^[A-Za-z]:|/", fn)) > 0
>            chartr("/", "\\",
>                   if(!is.abs) file.path(getwd(), filename)
>                   else filename)
>        }
>        con <- if(missing(xls.file))
>            "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="
>        else {
>            fp <- full.path(xls.file)
>            paste("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=",
>                  fp, ";DefaultDir=", dirname(fp), ";", sep = "")
>        }
> 	if(!readOnly) con = paste(con, "ReadOnly=False", sep=";")
>        odbcDriverConnect(con, tabQuote=c("[", "]"), ...)
>    }
>
> ....
>
>
>
> It would appear that the defacto name for the ODBC driver has changed in
> 2007 from prior versions. Thus, it would seem that some additional
> checking is needed here for compatibility with Office 2007 when using
> this function.
>
> Not running on Windows, I would defer to Prof. Ripley as to his
> preferred approach here.
>
> I would guess that the same would be the case for odbcConnectAccess().
>
> In the other case, using odbcDriverConnect(), you are explicitly naming
> the driver, so it works.
>
> So for the time being, using odbcDriverConnect() would be the approach
> to use.
>
> HTH,
>
> Marc
>
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595