SQL Server - Importando y Exportando Datos de Hojas de Cálculo de Excel

Cómo importar una hoja de cálculo de Excel a SQL Server

Sin duda, la forma más utilizada es leer datos de una hoja de cálculo de Excel en la base de datos de SQL Server. Para ello usaremos OPENROWSET y el controlador ACE OLEDB 12.0, ya que mi sistema operativo y mi banco están en la versión de 64 bits y usando una hoja de cálculo de Office 2016 (El complemento JET admite hasta la versión 2003).

El comando básico para leer es el siguiente:

SELECT * 
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
)

Ejemplo:

Otra forma, que facilita mucho esta integración, es crear un Procedimiento Almacenado para facilitar el uso de los datos:

CREATE PROCEDURE [dbo].[stpImporta_Excel](
    @Caminho VARCHAR(5000), 
    @Aba VARCHAR(200), 
    @Colunas VARCHAR(5000)
)
AS
BEGIN

    DECLARE @Exec VARCHAR(MAX)

    SET @Exec = 'SELECT * from OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''SELECT '
        + @Colunas
        + ' FROM ['
        + @Aba
        + '$]'') A'

    EXEC(@Exec)
 
END

Y luego puedes usarlo así:

También puede devolver solo columnas específicas:

O importe el resultado a una tabla de base de datos:

Cómo insertar/exportar datos de SQL Server a Excel

De una forma muy sencilla también es posible insertar datos de nuestra base de datos en una hoja de cálculo de Excel. La sintaxis básica es la siguiente:

INSERT INTO 
OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
)
SELECT 'Paulo', 32, 2584.44

Ejemplo de inserción:

Ejemplo de consulta que demuestra que el registro fue insertado:

Insertando desde una tabla:

Una vez más, podemos utilizar un Procedimiento Almacenado para facilitar las operaciones del día a día:

CREATE PROCEDURE [dbo].[stpInsere_em_Excel](
    @Caminho VARCHAR(MAX), 
    @Aba varchar(200), 
    @Tabela varchar(200), 
    @Colunas varchar(MAX)
)
AS
BEGIN

    IF (@Colunas = '*')
    BEGIN
    
        SELECT 
            @Colunas = isnull(nullif(@Colunas,'*') + ',','') + b.name
        FROM 
            sysobjects a WITH(NOLOCK)
            JOIN syscolumns b WITH(NOLOCK) ON a.id = b.id
        WHERE 
            a.xtype = 'U'
            AND a.name = @Tabela

    END		
    

    DECLARE @Exec VARCHAR(MAX)

    SET @Exec = 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''SELECT '
        + @Colunas
        + ' FROM ['
        + @Aba
        + '$]'') '
        + 'SELECT '
        + @Colunas
        + ' FROM '
        + @Tabela

    EXEC(@Exec)
 
END

Ejemplo de uso del procedimiento:

Cómo actualizar datos de una hoja de cálculo de Excel usando SQL Server

Al igual que otras operaciones, también es posible actualizar datos de una hoja de cálculo de Excel a través de SQL Server. La sintaxis básica es esta:

UPDATE A
SET A.Nome = 'Teste 1'
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
) A
WHERE A.Nome = 'Paulo'

Ejemplo:

Ejemplo con JOIN usando tablas de bases de datos locales:

Procedimiento almacenado para facilitar la vida cotidiana:

CREATE PROCEDURE [dbo].[stpAtualiza_em_Excel](
    @Caminho varchar(max), 
    @Aba varchar(200), 
    @Tabela varchar(200), 
    @Colunas_Join varchar(max), 
    @Colunas_Update varchar(max)
)
AS
BEGIN
        
    DECLARE 
        @join VARCHAR(MAX) ,
        @update VARCHAR(MAX);


    SELECT  
        @join = ISNULL(@join + ' and ', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))
    FROM    
        dbo.fncQuebra_Texto(@Colunas_Join, ',') AS a;
    

    SELECT  
        @update = ISNULL(@update + ',', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))
    FROM    
        dbo.fncQuebra_Texto(@Colunas_Update, ',') AS a;
    
    
    DECLARE @Exec VARCHAR(MAX)
    
    SET @Exec = 'UPDATE A '
        + 'SET '
        + @update
        + ' FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''Select * From ['
        + @Aba
        + '$]'') A'
        + ' JOIN '
        + @Tabela
        + ' b'
        + ' ON '
        + @join

    EXEC(@Exec)
 
END

Función fncQuebra_Texto requerida para usar SP:

CREATE FUNCTION [dbo].[fncQuebra_Texto] (
    @str NVARCHAR(4000) ,
    @separator CHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH tokens ( p, a, b )
    AS ( 
        SELECT   1, 1, CHARINDEX(@separator, @str)
        UNION ALL
        SELECT   p + 1, b + 1, CHARINDEX(@separator, @str, b + 1)
        FROM     tokens
        WHERE    b > 0
    )
    SELECT  
        p - 1 zeroBasedOccurance ,
        SUBSTRING(@str, a, CASE WHEN b > 0 THEN b - a ELSE 4000 END) AS s
    FROM    
        tokens
);

Uso de SP: