SQL Server - Importando e Exportando dados de planilhas do Excel

Post Views 9,990 views

Neste artigo

Reading time 5 minutes

Olá pessoal,
Boa noite!

Nesse post, vou falar sobre um recurso muito interessante do SQL Server e que funciona como um grande diferencial do banco de dados, que é a capacidade de integrar nativamente, com o Excel, permitindo consultar e manipular planilhas através do banco de dados, sem precisar de nenhum outro recurso externo.

Para que isso seja possível, você precisa instalar os drivers OLEDB ACE ou JET para realizar a integração com o Excel. Para saber mais sobre isso, acesse o post SQL Server – Como instalar os drivers Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0.

Planilha de exemplo que será usada neste post:

Importando dados do Excel para o Banco

Como Importar uma planilha do Excel para o SQL Server

A forma mais utilizada com certeza é a leitura de dados de uma planilha do Excel para o banco de dados SQL Server. Para isso, vamos utilizar o OPENROWSET e o driver ACE OLEDB 12.0, uma vez que meu sistema operacional e o meu banco estão na versão 64 bits e utilizando uma planilha do Office 2016 (O plugin JET suporta até a versão 2003).

O comando básico para a leitura é assim:

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

SELECT *

FROM OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Teste.xlsx;',

'SELECT * FROM [Aba1$]'

)

Exemplo:

Uma outra forma, que deixa essa integração bem mais fácil, é criar uma Stored Procedure para facilitar a utilização dos dados:

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

CREATE PROCEDURE [dbo].[stpImporta_Excel](

@Caminho VARCHAR(5000),

@Aba VARCHAR(200),

@Colunas VARCHAR(5000)

)

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

E aí você pode utilizar assim:

Você pode retornar apenas colunas específicas também:

Ou importar o resultado para uma tabela do banco de dados:

Exportando dados do banco para o Excel

Como inserir/exportar dados do SQL Server para o Excel

De uma forma muito simples, também é possível inserir dados do nosso banco de dados para uma planilha do Excel. A sintaxe básica é a seguinte:

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

INSERT INTO

OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Teste.xlsx;',

'SELECT * FROM [Aba1$]'

)

SELECT 'Paulo', 32, 2584.44

Exemplo da inserção:

Exemplo da consulta comprovando que o registro foi inserido:

Inserindo a partir de uma tabela:

Mais uma vez, podemos utilizar uma Stored Procedure para facilitar as operações durante o dia a dia:

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

CREATE PROCEDURE [dbo].[stpInsere_em_Excel](

@Caminho VARCHAR(MAX),

@Aba varchar(200),

@Tabela varchar(200),

@Colunas varchar(MAX)

)

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

Exemplo de utilização da procedure:

Atualizando dados do Excel pelo banco

Como atualizar dados de uma planilha do Excel pelo SQL Server

Assim como as outras operações, é possível também atualizar dados de uma planilha do Excel através do SQL Server. A sintaxe básica é essa:

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'

UPDATE A

SET A.Name = 'Teste 1'

FROM OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Teste.xlsx;',

'SELECT * FROM [Aba1$]'

) A

WHERE A.Name = 'Paulo'

Exemplo:

Exemplo com JOIN utilizando tabelas locais do banco de dados:

Stored Procedure para facilitar o dia a dia:

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

CREATE PROCEDURE [dbo].[stpAtualiza_em_Excel](

@Caminho varchar(max),

@Aba varchar(200),

@Tabela varchar(200),

@Colunas_Join varchar(max),

@Colunas_Update varchar(max)

)

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

Função fncQuebra_Texto necessária para utilizar a 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
);

CREATE FUNCTION [dbo].[fncQuebra_Texto] (

@str NVARCHAR(4000) ,

@separator CHAR(1)

)

RETURNS TABLE

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

);

Utilização da SP:

Infelizmente, apagar dados de planilhas do Excel pelo SQL Server não é possível. Caso você tente, encontrará essa mensagem de erro:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Deleting data in a linked table is not supported by this ISAM.”.
Msg 7345, Level 16, State 1, Line 1

And that's it, folks!
Um abraço e obrigado pela visita.

sql server importar ler exportar atualizar dados de uma planilha do excel para o banco import read export data spreadsheet datasheet

Adriano disse:

April 5, 2021 às 19:27

Boa Noite! Tenho uma base no Excel e trouxe ela para o SQL, porém como faço pra atualizar a tabela toda no SQL? Exemplo acrescentei mais uma linha no excel com mais informações e agora quero que traga no SQL.

Responder
Danilo disse:

February 12, 2021 às 14:45

TOP!!…Vou procurar aqui como fazer isso com arquivos CSV,
Obrigadi

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Importando e Exportando dados de planilhas do Excel

Como Importar uma planilha do Excel para o SQL Server

Como inserir/exportar dados do SQL Server para o Excel

Como atualizar dados de uma planilha do Excel pelo SQL Server

sql server importar ler exportar atualizar dados de uma planilha do excel para o banco import read export data spreadsheet datasheet

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

Como Importar uma planilha do Excel para o SQL Server

Como inserir/exportar dados do SQL Server para o Excel

Como atualizar dados de uma planilha do Excel pelo SQL Server

sql server importar ler exportar atualizar dados de uma planilha do excel para o banco import read export data spreadsheet datasheet

You may also like...

SQL Server – Como capturar dados de uma página Web (Webscraping com HttpRequest) e inserir os dados no banco com SQLCLR (C#)

SQL Server – Evitando consultas em determinadas colunas com o Column Level Security (CLS)

SQL Server - How to generate a script with all database indexes

2 Responses

Deixe uma respostaCancelar resposta