Re: [R] Updating a worksheet in Excel file using RODBC
You could use RDCOMClient or rcom packages to update an Excel spreadsheet in place and you would not need any VBA at all. Search through the archives for the keyword Excel.Application . On 3/27/07, Moshe Olshansky [EMAIL PROTECTED] wrote: 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:[EMAIL PROTECTED] Sent: Tuesday, 27 March 2007 4:44 PM To: Moshe Olshansky Cc: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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, [EMAIL PROTECTED] 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, UKFax: +44 1865 272595 __ 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-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] Updating a worksheet in Excel file using RODBC
Dear Hans-Peter, Thank you very much for your note! I tried your package and it works all right (i.e. it indeed writes data to Excel files), however it creates a new Excel file and this is not what I really need. I need to update/create one sheet in the existing file. I am using R do compute some data but then it must be put into an Excel file and an Excel chart must be created. So I intended to (manually) create one excel file and to write a VBA macro which makes a desirable chart of a certain sheet (let's say Sheet1). Then I intended to autoomatically make many copies of this file with appropriate file names, write the right data to Sheet1 of each such file so that when it is opened a desired chart is automatically created. So creting a totally new file does not help me. Best regards, Moshe. -Original Message- From: Hans-Peter [mailto:[EMAIL PROTECTED] Sent: Saturday, 24 March 2007 3:44 AM To: Moshe Olshansky Cc: R Help Subject: Re: [R] Updating a worksheet in Excel file using RODBC Hi, 2007/3/23, Moshe Olshansky [EMAIL PROTECTED]: Hello! I have no problem reading Excel files (each worksheet in the file is a table which can be read - at least in my case). What I would like to do is to read such a table, change it (just the contents, not the format) and write it back, and this I can not do. I am getting the following error messages (3 slightly different attempts): [snip] As another option (if you work with Windows) you can check my xlsReadWrite package (- CRAN). It should work very well in your case (it's not suited if you want to use SQL (join) statements, but for plain data reading/writing it is nice). For both versions (free/pro) updates are pending. They should be released by end of next week (but no guarantees). -- Regards, Hans-Peter Moshe Olshansky Chimaera Capital Limited Level 4 / 349 Collins Street Melbourne, Victoria 3000 Phone: +613 8614 8400 Fax: +613 8614 8410 Email: [EMAIL PROTECTED] 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. __ 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] Updating a worksheet in Excel file using RODBC
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: [EMAIL PROTECTED] 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. [[alternative HTML version deleted]] __ 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] 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: [EMAIL PROTECTED] 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, [EMAIL PROTECTED] 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, UKFax: +44 1865 272595 __ 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] Updating a worksheet in Excel file using RODBC
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:[EMAIL PROTECTED] Sent: Tuesday, 27 March 2007 4:44 PM To: Moshe Olshansky Cc: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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, [EMAIL PROTECTED] 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, UKFax: +44 1865 272595 __ 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] Updating a worksheet in Excel file using RODBC
Hello! I have no problem reading Excel files (each worksheet in the file is a table which can be read - at least in my case). What I would like to do is to read such a table, change it (just the contents, not the format) and write it back, and this I can not do. I am getting the following error messages (3 slightly different attempts): sqlSave(con, x, tablename = Chimaera20_3years$, append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE Chimaera20_3years$ (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = Chimaera20_3years$, append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3551 [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement. sqlSave(con, x, tablename = [Chimaera20_3years$], append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE [Chimaera20_3years$] (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = [Chimaera20_3years$], append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3553 [Microsoft][ODBC Excel Driver] Syntax error in field definition. sqlSave(con, x, tablename = [Chimaera20_3years], append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE [Chimaera20_3years] (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = [Chimaera20_3years], append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3553 [Microsoft][ODBC Excel Driver] Syntax error in field definition. Am I doing it wrong way or is there a problem with the Excel driver? Thank you in advance, Moshe Olshansky Chimaera Capital Group Moshe Olshansky Chimaera Capital Limited Level 4 / 349 Collins Street Melbourne, Victoria 3000 Phone: +613 8614 8400 Fax: +613 8614 8410 Email: [EMAIL PROTECTED] 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. [[alternative HTML version deleted]] __ 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] Updating a worksheet in Excel file using RODBC
The problem is that way the ODBC driver exposes table names is not valid SQL, and nor is the way quoting has to be used. You can get around this via direct SQL sent by sqlQuery. In addition, by default the Excel ODBC driver gives you read-only access to worksheets. Searching the list archives, would help, for example this answer: https://stat.ethz.ch/pipermail/r-help/2007-March/127851.html Making a wrapper interface in RODBC is on my TODO list, but not anywhere near the top. On Fri, 23 Mar 2007, Moshe Olshansky wrote: Hello! I have no problem reading Excel files (each worksheet in the file is a table which can be read - at least in my case). What I would like to do is to read such a table, change it (just the contents, not the format) and write it back, and this I can not do. I am getting the following error messages (3 slightly different attempts): sqlSave(con, x, tablename = Chimaera20_3years$, append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE Chimaera20_3years$ (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = Chimaera20_3years$, append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3551 [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement. sqlSave(con, x, tablename = [Chimaera20_3years$], append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE [Chimaera20_3years$] (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = [Chimaera20_3years$], append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3553 [Microsoft][ODBC Excel Driver] Syntax error in field definition. sqlSave(con, x, tablename = [Chimaera20_3years], append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE [Chimaera20_3years] (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = [Chimaera20_3years], append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3553 [Microsoft][ODBC Excel Driver] Syntax error in field definition. Am I doing it wrong way or is there a problem with the Excel driver? Thank you in advance, Moshe Olshansky Chimaera Capital Group Moshe Olshansky Chimaera Capital Limited Level 4 / 349 Collins Street Melbourne, Victoria 3000 Phone: +613 8614 8400 Fax: +613 8614 8410 Email: [EMAIL PROTECTED] 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. [[alternative HTML version deleted]] __ 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. -- Brian D. Ripley, [EMAIL PROTECTED] 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, UKFax: +44 1865 272595 __ 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] Updating a worksheet in Excel file using RODBC
On Fri, 23 Mar 2007, Prof Brian Ripley wrote: The problem is that way the ODBC driver exposes table names is not valid SQL, and nor is the way quoting has to be used. You can get around this via direct SQL sent by sqlQuery. In addition, by default the Excel ODBC driver gives you read-only access to worksheets. Searching the list archives, would help, for example this answer: https://stat.ethz.ch/pipermail/r-help/2007-March/127851.html Making a wrapper interface in RODBC is on my TODO list, but not anywhere near the top. 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 In your example you have a different problem: your field names are invalid in SQL (and as R data frame names). On Fri, 23 Mar 2007, Moshe Olshansky wrote: Hello! I have no problem reading Excel files (each worksheet in the file is a table which can be read - at least in my case). What I would like to do is to read such a table, change it (just the contents, not the format) and write it back, and this I can not do. I am getting the following error messages (3 slightly different attempts): sqlSave(con, x, tablename = Chimaera20_3years$, append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE Chimaera20_3years$ (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = Chimaera20_3years$, append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3551 [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement. sqlSave(con, x, tablename = [Chimaera20_3years$], append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE [Chimaera20_3years$] (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = [Chimaera20_3years$], append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3553 [Microsoft][ODBC Excel Driver] Syntax error in field definition. sqlSave(con, x, tablename = [Chimaera20_3years], append = FALSE, + rownames = FALSE, colnames = TRUE, + verbose = TRUE, oldstyle = FALSE,safer=FALSE) Query: CREATE TABLE [Chimaera20_3years] (Date varchar(255), 000Tax varchar(255), 1500Tax varchar(255), 3000Tax varchar(255), 4650Tax varchar(255)) Error in sqlSave(con, x, tablename = [Chimaera20_3years], append = FALSE, : [RODBC] ERROR: Could not SQLExecDirect 37000 -3553 [Microsoft][ODBC Excel Driver] Syntax error in field definition. Am I doing it wrong way or is there a problem with the Excel driver? Thank you in advance, Moshe Olshansky Chimaera Capital Group Moshe Olshansky Chimaera Capital Limited Level 4 / 349 Collins Street Melbourne, Victoria 3000 Phone: +613 8614 8400 Fax: +613 8614 8410 Email: [EMAIL PROTECTED] 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. [[alternative HTML version deleted]] __ 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. -- Brian D. Ripley, [EMAIL PROTECTED] 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, UKFax: +44 1865 272595 __ 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] Updating a worksheet in Excel file using RODBC
Hi, 2007/3/23, Moshe Olshansky [EMAIL PROTECTED]: Hello! I have no problem reading Excel files (each worksheet in the file is a table which can be read - at least in my case). What I would like to do is to read such a table, change it (just the contents, not the format) and write it back, and this I can not do. I am getting the following error messages (3 slightly different attempts): [snip] As another option (if you work with Windows) you can check my xlsReadWrite package (- CRAN). It should work very well in your case (it's not suited if you want to use SQL (join) statements, but for plain data reading/writing it is nice). For both versions (free/pro) updates are pending. They should be released by end of next week (but no guarantees). -- Regards, Hans-Peter __ 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.
