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: molshansky@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. [[alternative HTML version deleted]]
Prof Brian Ripley
2007-Mar-23 08:34 UTC
[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: 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. > > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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, 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
Prof Brian Ripley
2007-Mar-23 10:30 UTC
[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: 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. >> >> >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at 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, 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
Hi, 2007/3/23, Moshe Olshansky <molshansky at chimaeracapital.com>:> Hello! > >I have no problem reading Excel files (each worksheet in the file isa >"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
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:gchappi at gmail.com] 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 <molshansky at chimaeracapital.com>:> Hello! > >I have no problem reading Excel files (each worksheet in the file isa >"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: 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.
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 FALSEYou 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@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. [[alternative HTML version deleted]]
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