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:




Scenario 1: Excel file structure is defined & known

For this scenario, let us try to import sheet 1 by setting the argument sheetName = 1 in read.xlsx2 function. As we know the file structure, table has been created beforehand.

Please note: In the below script, we need to configure the values, pass folder path details for read.xlsx2 function




SET NOCOUNT ON
DROP TABLE IF EXISTS CustomerTransactions_Import

--As we know the Excel file structure beforehand, creating an empty table to import the data from Excel sheet
SELECT NULL AS RowId,* 
INTO CustomerTransactions_Import 
FROM [Sales].[CustomerTransactions]
WHERE 1 = 2

DECLARE @RScript NVARCHAR(MAX) =N' 
Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/")
    require(rJava);
require("xlsx");
    OutputDataSet <- read.xlsx2("H:/Temp/Blog/ImportExportExcel/Sales.xlsx", sheetName = 1,header=TRUE)
'
--Inserting from Excel
INSERT dbo.CustomerTransactions_Import
EXEC   sp_execute_external_script
      @language = N'R'
     ,@script = @RScript

--SELECT * FROM CustomerTransactions_Import

--Below query compares the data in source table and data imported from Excel
SELECT Src.CustomerTransactionID,Imp.CustomerTransactionID 
FROM [Sales].[CustomerTransactions] Src
LEFT JOIN CustomerTransactions_Import Imp
ON Src.CustomerTransactionID = Imp.CustomerTransactionID

Scenario 2: Excel file structure is unknown and table has to be created dynamically

For this scenario, let us try to import sheet 2 by setting the argument sheetName = 2 in read.xlsx2 function. As we don't know the file structure, table will be built dynamically using RevoScaleR function - rxDataStep and imported with data from read.xlsx2 function.

Please note: In the below script, we need to configure the values for 
sqlConnString variable with database connection details

Pass folder path details for read.xlsx2 function

DECLARE @RScript NVARCHAR(MAX) =N' 
Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/")
    require(rJava);
require("xlsx");
    Output <- read.xlsx2("H:/Temp/Blog/ImportExportExcel/Sales.xlsx", sheetName = 2,header=TRUE)

sqlConnString <- "Driver=SQL Server;Server=''LAPTOP-LCUB6HBB''; Database=WideWorldImporters;Uid=sa;Pwd=***"
    sqlTable <- "InvoiceLines_Import"
if (rxSqlServerTableExists(sqlTable,  connectionString = sqlConnString))  rxSqlServerDropTable(sqlTable,  connectionString = sqlConnString)
    sqlDS <- RxSqlServerData(connectionString = sqlConnString,table = sqlTable)
    rxDataStep(inData = Output, outFile = sqlDS,overwrite = TRUE)'

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

sqlConnString variable needs to be set with ServerName, DatabaseName, Username & Password for ODBC SQL Server connection.
sqlTable variable is set with the name of table to be created.


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

See Also:

• SQL Server Export/Import excel using R script - Part 1
• SQL Server Export/Import excel using R script - Part 2
• My post on Technet Wiki - SQL Server Import/Export to Excel using R script