Modify the summ() function to start like this summ <- function(Tabname){ query <- sprintf(" SELECT * FROM %s",Tabname) res <- dbGetQuery(con, query)
etc HTH, Eric On Fri, Jul 2, 2021 at 9:39 PM Kai Yang via R-help <r-help@r-project.org> wrote: > Hello List, > > The previous post look massy. I repost my question. Sorry, > > > I need to generate summary report for many tables (>200 tables). For each > table, I can use the script to generate report: > res <- dbGetQuery(con, "SELECT * FROM BIODBX.MECCUNIQUE2") > view(dfSummary(res), file = > "W:/project/_Joe.B/MSSQL/try/summarytools.BIODBX.MECCUNIQUE2.html") > rm(res) > BIODBX.MECCUNIQUE2 is the name of table. > > I have all of tables' name in a data frame. So, I'm trying to write a > function to do this: > summ <- function(Tabname){ > res <- dbGetQuery(con, "SELECT * FROM Tabname") > view(dfSummary(res), file = > "W:/project/_Joe.B/MSSQL/try/summarytools.Tabname.html") > rm(res) > } > for (i in dbtable$Tot_table) > { > Tabname <- as.character(sqldf(sprintf("SELECT Tot_table FROM dbtable", > i))) > summ(Tabname) > } > > 1. I created a function summ, the argument is Tabname. I put the Tabname > in the function. I hope it can be replaced one by one > 2. the table dbtable contents all tables' name (>200 rows), the field name > is Tot_table > 3. I want use "for" to establish a loop, which can automatic generate a > summary report for each table > > but I got error message below: > Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for > SQL Server][SQL Server]Invalid object name 'Tabname'. > [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could > not be prepared. > > <SQL> 'SELECT * FROM Tabname' > 10. stop(structure(list(message = "nanodbc/nanodbc.cpp:1655: 42000: > [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid > object name 'Tabname'. [Microsoft][ODBC Driver 17 for SQL Server][SQL > Server]Statement(s) could not be prepared. \n<SQL> > 'SELECT * FROM Tabname'", > call = NULL, cppstack = NULL), class = c("odbc::odbc_error", > "C++Error", "error", "condition"))) > 9.new_result(connection@ptr, statement, immediate) > 8.OdbcResult(connection = conn, statement = statement, params = params, > immediate = immediate) > 7..local(conn, statement, ...) > 6.dbSendQuery(conn, statement, params = params, ...) > 5.dbSendQuery(conn, statement, params = params, ...) > 4..local(conn, statement, ...) > 3.dbGetQuery(con, "SELECT * FROM Tabname") > 2.dbGetQuery(con, "SELECT * FROM Tabname") > 1.summ(Tabname) > > it seems the tables' name is not successfully pass into query. can someone > give me an instruction for this? > many thanks, > Kai > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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@r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.