[R] problem with sqlSave()
Dear R users, I've already asked this question yesterday, but, though I had answer for the other ones (and they helped a lot, thanks), for some reason didn't get an answer for this one (I would also appreciate to know why, so that I can improve the quality and clarity of my future questions). Here it is again: I'm trying to use RODBC to export dataframes to xls files. When I run this code I get an error: test - structure(list(Group = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5), Group = c(2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6), test = c(0.907901716802262, 0.344080298502179, 0.780331168285069, 2.15033450900747, 0.194069709110157, 0.406313481811673, 0.116434832984871, 1.52727249397868, 0.579195268552845, 0.420998518182892, 1.62939679051865, 0.138892090710994, 1.11253003450962, 0.555496810201502, 1.78610263147685), crit = c(2.97744276941854, 2.98843563665370, 3.05786246930315, 3.00681172135752, 2.99644239171413, 3.00036808377006, 3.07797861408659, 3.02408697737205, 3.01060847439059, 3.03639474502811, 2.99664917266571, 2.99135011459308, 3.04177033698215, 3.04239401519992, 3.00305006434149), se = c(12.1028423223510, 15.1532795810610, 16.3991441261986, 15.3598663144688, 14.9271014453042, 14.2113088036481, 15.5329116369945, 14.4313862483545, 13.9698880958026, 18.0115083683957, 17.0706765780184, 16.6823518483263, 18.1856549192307, 17.8216415967046, 16.8702244895529), df = c(99.682919819226, 89.3039016709219, 50.9067902231679, 76.0647290534967, 83.0087667496558, 80.2356900159524, 43.4849997568059, 66.760479193059, 73.8040951542063, 61.4088750589742, 82.8579246229594, 86.904905246028, 58.9562752558462, 58.5971747302359, 78.4453136851244)), .Names = c(Group, Group, test, crit, se, df), row.names = c(NA, -15L), class = data.frame) xlsFile - odbcConnectExcel(output.xls, readOnly=F)##the file is created here, it didn't exist before here sqlSave(xlsFile, test, tablename=test1, rownames=F, addPK=T, append=T) Error in sqlSave(xlsFile, test, tablename = test1, rownames = F, addPK = T, : HYS21 -1508 [Microsoft][Pilote ODBC Excel] Le champ 'Group' existe déjà dans la table 'test1'. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [test1] (Group NUMBER, Group NUMBER, test NUMBER, crit NUMBER, se NUMBER, df NUMBER)' The translation would be the field 'Group' already exists in the sheet 'test1'. It does create the sheet test1 but does not copy the data. It is obviously a problem with this dataset because it works with this for example: temp.df - data.frame(a = rnorm(10), b = runif(10)) But I have no idea what could be wrong. Could you please help me with this? Thanks in advance Regards, Ivan __ R-help@r-project.org 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] problem with sqlSave()
Hi Dennis, Actually, this data.frame is an output from a test. And there are 2 'Group' variables because it is a pairwise comparison test. Up to now, I've used write.csv() and I've never had such problems. I've tried changing the names to 'Group1' and 'Group2', and it indeed was the problem. Any idea except changing the names manually before using sqlSave()? Ivan Message original Sujet: Re: [R] problem with sqlSave() Date : Wed, 24 Feb 2010 01:36:37 -0800 De :Dennis Murphy djmu...@gmail.com Pour : ivan.calan...@uni-hamburg.de Why do you have the same name for two variables (Group)? That might explain the error message... HTH, Dennis On Wed, Feb 24, 2010 at 12:55 AM, Ivan Calandra ivan.calan...@uni-hamburg.de mailto:ivan.calan...@uni-hamburg.de wrote: Dear R users, I've already asked this question yesterday, but, though I had answer for the other ones (and they helped a lot, thanks), for some reason didn't get an answer for this one (I would also appreciate to know why, so that I can improve the quality and clarity of my future questions). Here it is again: I'm trying to use RODBC to export dataframes to xls files. When I run this code I get an error: test - structure(list(Group = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5), Group = c(2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6), test = c(0.907901716802262, 0.344080298502179, 0.780331168285069, 2.15033450900747, 0.194069709110157, 0.406313481811673, 0.116434832984871, 1.52727249397868, 0.579195268552845, 0.420998518182892, 1.62939679051865, 0.138892090710994, 1.11253003450962, 0.555496810201502, 1.78610263147685), crit = c(2.97744276941854, 2.98843563665370, 3.05786246930315, 3.00681172135752, 2.99644239171413, 3.00036808377006, 3.07797861408659, 3.02408697737205, 3.01060847439059, 3.03639474502811, 2.99664917266571, 2.99135011459308, 3.04177033698215, 3.04239401519992, 3.00305006434149), se = c(12.1028423223510, 15.1532795810610, 16.3991441261986, 15.3598663144688, 14.9271014453042, 14.2113088036481, 15.5329116369945, 14.4313862483545, 13.9698880958026, 18.0115083683957, 17.0706765780184, 16.6823518483263, 18.1856549192307, 17.8216415967046, 16.8702244895529), df = c(99.682919819226, 89.3039016709219, 50.9067902231679, 76.0647290534967, 83.0087667496558, 80.2356900159524, 43.4849997568059, 66.760479193059, 73.8040951542063, 61.4088750589742, 82.8579246229594, 86.904905246028, 58.9562752558462, 58.5971747302359, 78.4453136851244)), .Names = c(Group, Group, test, crit, se, df), row.names = c(NA, -15L), class = data.frame) xlsFile - odbcConnectExcel(output.xls, readOnly=F)##the file is created here, it didn't exist before here sqlSave(xlsFile, test, tablename=test1, rownames=F, addPK=T, append=T) Error in sqlSave(xlsFile, test, tablename = test1, rownames = F, addPK = T, : HYS21 -1508 [Microsoft][Pilote ODBC Excel] Le champ 'Group' existe déjà dans la table 'test1'. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [test1] (Group NUMBER, Group NUMBER, test NUMBER, crit NUMBER, se NUMBER, df NUMBER)' The translation would be the field 'Group' already exists in the sheet 'test1'. It does create the sheet test1 but does not copy the data. It is obviously a problem with this dataset because it works with this for example: temp.df - data.frame(a = rnorm(10), b = runif(10)) But I have no idea what could be wrong. Could you please help me with this? Thanks in advance Regards, Ivan __ R-help@r-project.org mailto:R-help@r-project.org 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. [[alternative HTML version deleted]] __ R-help@r-project.org 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] problem with sqlSave()
On Feb 24, 2010, at 4:50 AM, Ivan Calandra wrote: Hi Dennis, Actually, this data.frame is an output from a test. And there are 2 'Group' variables because it is a pairwise comparison test. Up to now, I've used write.csv() and I've never had such problems. I've tried changing the names to 'Group1' and 'Group2', and it indeed was the problem. Any idea except changing the names manually before using sqlSave()? You could pass the names through make.unique: names(test) - make.unique(names(test)) names(test) [1] Group Group.1 testcritse df Ivan Message original Sujet: Re: [R] problem with sqlSave() Date : Wed, 24 Feb 2010 01:36:37 -0800 De :Dennis Murphy djmu...@gmail.com Pour : ivan.calan...@uni-hamburg.de Why do you have the same name for two variables (Group)? That might explain the error message... HTH, Dennis On Wed, Feb 24, 2010 at 12:55 AM, Ivan Calandra ivan.calan...@uni-hamburg.de mailto:ivan.calan...@uni-hamburg.de wrote: Dear R users, I've already asked this question yesterday, but, though I had answer for the other ones (and they helped a lot, thanks), for some reason didn't get an answer for this one (I would also appreciate to know why, so that I can improve the quality and clarity of my future questions). Here it is again: I'm trying to use RODBC to export dataframes to xls files. When I run this code I get an error: test - structure(list(Group = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5), Group = c(2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6), test = c(0.907901716802262, 0.344080298502179, 0.780331168285069, 2.15033450900747, 0.194069709110157, 0.406313481811673, 0.116434832984871, 1.52727249397868, 0.579195268552845, 0.420998518182892, 1.62939679051865, 0.138892090710994, 1.11253003450962, 0.555496810201502, 1.78610263147685), crit = c(2.97744276941854, 2.98843563665370, 3.05786246930315, 3.00681172135752, 2.99644239171413, 3.00036808377006, 3.07797861408659, 3.02408697737205, 3.01060847439059, 3.03639474502811, 2.99664917266571, 2.99135011459308, 3.04177033698215, 3.04239401519992, 3.00305006434149), se = c(12.1028423223510, 15.1532795810610, 16.3991441261986, 15.3598663144688, 14.9271014453042, 14.2113088036481, 15.5329116369945, 14.4313862483545, 13.9698880958026, 18.0115083683957, 17.0706765780184, 16.6823518483263, 18.1856549192307, 17.8216415967046, 16.8702244895529), df = c(99.682919819226, 89.3039016709219, 50.9067902231679, 76.0647290534967, 83.0087667496558, 80.2356900159524, 43.4849997568059, 66.760479193059, 73.8040951542063, 61.4088750589742, 82.8579246229594, 86.904905246028, 58.9562752558462, 58.5971747302359, 78.4453136851244)), .Names = c(Group, Group, test, crit, se, df), row.names = c(NA, -15L), class = data.frame) xlsFile - odbcConnectExcel(output.xls, readOnly=F)##the file is created here, it didn't exist before here sqlSave(xlsFile, test, tablename=test1, rownames=F, addPK=T, append=T) Error in sqlSave(xlsFile, test, tablename = test1, rownames = F, addPK = T, : HYS21 -1508 [Microsoft][Pilote ODBC Excel] Le champ 'Group' existe déjà dans la table 'test1'. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [test1] (Group NUMBER, Group NUMBER, test NUMBER, crit NUMBER, se NUMBER, df NUMBER)' The translation would be the field 'Group' already exists in the sheet 'test1'. It does create the sheet test1 but does not copy the data. It is obviously a problem with this dataset because it works with this for example: temp.df - data.frame(a = rnorm(10), b = runif(10)) But I have no idea what could be wrong. Could you please help me with this? Thanks in advance Regards, Ivan __ R-help@r-project.org mailto:R-help@r-project.org 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. [[alternative HTML version deleted]] __ R-help@r-project.org 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@r-project.org 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] problem with sqlSave()
Thanks David, this is exactly what I need! Ivan Le 2/24/2010 12:00, David Winsemius a écrit : On Feb 24, 2010, at 4:50 AM, Ivan Calandra wrote: Hi Dennis, Actually, this data.frame is an output from a test. And there are 2 'Group' variables because it is a pairwise comparison test. Up to now, I've used write.csv() and I've never had such problems. I've tried changing the names to 'Group1' and 'Group2', and it indeed was the problem. Any idea except changing the names manually before using sqlSave()? You could pass the names through make.unique: names(test) - make.unique(names(test)) names(test) [1] Group Group.1 testcritse df Ivan Message original Sujet: Re: [R] problem with sqlSave() Date : Wed, 24 Feb 2010 01:36:37 -0800 De : Dennis Murphy djmu...@gmail.com Pour : ivan.calan...@uni-hamburg.de Why do you have the same name for two variables (Group)? That might explain the error message... HTH, Dennis On Wed, Feb 24, 2010 at 12:55 AM, Ivan Calandra ivan.calan...@uni-hamburg.de mailto:ivan.calan...@uni-hamburg.de wrote: Dear R users, I've already asked this question yesterday, but, though I had answer for the other ones (and they helped a lot, thanks), for some reason didn't get an answer for this one (I would also appreciate to know why, so that I can improve the quality and clarity of my future questions). Here it is again: I'm trying to use RODBC to export dataframes to xls files. When I run this code I get an error: test - structure(list(Group = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5), Group = c(2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6), test = c(0.907901716802262, 0.344080298502179, 0.780331168285069, 2.15033450900747, 0.194069709110157, 0.406313481811673, 0.116434832984871, 1.52727249397868, 0.579195268552845, 0.420998518182892, 1.62939679051865, 0.138892090710994, 1.11253003450962, 0.555496810201502, 1.78610263147685), crit = c(2.97744276941854, 2.98843563665370, 3.05786246930315, 3.00681172135752, 2.99644239171413, 3.00036808377006, 3.07797861408659, 3.02408697737205, 3.01060847439059, 3.03639474502811, 2.99664917266571, 2.99135011459308, 3.04177033698215, 3.04239401519992, 3.00305006434149), se = c(12.1028423223510, 15.1532795810610, 16.3991441261986, 15.3598663144688, 14.9271014453042, 14.2113088036481, 15.5329116369945, 14.4313862483545, 13.9698880958026, 18.0115083683957, 17.0706765780184, 16.6823518483263, 18.1856549192307, 17.8216415967046, 16.8702244895529), df = c(99.682919819226, 89.3039016709219, 50.9067902231679, 76.0647290534967, 83.0087667496558, 80.2356900159524, 43.4849997568059, 66.760479193059, 73.8040951542063, 61.4088750589742, 82.8579246229594, 86.904905246028, 58.9562752558462, 58.5971747302359, 78.4453136851244)), .Names = c(Group, Group, test, crit, se, df), row.names = c(NA, -15L), class = data.frame) xlsFile - odbcConnectExcel(output.xls, readOnly=F)##the file is created here, it didn't exist before here sqlSave(xlsFile, test, tablename=test1, rownames=F, addPK=T, append=T) Error in sqlSave(xlsFile, test, tablename = test1, rownames = F, addPK = T, : HYS21 -1508 [Microsoft][Pilote ODBC Excel] Le champ 'Group' existe déjà dans la table 'test1'. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [test1] (Group NUMBER, Group NUMBER, test NUMBER, crit NUMBER, se NUMBER, df NUMBER)' The translation would be the field 'Group' already exists in the sheet 'test1'. It does create the sheet test1 but does not copy the data. It is obviously a problem with this dataset because it works with this for example: temp.df - data.frame(a = rnorm(10), b = runif(10)) But I have no idea what could be wrong. Could you please help me with this? Thanks in advance Regards, Ivan __ R-help@r-project.org mailto:R-help@r-project.org 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. [[alternative HTML version deleted]] __ R-help@r-project.org 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@r-project.org 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] Problem with sqlSave
On Thu, 19 Nov 2009 04:07:52 -0800 (PST) anna_l lippelanna21 @hotmail.com wrote: Hello, the sqlSave function is used in order to write a dataframe on excel. This function creates worksheets using the attribute tablename and writes the data.frame in it. What I want to do is to create this data.frame but being able in case this worksheet already exists to delete the former datas and write the new ones in it. I used the safer and append attributes. When you set safer to false, from what I understood, it should delete the former datas. Then what I did is setting safer as false and append as true so it can append to the empty dataframe the new dataframe but it didn?t work. Your question doesn't make sense. If you want to delete the older data, you should *not* use append = TRUE, since that is used only in the case that you want to append the new data to the old date. Since you *don't* want to retain the old data, why do you set append = TRUE? -- Karl Ove Hufthammer __ R-help@r-project.org 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] Problem with sqlSave
Hi Karl, I was setting append=true because safer would delete the former datas when set to false. I must not have understood the purpose of these two attributes right. To be honest I tried all possible combinations and none would work. I am doing all this now with RExcel as I have been advised and it worked very well. Thank you for trying to help. Karl Ove Hufthammer wrote: On Thu, 19 Nov 2009 04:07:52 -0800 (PST) anna_l lippelanna21 @hotmail.com wrote: Hello, the sqlSave function is used in order to write a dataframe on excel. This function creates worksheets using the attribute tablename and writes the data.frame in it. What I want to do is to create this data.frame but being able in case this worksheet already exists to delete the former datas and write the new ones in it. I used the safer and append attributes. When you set safer to false, from what I understood, it should delete the former datas. Then what I did is setting safer as false and append as true so it can append to the empty dataframe the new dataframe but it didn?t work. Your question doesn't make sense. If you want to delete the older data, you should *not* use append = TRUE, since that is used only in the case that you want to append the new data to the old date. Since you *don't* want to retain the old data, why do you set append = TRUE? -- Karl Ove Hufthammer __ R-help@r-project.org 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. - Anna Lippel new in R so be careful I should be asking a lt of questions!:teeth: -- View this message in context: http://old.nabble.com/Problem-with-sqlSave-tp26421303p26441564.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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] Problem with sqlSave
Hello, the sqlSave function is used in order to write a dataframe on excel. This function creates worksheets using the attribute tablename and writes the data.frame in it. What I want to do is to create this data.frame but being able in case this worksheet already exists to delete the former datas and write the new ones in it. I used the safer and append attributes. When you set safer to false, from what I understood, it should delete the former datas. Then what I did is setting safer as false and append as true so it can append to the empty dataframe the new dataframe but it didn´t work. It appends in the existing worksheets but doesn´ t delete the existing datas. Any idea? - Anna Lippel new in R so be careful I should be asking a lt of questions!:teeth: -- View this message in context: http://old.nabble.com/Problem-with-sqlSave-tp26421303p26421303.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
