R help - Mar 2007 - RODBC Excel sqlQuery insert into

I have searched the archives for using insert into to update spreadsheets 
using RODBC and have come up short. So, first off, is it possible?

I have put together a dummy xls table (c:\foo.xls)for exploring 
possibilities of RODBC.  Ultimately, I am interested in replacing much of 
our previous use of vba macros with R ( I'd prefer elimination, but will 
take what I can get ).  In order to achieve this, I will still have a need 
to update spreadsheets directly through R scripts. 

Simple queries seem to work fantastic!  But, I am missing something when 
it comes to writing.

sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL ) 
VALUES( 5,'robin',5678 ) " )
[1] "[RODBC] ERROR: Could not SQLExecDirect"      "S1000 -3035 
[Microsoft][ODBC Excel Driver] Operation must use an updateable query."


myChan = odbcConnectExcel("c:\\foo.xls")
myChan
RODB Connection 13
Details:
  case=nochange
  DBQ=c:\foo.xls
  DefaultDir=c:\
  Driver={Microsoft Excel Driver (*.xls)}
  DriverId=790
  MaxBufferSize=2048
  PageTimeout=5

sqlTables(myChan)
  TABLE_CAT TABLE_SCHEM             TABLE_NAME TABLE_TYPE REMARKS
1   c:\\foo        <NA> 'my customers orders$'      TABLE   
<NA>
2   c:\\foo        <NA>        'my customers$'      TABLE   
<NA>
3   c:\\foo        <NA>         'my products$'      TABLE   
<NA>
4   c:\\foo        <NA>          'poor table$'      TABLE   
<NA>

sqlQuery(myChan,"select * from [my customers$] ")
  CUST_ID        NAME SOCIAL        DOB
1       1    superman   1234 1940-12-31
2       2      batman   2345 1960-01-01
3       3 wonderwoman   3456 1942-05-15
4       4   spiderman   4567 1982-09-30

sqlQuery(myChan,"select * from [my customers orders$] ")
  ORDER_ID CUST_ID       DATE PRODUCT_ID QUANTITY
1        1       3 1997-08-13          1       12
2        2       3 1998-07-23          7       24
3        3       1 1994-01-08          6       11
4        4       4 2001-11-13          5       32
5        5       2 1997-03-09          7        9

sqlQuery(myChan,"select * from [my products$] ")
  PRODUCT_ID PRODUCT_NAME
1          1         cape
2          2         mask
3          3       tights
4          4        boots
5          5      goggles
6          6       gloves
7          7      aspirin
sqlQuery(myChan,"select * from [poor table$] ")
  a bunch of stuff F2  F3          F4               F5        F6
1               NA NA  NA        <NA>             <NA>        NA
2               NA NA  NA        <NA>             <NA>        NA
3               NA NA  NA data_i_like more data I like        NA
4               NA NA 100        blue              hot  94.16857
5               NA NA 200         red             warm  35.85302
6               NA NA 300       green             cold 232.09150
7               NA NA 400        blue             cold  45.40191

sqlQuery(myChan,"select * from [my customers$] A, [my customers orders$] B 
where A.CUST_ID = B.CUST_ID AND A.SOCIAL > 3000", as.is=TRUE) 
  CUST_ID        NAME SOCIAL                 DOB ORDER_ID CUST_ID  DATE 
PRODUCT_ID QUANTITY
1       3 wonderwoman   3456 1942-05-15 00:00:00        2       3 
1998-07-23 00:00:00          7       24
2       3 wonderwoman   3456 1942-05-15 00:00:00        1       3 
1997-08-13 00:00:00          1       12
3       4   spiderman   4567 1982-09-30 00:00:00        4       4 
2001-11-13 00:00:00          5       32
$

f = sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL 
) VALUES( 5,'robin',5678 ) " )

[1] "[RODBC] ERROR: Could not SQLExecDirect"               "S1000
-3035
[Microsoft][ODBC Excel Driver] Operation must use an updateable query."

Additional reference: 
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

------------------------------------------------------------
CONFIDENTIALITY NOTICE: This electronic mail transmission (i...{{dropped}}

Hi Toby,

toby_marks at americancentury.com wrote:
> I have searched the archives for using insert into to update spreadsheets 
> using RODBC and have come up short. So, first off, is it possible?
I don't think so. Writing to an Excel spreadsheet is probably easier 
done using the RDCOMClient package. There are some good examples of how 
to do things that come with the package. Searching the R-help archives 
should also come up with some good examples.

Best,

Jim




-- 
James W. MacDonald, M.S.
Biostatistician
Affymetrix and cDNA Microarray Core
University of Michigan Cancer Center
1500 E. Medical Center Drive
7410 CCGC
Ann Arbor MI 48109
734-647-5623


**********************************************************
Electronic Mail is not secure, may not be read every day, and should not be used
for urgent or sensitive issues.

toby_marks at americancentury.com writes:
> I have searched the archives for using insert into to update spreadsheets 
> using RODBC and have come up short. So, first off, is it possible?
>
> I have put together a dummy xls table (c:\foo.xls)for exploring 
> possibilities of RODBC.  Ultimately, I am interested in replacing much of 
> our previous use of vba macros with R ( I'd prefer elimination, but
will
> take what I can get ).  In order to achieve this, I will still have a need 
> to update spreadsheets directly through R scripts. 
>
> Simple queries seem to work fantastic!  But, I am missing something when 
> it comes to writing.
>
> sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL
)
> VALUES( 5,'robin',5678 ) " )
> [1] "[RODBC] ERROR: Could not SQLExecDirect"      "S1000
-3035
> [Microsoft][ODBC Excel Driver] Operation must use an updateable
query."
[...]
>   Driver={Microsoft Excel Driver (*.xls)}
[...]
>
> Additional reference: 
>
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx
Your link describes the "Microsoft Jet OLE DB Provider" driver,
while your plain ODBC connections to Excel uses the "Microsoft OLE DB
Provider for ODBC Drivers" driver.

The documentation located here 
http://support.microsoft.com/?scid=kb%3Ben-us%3B257819&x=9&y=17
states:
> IMPORTANT: An ODBC connection to Excel is read-only by default. Your
> ADO Recordset LockType property setting does not override this
> connection-level setting. You must set ReadOnly to False in your
> connection string or your DSN configuration if you want to edit your
> data. Otherwise, you receive the following error message: Operation
> must use an updateable query.
Jens