How to Export Data from SQL Server to Excel


By:   |   Updated: 2020-05-22   |   Comments (1)   |   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


Last Updated: 2020-05-22


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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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



download


Recommended Reading

Passing Dynamic Query Values from Excel to SQL Server

How to Create a Burndown Chart in Excel from Scratch

Use Excel Parameters to Dynamically Export SQL Server Data

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

Insert, Update or Delete Data in SQL Server from Excel





get free sql tips
agree to terms


Learn more about SQL Server tools