SQL Server Export/Import excel using R script - Part 1

In this post, let us see how we can easily import or export to excel with just few lines of r script executed from SSMS using sp_execute_external_script.

I have done below example using SQL Server 2016 on windows 10 (64 bit) and R (3.2.2).

Refer my previous post on how to install R services. 

Also I have used "WideWorldImporters" sample database for this example.

Now let us jump on to the prerequisite steps for this approach. Though prerequisite steps are bit heavy, it is one time and thereafter enable us to export / import to excel easily with just few lines of code.

Prerequisite Step 1: Check the version of R by running sessionInfo() from R.exe

In my case, I have installed SQL Server 2016 so R.exe will be found in below path:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin

Prerequisite Step 2: From above step I got to know that it is 64 bit, So I have downloaded Java JDK for 64 bit (.exe file) from here & installed it.

C:\Program Files\Java\jdk-11.0.

Prerequisite Step 3: Now install rJava package from R.exe

install.packages("rJava")

Prerequisite Step 4: Now let us set the JAVA_HOME environment to Java JDK installed in Step 2 and load the rJava package to check if it working from R.exe.

Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/")

library(rJava)

Prerequisite Step 5: Now it's time to install & load the xlsx package from R.exe.

·         xlsx package is one of the powerful R packages to read, write and format Excel files.

·         It is a java-based solution and it is available for Windows, Mac and Linux.

·         It works for both Excel 2007 and Excel 97/2000/XP/2003 file formats (xls and xlsx file formats).

install.packages("xlsx")

library("xlsx")

Prerequisite Step 6: Copy & paste the packages downloaded from above steps into default R library  to SQL Server R_services library.

I have copied xlsx,xlsxjars & rJava folders from C:\Users\Sathy\Documents\R\win-library\3.2

to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library

As a Pre-requisite, right-click on the folder where Excel files will be imported / exported and provide folder access (read, write) to "ALL_APPLICATION_PACKAGES".

Now as part of last prerequisite step, from SSMS let us check the installed R packages. To execute R script using sp_execute_external script, refer to my previous post link provided in the beginning of this post.

sp_execute_external_script @language = N'R', @script = N'OutputDataSet <- data.frame(installed.packages())'

Now the script to export SQL Server table data into Excel:

Below script is pretty much simple and I have selected only first six key fields

from [WideWorldImporters].[Sales].[Orders] table and passed this as inputdataset (dataframe) to write.xlsx2 function.

USE [WideWorldImporters]

DECLARE @SQL NVARCHAR(MAX) = N'
SELECT [OrderID]
      ,[CustomerID]
      ,[SalespersonPersonID]
      ,[PickedByPersonID]
      ,[ContactPersonID]
      ,[BackorderOrderID]
  FROM [WideWorldImporters].[Sales].[Orders];'

DECLARE @RScript NVARCHAR(MAX) =N'
 Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/")
    library(rJava);
    library("xlsx");
    write.xlsx2(InputDataSet, file = "H:/Temp/Blog/ImportExportExcel/Sales.xlsx", sheetName="Order",
           col.names=TRUE,  append=FALSE) '

EXEC   sp_execute_external_script
      @language = N'R'
     ,@script = @RScript
     ,@input_data_1 = @SQL

73,595 records got exported in ~10 seconds. Note I have used "write.xlsx2" in above R script as it is faster than "write.xlsx".

More information on this topic, to be continued in part 2 ...

See Also: 

• SQL Server Export/Import excel using R script - Part 2