By: Matteo Lorini | Updated: 2020-04-14 | Comments (5) | 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.
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.
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.
Step 3 – Connect to SQL Server
Let us use R to connect to MSSQL using integrated security.
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)
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, ])
+ }
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)
+ }
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)
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.
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
- 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
Article Last Updated: 2020-04-14
