Compartilhar via

Importando uma planilha Excel para um Banco de Dados SQL Server

  • Artigo

Introdução

Muitas vezes, temos de realizar integração de dados no SQL Server, com diferentes fontes de dados como arquivos .txt (texto tabulado ou com separador), .xls ou .csv (Excel).

Nem sempre há tempo hábil para criar um pacote SSIS que faça toda importação destes dados, então uma alternativa muito útil é utilizar o método OPENROWSET.

Neste artigo, vamos abordar a importação de dados de arquivos Excel (.xls e .xlsx).

Construindo o ambiente para Teste

Para que possamos reproduzir todo o processo de importação de dados de um arquivo Excel para uma tabela de um banco de dados no SQL Server precisamos:

  • Criar um arquivo Excel como modelo para esta importação;
  • Configurar o servidor Windows, instalando os componentes necessários;
  • Configurar às permissões necessárias na instância SQL para que seja possível obter estes dados.

Vamos preparar o ambiente !

Criando um Arquivo para Teste (em formato Excel)

 Para esta tarefa, vamos criar um arquivo Excel simples apenas com alguns registros que possam servir de carga para a demonstração.

Adicionamos uma linha de cabeçalho, para definir explicitamente os dados: "ID", "Item Name" e "Date Created".

Os dados são sequencias apenas para ajudar na visualização do conteúdo que está sendo manipulado.

See this Excel file in the image below (click to enlarge)

Instalando os componentes necessários no Windows Server

Para obter os dados em uma consulta dentro do SQL Server, utilizamos um Provedor de Dados OLE DB.

A maior parte dos arquivos atualmente poderão utilizar o Provedor de Dados Microsoft.ACE.OLEDB.12.0 que pode ser obtido gratuitamente através do Data Connectivity Components.

Este pacote irá disponibilizar todos os drivers ODBC e OLEDB para manipulação de dados, conforme a tabela abaixo:

 File Type (extension)  Extended Properties
 Excel 97-2003 Workbook (.xls)  Excel 8.0
 Excel 2007-2010 Workbook (.xlsx)  Excel 12.0 XML
 Excel 2007-2010 Macro-enabled workbook (.xlsm)  Excel 12.0 Macro
 Excel 2007-2010 Non-XML binary workbook (.xlsb)  Excel 12.0

Existem duas versões deste pacote: uma plataforma x86 com o nome "AccessDatabaseEngine.exe" e outra para plataforma x64 com o nome "AccessDatabaseEngine_x64.exe".

Os requisitos mínimos para esta instalação podem ser obtidas na mesma página de download do pacote.

Caso você esteja instalando o pacote x86 é preciso verificar se o seu usuário possui permissão de acesso para o diretório temporário do seu sistema operacional Windows.

Para saber qual é seu diretório temporário abra o "Painel de Controle", clique na opção "Configurações avançadas do sistema". Uma janela irá abrir, selecione a aba "Avançado" e clique no botão "Variáveis de Ambiente".

Uma nova janela irá abrir com as variáveis de ambiente, incluíndo as variáveis "TEMP" e "TMP" que indicam o seu diretório temporário.  

See this windows in the image below (click to enlarge)

Então se seu sistema operacional Windows é 32 bits (x86) então é necessário incluir o acesso de leitura e escrita para o usuário de sua instância SQL Server.

É importante lembrar que o usuário de sua instância SQL Server deve ser um usuário local ou a conta padrão "Local System" para conceder este acesso. 

See this window Service Properties in the image below

Habilitando à Instância SQL para leitura do arquivo

As configurações e permissões para executar uma consulta de dados externos possui alguns detalhes que devem ser cumpridos para que seja possível obter os dados de um arquivo Excel (.xls ou .xlsx) e também outros formatos.

A execução de consultas distribuídas, quando utilizamos OPENROWSET só é possível quando a instância do servidor SQL tem a configuração Ad Hoc Distributed Queries habilitada. Por padrão, toda instância SQL Server mantém esta permissão negada.

 Nota

As Configurações Avançadas só devem ser alteradas por um profissional experiente ou um técnico certificado em SQL Server. Então é importante salientar para não utilizar estes comandos em bancos de dados de Produção sem análise prévia. Recomendamos você executar todos os testes em um ambiente isolado, sob seu próprio risco.

Para habilitar este recurso basta utilizar a stored procedure de sistema sp_configure em sua instância SQL para exibir suas Configurações Avançadas através do parâmetro show advanced options e logo à seguir, habilitar a configuração Ad Hoc Distributed Queries permitindo o uso de consultas distribuídas.

USE [master]
GO

--CONFIGURANDO À INSTÂNCIA SQL PARA ACEITAR OPÇÕES AVANÇADAS
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

--HABILITANDO O USO DE CONSULTAS DISTRIBUÍDAS
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

 A mudança nas Configurações Avançadas só entram em vigor após a execução do comando RECONFIGURE.

Assim que temos às permissões concedidas utilizamos à stored procedure de sistema sp_MSset_oledb_prop para vincular o Provedor de Dados Microsoft.ACE.OLEDB.12.0** **no SQL Server através do parâmetro AllowInProcess para que possamos utilizar os recursos deste Provedor de Dados e também precisamos permitir o uso de parâmetros dinâmicos através de DynamicParameters para que nossas consultas possam utilizar cláusulas T-SQL.

USE [master]
GO

--ADICIONANDO OS DRIVERS NA INSTÂNCIA SQL
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

See this output SQL script in the image below

Após configurar sua instância SQL para utilizar o Provedor de Dados Microsoft.ACE.OLEDB.12.0 e efetuar às permissões de acesso adequadas, podemos executar às consultas distribuídas de outras fontes de dados, neste caso para arquivos Excel. 

Consultando e Importando à Planilha

 Como nosso foco são os arquivos Excel (.xls) vamos realizar uma consulta utilizando o método OPENROWSET com o arquivo de teste que criamos no início deste artigo. 

Utilizamos alguns parâmetros deste método para poder consultar os dados, são eles:

  • Provedor de Dados - Neste caso, utilizamos Microsoft.ACE.OLEDB.12.0
  • Opções de BULK      - Versão do arquivo; Local onde está armazenado; Cabeçalho (HDR); Modo de Importação (IMEX)
  • Consulta                     - Instrução T-SQL com ou sem cláusulas para filtrar e tratar dados

--CONSULTANDO UMA PLANILHA
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Microsoft\Test.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Plan1$]') 
GO

See this output SQL script in the image below

Para agrupar os dados e/ou realizar outras tarefas de manipulação de dados, o ideal é sempre carregar os dados dentro do banco de dados. Você pode inserir os dados em uma tabela existente através do comando INSERT ou você pode criar uma tabela através da instrução INTO no comando SELECT.

--CONSULTANDO UMA PLANILHA
SELECT * 
INTO TB_EXAMPLE
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Microsoft\Test.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Plan1$]') 
GO

SELECT * FROM TB_EXAMPLE 
GO

****See this output SQL script in the image below
****

É importante também verificar se o usuário do servidor SQL possui acesso ao diretório do Windows onde está sua planilha. 

Conclusão

Possuir um recurso alternativo e rápido para importação de dados, através de T-SQL é extremamente útil, principalmente quando temos de manipular arquivos em formatos proprietários, como no caso dos arquivos .xlsx onde é necessário utilizar o Provedor de Dados adequado para que obter os dados corretamente e com facilidade de uso.

É importante estar atento para que apenas os usuários que realmente possuem necessidade de manipular estes arquivos possam utilizar estes recursos, reduzindo ao máximo a vulnerabilidade de seu ambiente através de uma permissão no seu servidor SQL Server.

Referências

Veja Também

Outros Idiomas