[R] create objects in a loop and adding sqlQuery content to them

[David Winsemius]

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