By: Matteo Lorini | Comments (8) | Related: > Microsoft Excel Integration
Problem
Often there is a need to export data from SQL Server into an Excel spreadsheet. In this tip we look at how this can be done using T-SQL and R to generate an Excel file based on query results.
Solution
The solution that I am proposing is to use sp_execute_external_script to create a simple R script that allows a DBA or Developer, to quickly and automatically export the results of any query to an Excel spreadsheet.
This tip will not explain what is R or how to use sp_execute_external_script procedure. This tip's goal is to show how to quickly write generic ad-hoc code that will load SQL Server data into an Excel spreadsheet. The advantage is that a DBA or Developer can quickly provide the data to a business user easily and quickly in Excel format.
Download AdventureWorks Database
In this tip, I will use AdventureWorks2014 database that can be easily downloaded and restore it to your SQL Server.
Creation of Excel File from SQL Query using R
The R code that I am using to create the Excel files can be found in my previous article Export SQL Data to Excel. I will use a subset of the code in this example. I will also use Microsoft sp_execute_external_script procedure to invoke R code and pass parameters to it.
R Code
The first part of the scripts focuses with the installation of the necessary packages to work with Excel, in particular, we need to open an Excel package to manipulate our output. The last part of the script, we create the Excel workbook, and save the data. Please note that the Excel file has a static name myTable and the output file is static to D:\test\myTable.xlsx.
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
OutputDataSet <- SqlData;
packages <- c("openxlsx", "dplyr")
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library(openxlsx)
library(dplyr)
wb <- createWorkbook()
addWorksheet(wb, sheetName = "myTable")
writeData(wb, "myTable", OutputDataSet)
saveWorkbook(wb, file = paste("D:\\test\\myTable",".xlsx", sep=""), overwrite = TRUE)
';
SQL Query
The T-SQL script can be any script that returns data. The main thing to note is that the @sqlscript variable must be define as NVARCHAR.
DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE SalesYTD > 2000000
ORDER BY SalesYTD DESC;’;
Calling sp_execute_external_script
In this step we execute the procedure by passing input to the R and T-SQL code.
EXEC sp_execute_external_script
@language = N'R',
@script = @rscript,
@input_data_1 = @sqlscript,
@input_data_1_name = N'SqlData';
GO
Complete Code
Below are all three sets of the above code.
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
OutputDataSet <- SqlData;
packages <- c("openxlsx", "dplyr")
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library(openxlsx)
library(dplyr)
wb <- createWorkbook()
addWorksheet(wb, sheetName = "myTable")
writeData(wb, "myTable", OutputDataSet)
saveWorkbook(wb, file = paste("D:\\test\\myTable",".xlsx", sep=""), overwrite = TRUE)
';
DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE SalesYTD > 2000000
ORDER BY SalesYTD DESC;’;
EXEC sp_execute_external_script
@language = N'R',
@script = @rscript,
@input_data_1 = @sqlscript,
@input_data_1_name = N'SqlData';
GO
Below is all three sets of code and execution of the code.
The executed script shows our result in SQL Server Management Studio (SSMS) and produced the desired output file. From the file preview we can see that the Excel file contains the data produced by the query in SSMS.
Make Excel File Name Dynamic
To make our script more versatile, we are adding a parameter called @mytname and assign it the file name that we want to create. In the following example, I have assigned the value of "vSalesPerson" to be used to create the output file name and the name of the Excel worksheet.
The below screenshot, highlights the changes to the above script to accommodate the passing of the parameter @mytname.
Here is the code:
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
OutputDataSet <- SqlData;
packages <- c("openxlsx", "dplyr")
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library(openxlsx)
library(dplyr)
wb <- createWorkbook()
addWorksheet(wb, sheetName = mytname)
writeData(wb, mytname, OutputDataSet)
saveWorkbook(wb, file = paste(paste("D:\\test\\",mytname),".xlsx", sep=""), overwrite = TRUE)
';
DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE SalesYTD > 2000000
ORDER BY SalesYTD DESC;’;
EXEC sp_execute_external_script
@language = N'R',
@script = @rscript,
@input_data_1 = @sqlscript,
@input_data_1_name = N'SqlData'
@params = N'@mytname nvarchar(20)',
@mytname = 'vSalesPerson';
GO
When run, we get the file vSalesPerson.xlsx generated instead of a fixed file name like in the first script.
Streamline and Separate R Code
I will remove the R code from the script in order to make it more manageable and readable. The following R code was copied into a text file and saved as "TableToExcel1.r".
packages <- c("openxlsx", "dplyr")
if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
install.packages(setdiff(packages, rownames(installed.packages())))
}
library(openxlsx)
library(dplyr)
wb <- createWorkbook()
addWorksheet(wb, sheetName = mytname)
writeData(wb, mytname, OutputDataSet)
saveWorkbook(wb, file = paste(paste("D:\\test\\",mytname),".xlsx", sep=""), overwrite = TRUE)
As we can see below, all the R code that was present in the previous T-SQL script has been replace by the source() R function that tells the R script to load the code from file D:\test\TableToExcel1.r.
Here is the code.
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
OutputDataSet <- SqlData;
source("D:\\test\\TableToExcel1.r")
';
DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE SalesYTD > 2000000
ORDER BY SalesYTD DESC;’;
EXEC sp_execute_external_script
@language = N'R',
@script = @rscript,
@input_data_1 = @sqlscript,
@input_data_1_name = N'SqlData'
@params = N'@mytname nvarchar(20)',
@mytname = 'vSalesPerson';
GO
Conclusion
We have just demonstrated how easy it is to export SQL Server data to an Excel spreadsheet. Such a script can be handy to a DBA or Developer that needs to provide ad-hoc data to the business in Excel format.
Next Steps
- The reader will need to install RStudio in order to test this tip.
- Check out these tips
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
