[R] problem with sqlSave()

Wed Feb 24 12:27:59 CET 2010

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" "test"    "crit"    "se"      "df"
>
>>
>> Ivan
>>
>>
>> -------- Message original --------
>> Sujet:     Re: [R] problem with sqlSave()
>> Date :     Wed, 24 Feb 2010 01:36:37 -0800
>> De :     Dennis Murphy <djmuser at gmail.com>
>> Pour :     ivan.calandra at 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.calandra at uni-hamburg.de <mailto:ivan.calandra at 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 at r-project.org <mailto:R-help at 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 at 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.
>
>