[R] Import from excel 2007

[Marc Schwartz]

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