Export SQL Server Tables to Excel


By:   |   Updated: 2020-04-14   |   Comments (4)   |   Related: More > Import and Export

Problem

Sometimes there is a need to export data for a lot of different SQL Server tables into an Excel file.  In this tip we look at how we can quickly export several tables into one Excel workbook where each table is stored in a different worksheet.  In a prior tip we learned how to Load Excel Data Into SQL Server.

Solution

The solution that I am proposing is to create a simple R program that allows you to quickly and automatically query a series of tables and provide the results in an Excel spreadsheet where each worksheet represents data from a specific table.

Export SQL Server Data to Microsoft Excel using R

This tip is not aimed to explain what is R or how to use it, the goal is to show how quickly we can write generic ad-hoc code that will load SQL Server data into an Excel spreadsheet.

The advantage of this approach is that you can quickly provide data to the business easily and quickly in an Excel format.

Step 1 – Download AdventureWorks Database

In this tip, I will use AdventureWorks2014 database that can be easily downloaded from Microsoft and restored to your SQL Server.

adventureworks tables

The goal of this exercise is to SELECT the top 50 rows of each tables that have a "Sales" schema name and to load it a single spreadsheet where each worksheet represents a different table.  Below are the tables.

adventureworks sales schema tables

First of all, I will show how each section of R code and at the end I will put it all together in one R script.

Step 2 – Open RStudio and Import Libraries

Let us open RStudio and import the libraries that will allow us to connect to SQL and to work with an Excel spreadsheet.

rstudio libraries

Step 3 – Connect to SQL Server

Let us use R to connect to MSSQL using integrated security.

SQL Connection string

Step 4 – Load data into R dataframe

Let us use R to load all of the tables having "Sales" as a schema into an R dataframe and view the results.

> ## Prepare my Query
> myQuery <- "select table_name from information_schema.TABLES where TABLE_SCHEMA='Sales' and table_type='BASE TABLE'"
> ## Execute my query and load the results into VmyQuery dataframe
> VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery,stringsAsFactors = F,as.is = T)
> ## View my results in RStodio
> View(VmyQuery)
query list of tables from R

Step 5 – Export SQL Server Data to Excel file

This step represents the core functionality of the script. We need to create a loop for each table and save the data to a specific Excel worksheet.

Let us show first how a simple "for" RR statement can list the table names loaded in our dataframe.

> for(i in 1:nrow(VmyQuery) ) {+     print(VmyQuery[i, ])
+ }
loop through tables using r

To create an Excel workbook we will use R function createWorkbook() and function addWorksheet() to add a new worksheet to the existing workbook.

The Below R code works in the following way. First, we create a new workbook and assign it to variable wb.

wb <- createWorkbook()

Second, we initialize our for loop from 1 to the number of tables listed in our dataframe VmyQuery.

for(i in 1:nrow(VmyQuery) ) {

Third, we use addWorksheet() R function to add a new worksheet for each table.

addWorksheet(wb, sheetName = VmyQuery[i, ])

Last, we query the table in MSSQL and save the data to the newly created worksheet.

myQ <- paste0("select top 50 * from Sales.",VmyQuery[i, ])
VmyQR <- sqlQuery(channel = sqlcnt, query = myQ, stringsAsFactors = F,as.is = T)
writeData(wb, VmyQuery[i, ], VmyQR)

The entire loop code is the following:

> ## Create our new workbook
> wb <- createWorkbook()
> ## Create a loop to add worksheet and populate our workbook for each table listed into our dataframe VmyQuery
> for(i in 1:nrow(VmyQuery) ) {
+       addWorksheet(wb, sheetName = VmyQuery[i, ])
+       myQ <- paste0("select top 50 * from Sales.",VmyQuery[i, ])
+       VmyQR <- sqlQuery(channel = sqlcnt, query = myQ, stringsAsFactors = F,as.is = T)
+       writeData(wb, VmyQuery[i, ], VmyQR)
+ }
r code to create excel workbook

Step 6 – Save the data to a physical Excel file

All our data is now saved in memory inside our wb R object. To write the data to a physical file we have to issue the following command.

saveWorkbook(wb, file = paste("adwork",".xlsx", sep=""), overwrite = TRUE)
r code to save excel workbook

As we can see, the adwork.xlsx file was created with multiple worksheets and each one contains the first 50 rows for each of the tables.

excel workbook and worksheets

Step 7 – Final R code

Here is the complete set of R code.

library(plyr)library(dplyr)
library(openxlsx)
library(RODBC)
 
sqlcnt <- odbcDriverConnect('driver={SQL Server Native Client 11.0};
                                server=MySQLServer;
                                database=AdventureWorks2014;
                                rows_at_time=1024;
                                trusted_connection=yes')
 
myQuery <- "select table_name from information_schema.TABLES where TABLE_SCHEMA='Sales' and table_type='BASE TABLE'"
VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery,stringsAsFactors = F,as.is = T)
 
  ## Create new workbooks
  wb <- createWorkbook() 
 
  ## Create the worksheets
  for(i in 1:nrow(VmyQuery) ) {
        addWorksheet(wb, sheetName = VmyQuery[i, ])
        myQ <- paste0("select top 50 * from Sales.",VmyQuery[i, ])
        VmyQR <- sqlQuery(channel = sqlcnt, query = myQ, stringsAsFactors = F,as.is = T)
        writeData(wb, VmyQuery[i, ], VmyQR)
  }
  saveWorkbook(wb, file = paste("adwork",".xlsx", sep=""), overwrite = TRUE)
 

Conclusion

We have just demonstrated how easy it is to export SQL data to an Excel spreadsheet using R. Such a script can be handy to DBA or SQL Developer that needs to provide ad-hoc data to the business in Excel format.

Next Steps


Last Updated: 2020-04-14


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





Comments For This Article




Sunday, May 03, 2020 - 1:55:47 PM - Matteo Back To Top

Joost,

thanks for your feedback. It would be very nice if you can share the powershell code that place the results of each query is a separate worksheet.

Thanks


Friday, May 01, 2020 - 11:18:27 AM - Mike Back To Top

@Joost -  this solution creates a single workbook with worksheets for each table.  While that can probably be done in Powershell it's probably not a one-liner.  Tbh, at first glance I would think this is too complicated if I hadn't done something similar recently. 


Friday, May 01, 2020 - 4:40:16 AM - Joost Back To Top

Powershell is easier:

* Install SQLServer and ImportExcel modules

* Invoke-Sqlcmd -ServerInstance . -Database AdventureWorks2017 -Query "select * from production.product" | Export-Excel -AutoSize -AutoFilter -FreezeTopRow


Thursday, April 16, 2020 - 5:54:24 AM - Onur Back To Top

Sorry to say that but Import-Export Wizard is way much more easier than pulling data with R studio!



download


Recommended Reading

Bulk Insert Data into SQL Server

Simple Image Import and Export Using T-SQL for SQL Server

Using OPENROWSET to read large files into SQL Server

SQL Server Bulk Insert for Multiple CSV Files from a Single Folder

How to Copy a Table in SQL Server to Another Database





get free sql tips
agree to terms


Learn more about SQL Server tools