More Fun Exporting Data with R

Up until now, we have reviewed getting data out of SQL Server and into Excel. We’ve done it via several means:

How to Export data to Excel

Have Fun Gathering Data to Excel Using More Tools

Even More Fun Exporting Data With PowerShell

In today’s post, we’ll go over how to export the data you have in SQL Server to Excel via one of my favorite computer languages – R. (Since we did have a post on how to Import data, it would seem rather rude not to have one on how to Export data.)

As always, you’ll need to open your R tool of choice. I tend to use RStudio but there are several out there that will accomplish this same goal.

First, we’ll need to load 2 libraries that will make our project fairly easy.

library(RODBC)
library(writexl)

Next, we connect to our database via the RODBC library:

sqlConnect <- odbcDriverConnect('driver={SQL Server Native Client 11.0};
                                    server=.;
                                    database=RTest;
                                    rows_at_time=1024;
                                    trusted_connection=yes')

We read the data that we want to get into the spreadsheet:

output <- sqlQuery(sqlConnect, "select * from dbo.MockPersonData")

Then, we simply write to a spreadsheet:

write_xlsx(output, path = "c:\\temp\\Writing_MockPersonData.xlsx")

And that, my friends, is truly that.

I like keeping it simple and this is one easy way to get data into a spreadsheet.

With that, I come to the end of the “Exporting Data from SQL Server to Excel” series. I hope you’ve enjoyed it and have learned a few things along the way.