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

Katharina May may.katharina at googlemail.com
Tue Apr 21 02:04:54 CEST 2009


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=""))
  }

#Here my second try [error message  on the line within the loop,  
saying something like:
# 'only the first element will be used as variable name']
for(i in site_data) {
	assign(paste("site",i,"_data",sep=""), sqlQuery(channel,  
paste("select * from  [biomass_data$] where site_no = ",i,sep="")))

}

I would be very, very glad if anybody sends me a clue about this, as  
I'm a obviuos Newbie using
R...

Thanks,

          Katharina




More information about the R-help mailing list