How to Export Data from SQL Server to Excel


By:   |   Updated: 2020-05-22   |   Comments (6)   |   Related: More > 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.

databases

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.

sql script

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.

my table

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.

r script

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.

my tablwe

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
r script

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





get scripts

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips


Article Last Updated: 2020-05-22

Comments For This Article




Tuesday, January 5, 2021 - 11:38:03 AM - Matteo Lorini Back To Top (88004)
Ali,
it looks like the account that runs SQL Server does not have permission to write to that drive. This is a Account/Permission issue. Try to write to another folder or add permissions to the E: drive for the account that runs the SQL Server service.

Monday, January 4, 2021 - 8:33:43 PM - Ali Back To Top (87997)
Hello Mr. Lorini :)

I did all the requirements and still face one fatal problem. The R script tries to create the excel file but is denied the write permission on the local drive. The error returned is the following from the messages section of SSMS:

Warning message:
In file.create(to[okay]) :
cannot create file 'e:\ 1399.10.16.xlsx', reason 'Permission denied'

I truly appreciate your help. :)
Thanks,
Ali

Wednesday, December 30, 2020 - 11:57:30 AM - Matteo Lorini Back To Top (87981)
Ali, make sure that R
MSSQL R Serviceis installe see: https://www.mssqltips.com/sqlservertip/6465/quick-start-guide-for-data-science-with-sql-server-and-r-services/

Also make sure that this is enable:

EXEC sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

Tuesday, December 29, 2020 - 3:26:25 AM - Ali Back To Top (87963)
Hi Mr. Lorini

I get 2 errors in this solution. when I run SQL script under ssms i get the following error:

Msg 39020, Level 16, State 2, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0]
Feature 'Advanced Analytics Extensions' is not installed. Please consult Books Online for more information on this feature.

when I run r script the following error is returned:

Error in saveWorkbook(wb, file = paste("D:\\test\\myTable", ".xlsx", sep = ""), :
could not find function "saveWorkbook"

I appreciate your help :)
Thanks,
Ali

Thursday, November 19, 2020 - 11:15:29 PM - Mithalesh Gupta Back To Top (87814)
Thanks a lot for this informative post.
You are one of very few bloggers who has explained approach in a step-wise manner.

Keep it up.

Thanks you

Friday, May 22, 2020 - 9:11:36 AM - Ginger Back To Top (85725)

Excellent article - this is exactly what I need to do!  Thanks for the clear and consise explanation :)



download














get free sql tips
agree to terms