Como importar dados a partir de um range de células de uma planilha excel ?

Boa Tarde Pessoal,

Essas últimas semanas eu fiquei bem atarefado por contas de alguns incidentes em produção no trabalho que renderam algumas madrugadas. Aliado a isso passei as duas últimas semanas ministrando cursos oficiais na área de BI no SQL Server 2008. Estou correndo para tirar o atraso e para não perder o hábito de postar vou deixar aqui uma dica rápida relacionada ao Integration Services. Um dia desses me deparei no fórum com um dúvida bem interessante. Referia-se a como fazer uma importação de dados a partir de um range de células do Excel. O Excel é sem dúvida um fonte de dados muito popular e não raras às vezes os projetos com o Integration Services trabalham com ele. É bem simples importar dados de uma planilha inteira, mas o que fazer quando apenas uma parte dos dados deve ser realmente importada ? Seria fácil fazer a importação completa para uma área temporária e posteriormente efetuar o descarte dos dados ou até utilizar uma transformação do tipo SPLIT, mas em ambos os casos a leitura da planilha seria completa o que me leva a crer que essas não seriam alternativas muito performáticas. Haveria uma forma de importar somente os dados necessários diretamente da origem ? Vejamos como fazê-lo.

Uma planilha de exemplo

Para demonstrar como funciona a importação, utilizei uma planilha com alguns exemplos de produtos ofertados na internet. A planilha é relativamente simples tendo apenas o nome do produto, o preço e o nome do ofertante conforme o esboço abaixo:

A construção do pacote

A montagem do pacote não tem mistério. Estou considerando que o arquivo XLSX está localizado em C:\Lista.xlsx. Será necessário um Data Flow com o conector Excel Source. Configurei as seguintes propriedades para o conector com o Excel.

Aba Propriedades
Connection Manager

OLE DB Connection Manager: (Clicar em New)
    Excel File Path (Browse): C:\Lista.xlsx
    Excel version: Microsoft Excel 2007
    First row has column names (marcado)

Data access mode: Table or view

Name of the Excel sheet: Produtos$

Columns External Column – Output Column
    Produto – Produto
    Preço – Preço
    Ofertante – Ofertante

Para conferir se a conexão com o Excel foi realizada corretamente e aproveitar para dar uma visualização nos dados, basta apenas clicar sobre o botão Preview na aba connection manager conforme a figura abaixo:

Da forma como foi montado o conector com o Excel, todos os dados da planilha "Produto" serão importados. Essa é a forma padrão, mas é contrário ao proposto pelo artigo. Supondo que apenas os produtos ofertados pelas lojas americanas sejam desejados, ou seja, o intervalo de células A5 até C7, será necessário fazer algumas alterações no connection manager e no Excel Source conforme os passos abaixo:

  • Navegue até o painel central inferior
  • Clique sobre o connection manager com o Excel (possivelmente chamado de Excel Connection Manager)
  • Desmarque a opção First row has columns names
  • Clique sobre o conector com o Excel
  • Ao aparecer uma janela (Editing Component) aceite a correção de erros clicando em Yes
  • Na aba Connection Manager mude a propriedade Data Access Mode para SQL Command
  • Na propriedade SQL command text preencha SELECT * FROM [Produtos$A5:C7]
  • Clique em Preview para visualizar os dados

Os dados mostram que as colunas Produto, Preço e Ofertante foram renomeadas para F1, F2 e F3 respectivamente como consequência das alterações no Connection Manager. Os dados contemplados na visualização são apenas os referentes às lojas americanas por conta do range de células (A5:C7).

A conexão com os dados considerando as células desejadas foi efetuada com êxito. O próximo passo é a configuração das colunas para que alguma transformação ou destino possa consumir esses dados.

  • Clique sobre Columns
  • Em Output Column renomeie F1 para Produto
  • Em Output Column renomeie F2 para Preco
  • Em Output Column renomeie F3 para Ofertante

Com a fonte de dados devidamente configurada, é possível escolher qualquer conector de destino para carregar os dados. Para esse exemplo optei pelo Flat File Destination.

As propriedades do conector Raw Destination devem ser configuradas conforme a tabela abaixo:

Aba Propriedades
Connection Manager Flat File connection manager: (Clicar em New)
    Flat File Format: Delimited
    Connection Manager name: Lista
    General
        File name: C:\Lista.txt (Aceitar todos os padrões)
    Columns (Aceitar todos os padrões)
    Advanced
        Produto (Mudar a propriedade ColumnDelimiter para Tab {t})
        Preço (Mudar a propriedade ColumnDelimiter para Tab {t})
Mappings Input Column – Output Column
    Produto – Produto
    Preço – Preço
    Ofertante – Ofertante

Com as configurações de todos os objetos efetuadas corretamente só é necessário executar o pacote e conferir o layout do arquivo. Somente as células desejadas foram contempladas.

A abertura do arquivo C:\Lista.txt mostra que apenas os produtos e preços das lojas americanas foram exportados. Com a correta configuração do conector ao Excel, esses dados poderiam ser exportados para outras fontes como o SQL Server, Oracle, etc. O grande detalhe para filtrar as células está na consulta via comando à planilha desejada. Aos que não conseguiram reproduzir o exemplo e (ou) desejem o projeto SSIS utilizado, disponibilizei o mesmo no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20100328%5E_ExcelImportCells.zip

[ ]s,

Gustavo

2 Respostas para “Como importar dados a partir de um range de células de uma planilha excel ?

  1. Como sempre ótimo post Gustavo. Gostaria de aproveitar e sugerir que posta-se como fazer importação de fontes como oracle para o SQL Server através do SSIS e também os antigos arquivos DBF que até hoje não encontrei uma forma simples de fazer para o SQL Server 2008.Um forte abraçoClayton Santos

  2. Oi Clayton,Vou anotar aqui sua sugestão. Até que com o Oracle eu não tive muitos problemas, mas DBF é realmente um desafio.[ ]s,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s