[R] Updating a worksheet in Excel file using RODBC

[Moshe Olshansky]

OK.

By the way,  I only thought that I could do what I wanted!
It worked once but then it failed.  When I was trying to update an existing sheet I got an error message saying that it existed and when I was trying to make a new sheet (something that worked once) I got a message saying that there was no such table!

-----Original Message-----
From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk]
Sent: Tuesday, 27 March 2007 4:44 PM
To: Moshe Olshansky
Cc: r-help at hypatia.math.ethz.ch
Subject: Re: Updating a worksheet in Excel file using RODBC


Yes, sqlDrop does not work correctly for Excel worksheet names (and there 
are other quirks).

As I said in another message, it is on my TODO list to make this work 
better, but in the absence of good documentation of what the Excel ODBC 
driver should do and several with known bugs it is largely a matter of 
trial-and-error.

On Tue, 27 Mar 2007, Moshe Olshansky wrote:

> Dear Prof. Ripley,
>
>> You seem not to have tried the simplest possible option.  The following
>> works for me (beware of wrapped lines from mailers)
>>
>> chan <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\\bdr\\hills.xls; ReadOnly=False")
>> sqlSave(chan, USArrests, "tests", fast=TRUE) # or FALSE
>
> You are right - I have not.
> It does not work exactly as it should have but this solves my problem.
> I created a very small Excel file odbc1.xls containing 3 sheets (test, Sheet2 and Sheet3).
> Below is a short R session:
>
>> chan <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\\EFGraphs\\odbc1.xls; ReadOnly=False")
> >x<- c(1:6)
> >x <- matrix(x,nrow=3,ncol=2)
> >x <- data.frame(x)
> x
>  X1 X2
> 1  1  4
> 2  2  5
> 3  3  6
>> sqlSave(chan, x, "test", fast=FALSE)
> Error in sqlSave(chan, x, "test", fast = FALSE) :
>        table 'test' already exists
>> sqlSave(chan, x, "tests", fast=FALSE)
>
> As you see I was unable to overwrite an existing sheet (an attempt to drop this table also fails), but I was able to add a new sheet to an existing Excel file (after this action the file contains 4 sheets - the 3 it contained and the last sheet named tests).
> This allows me to do what I wanted, i.e. manually create an Excel file with a small VBA macro, make many copies of this file (under appropriate names), write an appropriate data to each file and then the macro will work on the right data (different for each file).
>
> Thanky you!
>
> Moshe.
>
>
>
>
>
>
>
>
> Moshe Olshansky
>
> Chimaera Capital Limited
> Level 4 / 349 Collins Street
> Melbourne, Victoria 3000
> Phone: +613 8614 8400
> Fax: +613 8614 8410
> Email: molshansky at chimaeracapital.com
>
> Disclaimer: This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Chimaera Capital Limited. E-mail transmissions cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice.
>
>
>

-- 

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