Coletando dados de uma planilha do Excel.

Descrição:

Em muitos casos, é necessário coletar dados de uma planilha do Excel. Geralmente, isto é feito quando os dados não vêm de um sistema automatizado, e então eles são inseridos manualmente na planilha (obviamente, não exclui-se a possibilidade de que o arquivo também seja gerado por um sistema). Em um destes casos, as análises manuais são feitas em laboratório, e após interpretados os dados, os resultados são inseridos em uma planilha.

Neste, como posso coletar estes dados para armazenamento no EPM?


Solução:

O primeiro passo é instalar um OLEDB Provider na máquina onde está instalado o Interface Server que fará a coleta do arquivo Excel. Esse componente é o que permite que o arquivo seja visto como um banco de dados, e possibilite a coleta dos dados através de códigos SQL.

Basicamente, a versão do OLEDB Provider a ser instalada, também conhecido como ACE (Access Database Engine), dependerá da versão do arquivo do Excel ou do Office presente na máquina (por exemplo Office 2007, Office 2013). De um modo geral, pode-se usar os seguintes links:

(Office2007) 2007 Office System Driver: Data Connectivity
Components

https://www.microsoft.com/en-us/download/details.aspx?id=23734

(Office 2010 ou superior) Microsoft Access Database
Engine 2010 Redistributable

https://www.microsoft.com/en-us/download/details.aspx?id=13255

NOTA: Um restart na máquina pode não ser solicitado, mas é desejado.

Para esse artigo, foi criada uma planilha de exemplo, onde existem as colunas DataHora, PressaoAplicada_PSI, TemperaturaAtingida_Celsius e Responsavel. Nesse caso, desejamos coletar e armazenar em dois tags do EPM, a PressaoAplicada_PSI
e a TemperaturaAtingida_Celsius.

A coluna DataHora irá fornecer o Timestamp para os dois tags. Veja abaixo como ficaram dispostos os dados.

Agora vamos iniciar a configuração do Interface. Para isso, utilize o EPM Studio para criar um Interface Database dentro do nodo do Interface Server que fará a coleta dos dados. A primeira etapa será configurar a Connection String para a planilha, por exemplo.

Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=”C:\temp\ResultadosAnaliseLaboratorio.xlsx”;Extended
Properties=”Excel 12.0 Xml; HDR=Yes”;

Clique em Test Connection para se certificar de que esteja funcionando. Feche a janela e salve as modificações do Interface. O site http://www.connectionstrings.com possui uma boa documentação sobre diversas formas de conexão, além da descrição de cada um dos parâmetros.

O próximo passo será criar a consulta que vai coletar os dados. Nesse caso, poderemos fazer um único SQL que receberá todas colunas de
uma única vez. Portanto, na aba Queries, crie uma nova query e coloque o seguinte SQL:

SELECT *
FROM
[Sheet1$]
WHERE
DataHora > @DataHora.LastValue
ORDER
BY DataHora ASC

Onde:

FROM
[Sheet1$]

Nome da planilha do arquivo .xlsx

WHERE
DataHora > @DataHora.LastValue

Cláusula de controle da consulta, ou seja, a consulta
só irá coletar os dados que ainda não foram coletados (maior que o LastValue).

ORDER BY DataHora ASC

Garante que os dados retornados sempre estarão em ordem
cronológica.

Ao executar a consulta, defina o valor inicial da variável como 25/03/2017 00:00:00, para que os dados sejam coletados desde o início.

Após fechar a janela, selecione a consulta na lista de queries criadas e clique em Test, localizado na barra de ferramentas da aba Queries. Por enquanto, mantenha o checkbox da coluna Enabled, desmarcado.

Agora acesse a aba Address, e insira dois endereços novos, que serão usados para mapear as colunas da consulta em Endereços com Valor, Timestamp e Qualidade, criando um objeto válido para a importação e vínculo com os tags do EPM.

Ambos os endereços terão a coluna DataHora como Timestamp Field e o Quality Field vazio, o que significa que a qualidade atribuída aos valores coletados será sempre Good. Isso é especialmente importante nesse caso, pois não temos essa informação na planilha.

O campo Value Field de cada endereço será mapeado com suas respectivas colunas. Após isso, salve as configurações.

Criando as Basic Variables (Tags do EPM)

Pelo EPM Studio, acesse o nodo DataObjects -> Basic Variables. Em seguida, clique no botão Import da ribbon. Localize os dois endereços que estão dentro do Interface Database e importe os mesmos.

Configure o Cast Type como float e habilite o armazenamento na aba Storage de cada um dos tags.

Volte à aba do Interface Database, marque o checkbox Enabled da query criada, habilite também a opção Enable Storage do Interface e salve as configurações.
A coleta começará a ser feita e os dados armazenados nos respectivos tags. Para visualizar os dados por exemplo, basta adicionar os tags em algum chart.

Extras:
Abaixo, outros exemplos de consultas no Excel:
(Deve-se cuidar pois nem todos comandos/funções/recursos de um Banco de Dados comercial, são suportados pelo OLEDB Provider)

SELECT *
FROM [Sheet1$B3:D10]
WHERE DataHora > @DataHora.LastValue
ORDER
BY DataHora ASC

SELECT TOP 10 *
FROM
[Sheet1$B3:G]
WHERE
DataHora > @DataHora.LastValue

ORDER BY DataHora ASC

Anexos:

ResultadosAnaliseLaboratorio.xlsx

Print Friendly, PDF & Email

Este artigo foi útil? Was this helpful?

Classificação média - Average rating 0 / 5. Count: 0

Deixe seu Comentário

Seu endereço de e-mail não será publicado. Campos marcados com asterisco são obrigatórios *