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:
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
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:
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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 |
Exemplo de utilização da procedure:
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:
|
1 2 3 4 5 6 7 8 |
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' |
Exemplo:
Exemplo com JOIN utilizando tabelas locais do banco de dados:
Stored Procedure para facilitar o dia a dia:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 |
Função fncQuebra_Texto necessária para utilizar a SP:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 ); |
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
É isso aí, pessoal!
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
sql server importar ler exportar atualizar dados de uma planilha do excel para o banco import read export data spreadsheet datasheet
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.
TOP!!…Vou procurar aqui como fazer isso com arquivos CSV,
Obrigadi