SQL Server Export/Import excel using R script - Part 6

In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3, Part 4 & Part 5. In this post, let us see how to invoke sql files in a folder and export the output of the query as Excel files.

As a prerequisite, we need R packages "xlsx"  & "rJava", which I had showed in my previous posts on how to install and load them.

As we are going to make use of the powershell SQL Server cmdlets "Invoke-Sqlcmd", let us make sure SQL Server powershell modules are downloaded & installed from Powershell.

For this example, I have created below folder structure:

Temp folder - Is a placeholder for staging files created in the intermediate process
Script folder - Has the R script that runs this entire process
BusinessReports folder - Where excel reports are exported
SQL folder -  Contains the SQL files to be executed

For this example, I have placed three .sql files in a folder and the sql inside the file are as shown in below screenshot. All three sql queries are selecting data from three different tables in "WideWorldImporters" sample database.

Explanation on how this approach works:

R invokes the powershell command using "system2" function. Powershell command then reads all the sql files (using Invoke-Sqlcmd) and writes the sql result to a text file (just as a staging). Only one staging file will be created for entire process of exporting SQL files output to Excel and will be deleted at the end of the process. Using the data in staging text file on each iteration, an excel is created using "xlsx" r package.

Below R script does everything what I have mentioned in above explanation:

I have saved the above script in Script folder, it can be either manually opened & executed from any R environment (R studio etc.) or this script can be invoked from command line.

Below screenshot shows on how to invoke R script from command Prompt by using Rscript.exe in SQL Server folder:

Goto Run -> cmd -> 

This command line can also be created as a SQL agent job and scheduled as per requirement.

CD "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\R_SERVICES\bin"
Rscript.exe "H:\Temp\Blog\ImportExportExcel\Script\SQLExportExcel.R"

After executing the R script from Command Prompt or R studio:
We can see the output of SQL files in Excel format within a directory created with date and Excel file & sheet name is SQL file name

Executing the R script on same day, just overwrites the directory & excel files without error and a separate directory is created when the script is executed on the next day.

I tested this scenario with the above script, where if one of the SQL file has incorrect syntax, script still works for all the other SQL files and runs the entire process. 

Using password argument in write.xlsx2 function, the exported excel files can be password protected. But unfortunately writing of password protected workbooks is supported for Excel 2007 OOXML format only.

Below is the complete code block:

Please note: In the below script, we need to configure the values for 
database connection details and folder path details 

library(foreach);
Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/")
library(rJava);
library("xlsx");
#getting list of sql files & file names from folder
list_filespath <- list.files(path = "H:/Temp/Blog/ImportExportExcel/SQL", pattern = ".sql", all.files = FALSE,
                             full.names = TRUE, recursive = FALSE,
                             ignore.case = FALSE, include.dirs = FALSE, no.. = TRUE)
list_filenames <- list.files(path = "H:/Temp/Blog/ImportExportExcel/SQL", pattern = ".sql", all.files = FALSE,
                             full.names = FALSE, recursive = FALSE,
                             ignore.case = FALSE, include.dirs = FALSE, no.. = TRUE)
#Check file exists in path
if (length(list_filespath) != 0) {
  #creating new directory on each day for SQL output 
  newdir <- paste("H:/Temp/Blog/ImportExportExcel/BusinessReports/Report", Sys.Date(), sep = "_")
  dir.create(newdir)
  foreach(m = 1:length(list_filespath)) %do%
  {
  #forming the powershell command to invoke sql files  
  command1 <- "Invoke-Sqlcmd -ServerInstance 'LAPTOP-LCUB6HBB' -Database WideWorldImporters  -InputFile "
  command3 <- "| Export-Csv 'H:/Temp/Blog/ImportExportExcel/Temp/Stagingfile.txt'"
  command <- paste(command1,list_filespath[m],command3, sep=" ")
  system2("powershell", args = command)
  #powershell Invoke-sqlcmd output to staging file in .txt format
  StagingOut <- read.table('H:/Temp/Blog/ImportExportExcel/Temp/Stagingfile.txt', skip = 1, header =TRUE, sep =',')
  #create excel output using .txt staging file
  ExcelFilename <- paste(newdir,"/",gsub(".sql", "", list_filenames[m]),".xlsx", sep="")
  write.xlsx2(StagingOut, file = ExcelFilename, sheetName=list_filenames[m],
              col.names=TRUE,  append=FALSE, password= "Password123")
  }
}
#Delete staging file (intermediate file before exporting to excel) if it exist
if (file.exists('H:/Temp/Blog/ImportExportExcel/Temp/Stagingfile.txt')) 
{
  file.remove('H:/Temp/Blog/ImportExportExcel/Temp/Stagingfile.txt')
}

See Also:

SQL Server Export/Import excel using R script
My post on Technet wiki - SQL Server - Export SQL file output to Excel file

Happy new year 2019 !!