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 |
Nome da planilha do arquivo .xlsx |
|
WHERE |
Cláusula de controle da consulta, ou seja, a consulta |
|
ORDER BY DataHora ASC |
Garante que os dados retornados sempre estarão em ordem |
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
Anexos:
ResultadosAnaliseLaboratorio.xlsx
