I finally found the problem within my site_data object...
this works well:
<snip>
site_data <- sqlQuery(channel, "select site_no from [biomass_data$]
group by site_no")$site_no
for(i in site_data) {
assign(paste("site",i,"_data",sep=""), sqlQuery(channel,
paste("select * from [biomass_data$] where site_no = ",i,sep="")))
}
</snap>
Thanks for all your solution proposals!
Katharina
On 21.04.2009, at 02:43, David Winsemius wrote:
On Apr 20, 2009, at 8:04 PM, Katharina May wrote:
Hi there,
I've got a database or rather spreadsheet with several columns and
rows.
For one column named sites I want to loop through all possible
values and retrieve
all data out of the database where site = x and write it into an
object named 'sitex_data'.
Somehow I'm really missing something as I'm not able to create
these sitex_data objects with
the database values, neither using list nor assign...
Here some code snipplets:
>library (RODBC)
>channel <- odbcConnectExcel2007 ("biomass_data.xlsx")
>site_data <- sqlQuery(channel, "select site_no from
[biomass_data$] group by site_no")
#Here the values I want to loop through
>str(site_data)
'data.frame': 44 obs. of 1 variable:
$ site_no: num 4 7 9 10 15 16 17 18 19 20 ...
#Here my first try [error message on the line within the loop,
saying something like:
# 'recursive indexing on level 2 failed']
sites_object_list <- vector("list",99)
for(i in site_data) {
sites_object_list[[i]] <- sqlQuery(channel, paste("select * from
[biomass_data$] where site_no = ",i,sep=""))
}
This does not appear to be a dataset to which we have access, so I
will take an untested stab at this. What happens if you assign the
results of the function unique() around site_data$site_no to your
index, i? That way you would stand a chance of referring to the
sites in a manner that R might understand and to only use the site
numbers once apiece in the loop. I'm not a competent SQL programmer
so i have no comments on that part.
David Winsemius, MD
Heritage Laboratories
West Hartford, CT
______________________________________________
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.
