Criando tabela no SQL Server a partir de uma tabela do Excel apenas com Select

     Olá pessoal, novamente vamos para um artigo que pode ser útil para quem precisa trabalhar com tratamento de dados.

     Dessa vez, minha intenção é a partir de uma tabela de Excel (2007), criar uma tabela do SQL Server (2008) sem ter que usar o comando <CREATE TABLE>, mas sim usando o comando <SELECT> com um pequeno complemento, o <INTO> <TABELA A SER CRIADA>.

tabela do Excel que usarei como exemplo.

     Seguinte, podemos através do SQL Server fazer um <SELECT> direto de uma tabela de Excel, seja através de um  LINKED SERVER, seja com um <OPENROWSET>.

Neste caso, pretendo usar o <OPENROWSET>, ele inclui todas as informações sobre a conexão exigida para acessar dados remotos através de uma fonte de dados OLE DB. É um método alternativo para acessar tabelas em um servidor vinculado.

     É um método AD HOC para conexão e acesso a dados remotos por meio de OLE DB. Uma conexão AD HOC é feita para fins de uma única consulta e é conhecida como uma consulta distribuída.

Uma consulta AD HOC deve ser usada apenas para fazer referência a fontes de dados OLE DB que são acessadas com pouca frequência. Para qualquer fonte de dados que será acessada com muita frequência, deve ser defino um servidor vinculado, LINKED SERVER.

     Por padrão, o SQL Server não permite consultas distribuídas AD HOC (Ad Hoc Distributed Queries). Quando esta opção é setada com o valor 1, o SQL Server permite acesso AD HOC. Quando esta opção esta setada com o valor 0, o SQL Server não permite o acesso, devemos observar essas questões.

     Antes de setar as consultas distribuídas do AD HOC, devemos habilitar as opções avançadas do SP_configure para que possamos ter exito na consulta com o <OPENROWSET>, pois o SP_configure  vem por padrão setado com valor 0 para 'show advanced options'. 

A forma de ativá-lo seria a seguinte:

exec sp_configure 'show advanced options', 1

reconfigure

Agora vamos às consultas distribuídas AD HOC.

Exemplo de setar o valor do AD HOC:

exec sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

     Outra coisa, deve ser observado se em seu Windows existe o driver atualizado do Microsoft Access Database Engine, ele tem um conjunto de componentes que facilitam a transferência de dados entre arquivos existentes do Microsoft Office, como o Microsoft Office Access (*. Mdb e *. Accdb), arquivos e Microsoft Office Excel (*. Xls, *. Xlsx e * Os. xlsb) e arquivos para outras fontes de dados como o Microsoft SQL Server. Se o download do driver for o da versão mais atual, 2010, ele serve para versões anteriores também, como 2007, 2003, 2000 e 97.

     Para poder baixa-lo, vá ao download Center da Microsoft e procure por, Microsoft Access Database Engine. Depois de instalado, as seguintes opções do mesmo devem estar habilitadas:

AllowInProcess deve ser setado para 1 e DynamicParameters para 1 também, elas vem setadas com 0 por default. 

Exemplo:

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

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

Depois de todas essas explicações, vamos ao <OPENROWSET>, vou colocar de uma vez o exemplo que utilizei e abaixo quais opções coloquei no mesmo.

Exemplo:

SELECT * FROM OPENROWSET(

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;

HDR=YES;

Database=C:\Pasta1.xlsx',

'SELECT * FROM [Plan1$]')

SELECT * FROM OPENROWSET(

'{nome do provedor (driver)}',

'{catalogo acessado (excel 97/2000/2003/2007/2010)};

{(HDR) Habilita ou não o título das colunas do Excel};

Database={caminho completo da planilha}',

'{Select da planilha}')

*** o Caractere $ serve para identificar qual planilha está sendo usada, então não deve ser omitido esse valor do nome da mesma.

Nesse momento temos o SELECT de uma planilha de dados por meio do SQL Server através do <OPENROWSET>.

     Agora vem a parte fácil, criar uma tabela por meio do SELECT, depois de todas essas explicações vou usar um complemento dentro da query que fizemos, <INTO [TABELA]>.

     A instrução SELECT INTO permite criar uma tabela e popular a mesma com um conjunto de resultados vindos da instrução SELECT. A instrução SELECT INTO pode ser usada para combinar dados de várias tabelas ou exibições em uma única tabela.

Exemplo:

SELECT * into tb_exc

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0 Xml;HDR=YES;Database=C:\Pasta1.xlsx',

    'SELECT * FROM [Plan1$]')

*** tb_exc é o nome que defini, pode ser difinido qualquer nome desde que respeite as normas do SQL Server para tal.

Percebam que apenas acrescentei “into tb_exc”  antes do FROM, pronto, executando a query acima criamos uma tabela com o SELECT a partir de dados do Excel.

     Mais algumas informações, notaram que não defini a estrutura da tabela, apelas pedi ao SQL Server que criasse a tabela para mim. Vejam como ficou a estrutura da mesma.

     Como o SQL Server fez isso? Ele verifica as 8 primeira linhas, analisando a sequencia de dados de cada coluna, se os oito primeiros valores são string, então aquela coluna vai ser string, se eles são valores numéricos, essa coluna vai ser do tipo numérico, se são datas serão do tipo data.

     Mas e se tiver um valor errado dentro de uma coluna numérica? Por exemplo um nome? Ela fica nula, pois não faz parte do padrão pré-estabelecido.

     Para evitar algo assim de forma simples, use CAST para converter os dados das colunas no tipo desejado, ao invés de numérico, colocar como string e pronto.

Enfim, acho que é isso, até a próxima.

Referências

<OPENROWSET>: http://msdn.microsoft.com/pt-br/library/ms190312.aspx

AD HOC: http://msdn.microsoft.com/pt-br/library/ms187569.aspx

AD HOC: http://support.microsoft.com/kb/306397/pt-br

LINKED SERVER: http://msdn.microsoft.com/en-us/library/ms188279.aspx

Microsoft Access Database Engine : http://www.microsoft.com/en-us/download/details.aspx?id=13255

Opções avançadas do Servidor SQL Server (sp_configure): http://msdn.microsoft.com/pt-br/library/ms189631.aspx

Criação dos tipos de dados da tabela a partir do Excel: http://support.microsoft.com/kb/257819