SQL Server Export/Import excel using R script

In this post, let us see new approach to import excel into SQL Server and export SQL server data to excel. There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked server & OPENROWSET query and SQLCMD.

BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly
BCP - Work around has to be done to include the header  
SSIS - With dynamic source & destination, handling mapping increases the complexity of the package
SQLCMD - Cannot export output in Excel

R & Python language extension was introduced in SQL Server 2016 & 2017 as part of machine learning. With support of R in Azure SQL database and Java language extension support in SQL Server 2019, this new approach can be used extensively as it easy, fast and flexible.


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

In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3, Part 4 & Part 5. In this post, let us see how to invoke sql files in a folder and export the output of the query as Excel files.

As a prerequisite, we need R packages "xlsx"  & "rJava", which I had showed in my previous posts on how to install and load them.

As we are going to make use of the powershell SQL Server cmdlets "Invoke-Sqlcmd", let us make sure SQL Server powershell modules are downloaded & installed from Powershell.

For this example, I have created below folder structure:






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

In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3 & Part 4. In this post, let us see how to import multiple excel files in a folder (including sub folders) into different SQL Server table created dynamically based on excel file structure.


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

In continuation to my previous posts on SQL Server Export/Import excel using R script - Part 1 ,Part 2 & Part3 where we saw about exporting to Excel and importing to SQL Server table using "xlsx" package which is a powerful package but it has external dependency on Java ("rJava" package).

In this post, let us see how to import excel with multiple sheets using "readxl" package into different SQL Server tables.


I have done below example using SQL Server 2019 on windows 10 (64 bit) and R (3.4.4).
Refer my previous post on how to install R services

I have also used Wideworldimporters sample database for data setup in this example. 
Before trying to import excel with multiple sheets using "readxl" package into SQL Server tables, I tried to re-create the Excel file that was created using script provided in part 2

I got below error :

SQL Server 2019 Installation

In this post, let us see how to install SQL Server 2019 CTP 2.2 Evaluation Edition on Windows 10 (64 bit) machine.

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

In continuation to my previous posts on SQL Server Export/Import excel using R script - Part 1 & Part 2, where we saw about exporting to Excel. In this post, let us see how to import Excel into SQL Server table using R script. 

In part 2 post, I have exported few tables under "Sales" schema from "WideWorldImporters" sample database into single excel file with six sheets: 

Sheet 1 - CustomerTransactions
Sheet 2 - InvoiceLines
Sheet 3 - Invoices
Sheet 4 - OrderLines
Sheet 5 - Orders
Sheet 6 - SpecialDeals

While importing we have two scenarios  1) Excel file structure is defined & known
                                                                 2) Excel file structure is unknown and table has to be created dynamically

Let us see example for both scenarios:


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


In continuation to my previous post on SQL Server Export/Import excel using R script - Part 1, let us see few other examples in this post.

In previous post, I had selected only first six key fields from [WideWorldImporters].[Sales].[Orders] table and passed this as inputdataset (dataframe) to write.xlsx2 function.

Instead if I do "SELECT * FROM [WideWorldImporters].[Sales].[Orders]", I got error as shown in below screenshot:

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.