Re: [R] RODBC Excel sqlQuery insert into
[EMAIL PROTECTED] 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%3B257819x=9y=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
__
R-help@stat.math.ethz.ch 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.
[R] 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:\\fooNA 'my customers orders$' TABLENA
2 c:\\fooNA'my customers$' TABLENA
3 c:\\fooNA 'my products$' TABLENA
4 c:\\fooNA 'poor table$' TABLENA
sqlQuery(myChan,select * from [my customers$] )
CUST_IDNAME SOCIALDOB
1 1superman 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
11 3 1997-08-13 1 12
22 3 1998-07-23 7 24
33 1 1994-01-08 6 11
44 4 2001-11-13 5 32
55 2 1997-03-09 79
sqlQuery(myChan,select * from [my products$] )
PRODUCT_ID PRODUCT_NAME
1 1 cape
2 2 mask
3 3 tights
4 4boots
5 5 goggles
6 6 gloves
7 7 aspirin
sqlQuery(myChan,select * from [poor table$] )
a bunch of stuff F2 F3 F4 F5F6
1 NA NA NANA NANA
2 NA NA NANA NANA
3 NA NA NA data_i_like more data I likeNA
4 NA NA 100blue hot 94.16857
5 NA NA 200 red warm 35.85302
6 NA NA 300 green cold 232.09150
7 NA NA 400blue 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_IDNAME SOCIAL DOB ORDER_ID CUST_ID DATE
PRODUCT_ID QUANTITY
1 3 wonderwoman 3456 1942-05-15 00:00:002 3
1998-07-23 00:00:00 7 24
2 3 wonderwoman 3456 1942-05-15 00:00:001 3
1997-08-13 00:00:00 1 12
3 4 spiderman 4567 1982-09-30 00:00:004 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}}
__
R-help@stat.math.ethz.ch 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.
Re: [R] RODBC Excel sqlQuery insert into
Hi Toby, [EMAIL PROTECTED] 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. __ R-help@stat.math.ethz.ch 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.
Re: [R] RODBC Excel sqlQuery insert into
I do recall seeing posts on RDOM flying around from time to time and today
have stumbled across as well through my RODBC searches. I have also been
reminded from another reply of RCOM being a potential solution. I had
hoped to stick to more of a pure sql based process, but certainly R makes
it easy to change course when necessary. I shall do some digging into
each of these packages now as well. Thanks for the response!
--Cheers.
James W. MacDonald [EMAIL PROTECTED]
03/13/2007 02:05 PM
To
[EMAIL PROTECTED]
cc
r-help@stat.math.ethz.ch
Subject
Re: [R] RODBC Excel sqlQuery insert into
Hi Toby,
[EMAIL PROTECTED] 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.
CONFIDENTIALITY NOTICE: This electronic mail transmission (i...{{dropped}}
__
R-help@stat.math.ethz.ch 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.
