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 at 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