I'm writing SQL queries in Excel Spreadsheets and I have linked Rstudio with SQL server using ODBC.
Now, using sqlQuery command in R, any query can be run. For example,
library(RODBC)
mycon <- odbcConnect("MYSQLSERVER")
a1 <- sqlQuery(mycon,paste("SELECT TOP 10 * FROM USER"]
If the same query (or a number of queries) is written in a particular cell (or different cells) in an Excel file, how can I get the output?
Edit: The excel file will be used as an input and once all the queries are written in the excel file, the R code will be run. The user will only be provided to edit the excel file. How do I connect all this process? I just need a start. I found XLConnect and the readNamedRegion() function but that's the extent of it.
With same exact package, RODBC, you can actually run SQL queries on Excel workbooks, even specifying a range of the needed cells.
Consider connecting to Excel, retrieving SQL statements in cells into a dataframe and then looping through such a queriesdf to pass as calls to SQL Server fetches.
library(RODBC)
# EXCEL CONNECTION -----------------------------
xlconn <- odbcDriverConnect('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\\Path\\To\\ExcelWorkbook.xlsx')
# WHOLE WORKSHEET (FIRST ROW AS HEADERS)
queriesdf <- sqlQuery(xlconn, "SELECT * FROM [Sheet1$]")
# SPECIFIC RANGE (FIRST ROW AS HEADERS)
queriesdf <- sqlQuery(xlconn, "SELECT * FROM [Sheet1$A1:Z100]")
close(xlconn)
# SQL SERVER CONNECTION ------------------------
mssqlconn <- odbcConnect("MYSQLSERVER")
# LIST OF DATA FRAMES
dfList <- lapply(queriesdf$QueryCol, function(q) sqlQuery(mssqlconn, q))
close(mssqlconn)