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

[Katharina May]

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
>