R stored procedure to load Excel files, by Matteo Lorini

R_myReadXcelProc.sql

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
/*
Based on sample by Matteo Lorini:
https://www.mssqltips.com/sqlservertip/6622/stored-procedure-in-sql-server-with-r-code/

The readxl package needs to be installed first:
https://www.mssqltips.com/sqlservertip/4982/installing-external-r-packages-to-use-with-sql-server-2017/
*/
CREATE PROCEDURE [dbo].[R_myReadXcelProc]
  @parallel_outer bit = 0,
  @srcFileName_outer varchar(max),
  @xlsxRows_outer float output
AS
  BEGIN TRY
    exec sp_execute_external_script
     @language = N'R',
      @script = N'
       myReadXcelFunc <- function (srcFileName)
       {
           library(readxl)
           myFile <- read_xlsx(srcFileName)
           numRows <- nrow(myFile)
           myFileDF <- data.frame(myFile)
           retList <- list(xlsxFile = myFileDF, xlsxRows = numRows)
           return(retList)
       }
       result <- myReadXcelFunc(srcFileName = srcFileName)
       if (is.list(result)) {
         OutputDataSet <- result$xlsxFile
         xlsxRows <- result$xlsxRows
       } else stop("the R function must return a list")
     ',
      @parallel = @parallel_outer,
      @params = N'@srcFileName varchar(max), @xlsxRows float output',
      @srcFileName = @srcFileName_outer,
      @xlsxRows = @xlsxRows_outer output
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH;
GO