Problema: A menudo hay necesidad de exportar datos de SQL Server a una hoja de cálculo de Excel.

Solución: En esta publicación del blog, exploraremos cómo utilizar T-SQL y R para generar un archivo de Excel basado en los resultados de una consulta.

La solución que proponemos es utilizar el procedimiento almacenado sp_execute_external_script para crear un script R simple que permita a un DBA o desarrollador exportar rápidamente y automáticamente los resultados de cualquier consulta a una hoja de cálculo de Excel. Este enfoque proporciona una forma conveniente de proporcionar datos a los usuarios de negocios en formato Excel.

Primero, necesitamos descargar y restaurar la base de datos AdventureWorks2014 en nuestro SQL Server. Esta base de datos se utilizará para nuestros ejemplos.

A continuación, escribiremos el código R que creará el archivo de Excel. Utilizaremos los paquetes openxlsx y dplyr para manipular la salida. El código creará un libro de Excel, agregará una hoja de trabajo y escribirá los resultados de la consulta en la hoja de trabajo. El archivo de Excel se guardará con un nombre y ubicación estáticos.

DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
    OutputDataSet <- SqlData;
    packages <- c("openxlsx", "dplyr")
    if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
          install.packages(setdiff(packages, rownames(installed.packages())))  
    }
   library(openxlsx)
   library(dplyr)
   
   wb <- createWorkbook()
   addWorksheet(wb, sheetName = "miTabla")
   writeData(wb, "miTabla", OutputDataSet)
 
   saveWorkbook(wb, file = paste("D:\\test\\miTabla",".xlsx", sep=""), overwrite = TRUE)
';

Ahora, necesitamos escribir el script T-SQL que devolverá los datos que queremos exportar. El script puede ser cualquier script T-SQL válido que devuelva datos. En este ejemplo, seleccionamos las columnas FirstName, LastName y SalesYTD de la tabla Sales.vSalesPerson, filtrando los resultados para incluir solo las filas donde SalesYTD es mayor que 2,000,000 y ordenando los resultados por SalesYTD en orden descendente.

DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
    SELECT FirstName, LastName, SalesYTD
    FROM Sales.vSalesPerson
    WHERE SalesYTD > 2000000
    ORDER BY SalesYTD DESC;';

Finalmente, podemos ejecutar el procedimiento almacenado sp_execute_external_script para ejecutar nuestro código R y pasar el script T-SQL como datos de entrada. Esto generará el archivo de Excel con los resultados de la consulta.

EXEC sp_execute_external_script
    @language = N'R',
    @script = @rscript,
    @input_data_1 = @sqlscript,
    @input_data_1_name = N'SqlData';

Al ejecutar el código anterior, obtendremos el archivo de Excel deseado que contiene los datos producidos por la consulta en SQL Server Management Studio (SSMS).

Para hacer nuestro script más versátil, podemos agregar un parámetro llamado @mytname para especificar el nombre del archivo que queremos crear. Esto nos permite generar dinámicamente el nombre del archivo y el nombre de la hoja de Excel.

DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
    OutputDataSet <- SqlData;
    packages <- c("openxlsx", "dplyr")
    if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
          install.packages(setdiff(packages, rownames(installed.packages())))  
    }
   library(openxlsx)
   library(dplyr)
   
   wb <- createWorkbook()
   addWorksheet(wb, sheetName = mytname)
   writeData(wb, mytname, OutputDataSet)
 
   saveWorkbook(wb, file = paste(paste("D:\\test\\",mytname),".xlsx", sep=""), overwrite = TRUE)
';

Luego, podemos modificar el script T-SQL para incluir el parámetro @mytname y proporcionar un valor para él.

EXEC sp_execute_external_script
    @language = N'R',
    @script = @rscript,
    @input_data_1 = @sqlscript,
    @input_data_1_name = N'SqlData',
    @params = N'@mytname nvarchar(20)',
    @mytname = 'vSalesPerson';

Además, podemos simplificar y separar el código R guardándolo en un archivo separado. Esto hace que el script sea más manejable y legible.

Aquí está el código R guardado en un archivo llamado “TableToExcel1.r”:

packages <- c("openxlsx", "dplyr")
    if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
          install.packages(setdiff(packages, rownames(installed.packages())))  
    }
 
   library(openxlsx)
   library(dplyr)
   
   wb <- createWorkbook()
   addWorksheet(wb, sheetName = mytname)
   writeData(wb, mytname, OutputDataSet)
 
   saveWorkbook(wb, file = paste(paste("D:\\test\\",mytname),".xlsx", sep=""), overwrite = TRUE)

Luego, podemos modificar el script T-SQL para usar la función source() para cargar el código R desde el archivo.

DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
    OutputDataSet <- SqlData;
    source("D:\\test\\TableToExcel1.r")
';

Siguiendo estos pasos, hemos demostrado lo fácil que es exportar datos de SQL Server a una hoja de cálculo de Excel utilizando T-SQL y R. Este enfoque puede ser útil para DBAs o desarrolladores que necesitan proporcionar datos ad hoc a los negocios en formato Excel.