Hunter GE Operators

quinta-feira, 18 de novembro de 2010 15:23:00 Categories: Access Google Earth Hunter
Rate this Content 2 Votes

Olá pessoal. Como todos devem saber, devido aos inúmeros problemas que encontrei no desenvolvimento metodologia telecomHall, eu parei com o envio de macros, códigos, arquivos, etc. E é claro, suspendi as contas de todos os até então assinantes, que já não são cobrados por mais nada.

 

 

Entretanto, muitos de vocês me pediram que eu continuasse, mesmo que fosse apenas ensinando alguns algorítmos, procedimentos padrão, etc. E eu já tinha artigos prontos, pois publicava semanalmente. De qualquer forma, a ferramenta, ou sistema completo existe, e eu uso. Ela contém muito mais módulos até mesmo que os mostrados no RoadMap, sejam com tutoriais publicados ou não.

Vamos então, retomar nosso contato, agora com um foco mais de ensino da idéia, da concepção de todas as ferramentas que criei e ainda crio. Ferramentas e procedimentos de Telecom e TI, que resultam em uma produtividade excepcional, com ganho de tempo, minimização de erros, padronização, entre inúmeras outras vantagens.

Como sou eu sozinho, e só tenho o tempo à noite e fim de semana para trabalhar no site, fica muito difícil (um dos pontos que me fez parar temporariamente o telecomHall). Mesmo assim, vou tentar. Por favor, desculpe os erros de revisão, principalmente se você estiver lendo isso em outro idioma que não o português. :(

Só gostaria também de informar que estamos* elaborando um curso, que muito em breve começará a ser disponibilizado. Serão totalmente gratuitos, e terão esta mesma linguagem simples que vocês estão acostumados com os artigos do telecomHall. (* Eu e alguns membros que estão participando. Você também pode fazer parte da criação desse curso. Clique aqui e saiba mais).

Hoje vou falar de uma aplicação consideravelmente simples: reunir a informação de todos os sites de outras operadoras num só local, servindo como excelente apoio tanto para avaliação da cobertura concorrente - benchmarking - e também como fonte de informações para possíveis solicitações de compartilhamento.

 

Objetivo

Plotar os sites de todas as operadoras existentes no Google Earth, baseado em planilhas do Excel com informação dos sites das mesmas, fazendo o agrupamento – merge – das planilhas no Excel e o tratamento e criação dos arquivos KML no Access.

Observação: As informações de latitude e longitude das operadoras são disponibilizadas pelas agências reguladoras e/ou entidades afins. Cada país tem o seu formato de disponibilização, e ficaria impossível cobrirmos em uma só aplicação, e precisamos escolher um país para demonstração. No caso, escolhemos como país o Brasil, e a ANATEL como fonte de dados.

O tutorial de hoje demonstrará como deve ser feito o processo de forma geral, e as adaptações devem ser feitas de acordo com as características de cada formato específico.

 

Estrutura de Arquivos

Como esse é um módulo novo da nossa ferramenta Hunter, a primeira coisa a fazer é criar os diretórios padrão. Lembre-se que seguindo essa estrutura organizada fica mais fácil de integrar todos os módulos, como já vimos em tutoriais anteriores. De qualquer forma, você pode aprender aqui, e criar a sua própria estrutura.

Seguindo a nossa sugestão, primeiro crie o diretório Operators (1), logo abaixo do diretório já existente GE – sim, esse é um submódulo Hunter Google Earth. Observe que , já temos o diretório criado icon (2), que será usado – contém as imagens que são utilizadas pelo arquivo KML, e é um diretório comum a outros módulos.

Logo abaixo de Operators, criamos os diretórios cujas funções já nos são conhecidas Data, Help, Output e Scripts (3).

Por fim, dois novos subdiretórios do diretório Data: Raw e Parsed (4), contendo respectivamente os dados brutos – planilhas do Excel ou até mesmo outros arquivos com informações das operadoras, e os dados tratados, prontos para serem importados.

Fica mais fácil vendo a figura.

 

Vamos ao que interessa?

Começamos com uma verdade no mundo das programações: muita coisa se copia!

Calma, não estamos falando em pirataria. Somos totalmente contra isso! Mas em se falando de programação, você não vai ficar reinventando a roda sempre que precisar de algo novo. Principalmente porque na área de programação existem diversos websites especializados que nos ajudam na criação dos códigos.

A regra geral da maioria desse websites é que você pode utilizar os exemplos disponibilizados em suas aplicações, desde que mantenha os créditos do autor. É muito mais digno creditar o autor, do que tentar parecer que foi você que 'criou'.

Esse é o caso de hoje. Numa parte de nossa aplicação – como veremos logo logo – precisamos reunir as informações de várias planilhas numa só. E para isso, utilizamos o excelente exemplo de um MVP (Most Valuable Professional), ou em outras palavaras, profissionais experts reconhecidos pela Microsoft, o site http://www.rondebruin.nl/, de Ron de Bruin.

 

No site, você encontra a documentação completa sobre o nosso exemplo (Merge Excel Workbooks), além de diversas outras informações úteis. É claro, se você ainda não tem muita experiência com o VBA, pode achar um pouco complicado. Mas continue aprendendo sempre, e em breve vai ver que websites como o do Ron de Bruin e outros – MVP ou não podem ser de grande valia para nos ajudar a melhorar sempre.

 

O que vamos fazer hoje?

Agora vamos então ver o que vamos fazer hoje. Para facilitar, vamos dividir em duas partes.

  • Agrupar os dados das operadoras do Brasil (ANATEL Raw) em uma só planilha (Merged), e colocar os mesmos no formato adequado (Parsed), utilizando o Excel.

Embora possa parecer uma tarefa trivial, existem alguns tratamentos especiais que deve ser feitos. Isso será assunto para o tutorial Hunter Parser, mas em resumo vamos pegar os dados de todas as planilhas e criar uma só planilha.Dá pra fazer isso na mão, com certeza. O objetivo principal é demonstrar que podemos usar a programação a nosso favor, para resolver qualquer desafio informático que apareça.

  • Importar os dados dessa planilha no Access, e fazer mais alguns tratamentos (utilizando consultas e tabelas auxiliares) e plotar os dados em arquivos KML.

Em si também pode ser considerada uma tarefa trivial, mas mostraremos algumas dicas bem interessantes.

Em suma é isso, mas acompanhe o desenvolvimento, aprenda e deixe sua criatividade livre, vendo como as soluções vão sendo implementadas.

 

Primeira parte: Agrupando e Tratando os arquivos do Excel (Dados das Operadoras)

Como não é nosso objetivo principal ensinar programação, e sim passar as idéias, essa parte não será muito detalhada. Vamos apenas mostrar o que é feito pelas macros. Em caso de qualquer dúvida, ou se precisar de mais explicações, entre em contato.

Os arquivos públicos com os dados de sites da ANATEL tem o formato mostrado a seguir, e para complicar a nossa vida, não vem de forma totalmente tabular. Tem um cabeçalho (1) e uma parte com dados das estações como latitude e longitude (2). O nosso problema é que precisamos repetir os dados comuns do cabeçalho ao lado de cada registro correspondente. E para complicar, cada arquivo de Estado vem com os dados de operadoras logo abaixo da outra.

 

Agrupando Planilhas do Excel com uma Macro

Os dados são disponibilizados por Estado (UF), e essa é origem da nossa necessidade de da primeira macro: são 27 Estados no caso do Brasil! Para agrupar todas numa só, utilizamos a função RawMerged(), que é uma adaptação do código de Ron de Bruin.

 

Essa função adaptada pode ser encontrada no nosso arquivo do Excel, responsável pela primeira parte do processo. Hunter_GE_Operators_BR_1.0_RUN.xls, localizado no diretório Scripts.

 

Tratando Planilhas do Excel com outra Macro

Também nesse arquivo temos uma segunda função, responsável por colocar os dados comuns de cabeçalho lado a lado com os dados de sites. Após serem rodadas ambas as funções (você pode rodar macros utilizando o botão RUN da planilha principal, ou teclar ALT + F11 e rodar direto do VBE, como já vimos com a tecla F5)

 

Nota 1: Dependendo da sua versão do Excel, pode ser que surja uma mensagem informando que o mesmo não está no formato informado pela extensão. Desconsidere e clique em abrir assim mesmo.

Nota 2: Um outro detalhe é que esse arquivo faz referência a planilhas de estilos, que você também não tem em sua máquina. Simplesmente clique em OK.

 

Pronto. Agora vamos voltar ao ritmo um pouco mais detalhado que você está acostumado. Isso por três motivos:

  • A primeira parte – aplicação que agrupa e trata os dados é específica para o formato de dados de um país (Brasil).
  • Essa aplicação pode ser vista em sua forma original no site indicado;
  • E terceira e mais importante: o que você precisa como resultado da primeira parte é de uma tabela com pelo menos dados de Operadora, Latitude e Longitude. Demais campos podem ser considerados desejáveis, mas são apenas opcionais. Ou seja, se você tiver uma planilha – tabela – com os dados de todas as operadoras, poderá ajustar a segunda parte de acordo com as suas necessidades.

 

Segunda Parte: Importando os Dados e Gerando os arquivos KML

A partir de agora vamos usar o nosso segundo arquivo, também localizado no diretório Scripts. É o arquivo Hunter_GE_Operators_BR_1.0_RUN.mdb. Nota: mesmo não tendo esses arquivos, pode aprender como tudo é feito, como vamos mostrar aqui. Se ainda tiver alguma dúvida, é só entrar em contato. Até mesmo nos fóruns estaremos discutindo sobre as aplicações (também).

Esse arquivo realiza o processo em duas partes, que podem ser executadas a partir de duas macros:

  • Import_RawMergedParsed_RUN: que importa os dados do arquivo do Excel para a tabela RawMergedParsed; Bem simples, como vamos ver a seguir.
  • Plot_Operators_KML_RUN: que utiliza a consulta qry_Operators_KML como fonte dos dados para criar o arquivo KML. Lembre-se que já vimos com detalhes em outros tutoriais como criar os arquivos KML via código VBA, e é importante que você já tenha aprendido isso. Caso contrário, recomendamos que refaça alguns tutoriais.

 

Importando os Dados

Eis a função que importa a planilha do Excel RawMergedParsed.xls para a tabela RawMergedParsed do nosso banco de dados.

 

Bem simples, somente usando o comando TransferSpreadSheet e os argumentos como mostrado.

Um detalhe que é a utilização do comando DoCmd.RunSQL . Esse comando é usado para apagarmos os dados da nossa tabela para onde vamos importar os dados. Boa prática, já que não queremos nenhum dado duplicado – somente os dados do arquivo importado em seguida.

 

Gerando os arquivos KML

Uma primeira informação. Os dados tratados pela nossa primeira parte (Macros do Excel) geraram a planilha no formato desejado, porém com algumas linhas em branco. Quando o arquivo é importado, as linhas aparecem na tabela.

 

Isso não é problema. É claro que poderíamos ter incluídos algumas linhas nas macros do Excel para tratar esse pequeno probleminha.

Mas vamos ver como resolver isso usando uma consulta no Access. Para isso, criamos uma consulta mostrando todos os dados da tabela RawMergedParsed, só que excluindo as linhas em branco. Podemos fazer isso inserindo um critério <> "" na nossa nova consulta qry_RawMergedParsed.

 

Pronto, resolvemos o problema das linhas em branco, e nossa tabela de dados na verdade é a consulta qry_RawMergedParsed.

Mas agora mais um pequeno problema. E útil para mostrarmos mais uma excelente aplicação de consultas e tabelas auxiliares. O problema é o seguinte: dependendo da região, existem diferentes nomes - filiais - para cada operadora. E queremos plotar os dados utilizando o nome fantasia único, e não vários nomes para cada filiais.

Para entender melhor, veja a figura a seguir. Para resolver o nosso problema, basta criarmos uma tabela auxiliar, com o mapeamento dos campos das filiais e o correposndente nome Fantasia comum.

 

E como fazer isso numa nova consulta? Simples, crie uma nova consulta, adicione a tabela tbl_AuxFantasy e a consulta qry_RawMergedParsed. Una o campo com o nome das filiasi como mostrado na figura abaixo, e adicione o nome Fantasia na nova consulta qry_RawMergedParsedFantasy.

 

Certo, estamos quase lá. Vamos só fazer mais algumas coisas interessantes. Como no nosso caso os arquivos tem a data de ativação de cada site, vamos criar um campo com a data, para que depois possamos gerar arquivos para cada subgrupo. Explicando melhor, criamos por exemplo um novo campo calculado 'Ano', e depois podemos criar arquivos com dados de cada ano. Para obter o ano a partir de uma data no VBA usamos a função Year().

Nota: Existem alguns outros campos calculados, como por exemplo a Latitude e Longitude, mas apenas cálculos matemáticos para colocar no formato decimal. Isso não é relevante, principalmente porque os dados que você talvez utilize em seu país pode já fornecer a Latitude e Longitude neste formato decimal.

Criamos então a nossa terceira e final consulta qry_Operators_KML, utilizando a consulta anterior – que agora já tem o Nome Fantasia – e com os campos calculados necessários. Lembrando que na verdade só precisaríamos do Nome Fantasia, Latitude e Longitude. Mas com outros campos, como por exemplo a data, podemos melhorar um pouco mais a apresentação.

 

Alguns detalhes do código VBA

Vale a pena falar de mai alguns novos comandos e dicas utilizadas no código VBA para geração dos arquivos KML.

A primeira delas é a seguinte: temos a informação de ano, e para uma melhor apresentação, vamos gerar os arquivos por ano (que no caso específico vai de 1992 a 2010). Mas também queremos gerar os arquivos total, ou seja, apenas um arquivo, contendo todas as informações, para todos os anos.

Existem diversas formas de fazer isso, e vamos mostrar uma.

Essa é a parte do código que faz isso. A seguir, explicamos um pouco mais.

 

Primeiro, definimos uma váriável intUseYear, para indicar se queremos usar o Ano também nas plotagens ou não. Se o valor dela for atribuido por nós igual a zero, então roda duas vezes, senão roda apenas uma. E cada vez que rodar, dentro do loop usamos esse valor para definir os recordsets e filtros de acordo.

 

RecordSet Filter

Uma nova utilização bem legal dos recordset, que usamos hoje, é a possibilidade de fazer filtros. E essa é a forma como fizemos nesse exemplo de hoje, para demonstração.

Em resumo, primeiro abrimos um recordset, com todos os dados.

 

Então, baseado no filtro escolhido, abrimos o Segundo recordset.

 

Bom, as maiores novidades no VBA por hoje são essas. Continue praticando, faça pesquisas na Internet (Google). Aumente o seu conhecimento dia a dia, descubra novas utilizações e possibilidades de cada função ou comando.

 

Resultado Final

O nosso resultado final, especificamente para o Brasil já que utilizamos os dados desse país como exemplo, é mostrado a seguir.

 

Observe que uma das utilizações dos arquivos gerados por ano é que fica fácil você identificar as estações de uma determinada operadora em um ou mais anos.

 

Uma utilização também interessante é navegar até uma área específica, e obter as informações detalhadas do site – simplesmente clicando sobre ele!

 

Além da utilização como auxílio em Surveys de RF – solicitações de compartilhamento – e Avaliação da Cobertura das outras operadoras, você pode criar relatórios com excelente apresentação. Pode ainda trabalhar com os dados em Excel, e gerar gráficos com a evolução de cada operadora, por exemplo. Tudo isso que você pode fazer com os dados fica a acargo de sua criatividade!

 

Conclusão

Neste tutorial demonstramos como utilizar Macros do Excel e do Access para reunir informação de todos os sites de outras operadoras num só local, servindo como excelente apoio tanto para avaliação da cobertura concorrente - benchmarking - e também como fonte de informações para possíveis solicitações de compartilhamento.

Vimos algumas novas funções VBA como Filtros de Recordset. Cada vez mais utilizaremos esses recursos a fim de obtermos aplicações que facilitem o nosso trabalho, melhorem a nossa performance, e produtividade. O resultado não pode ser outro: nos destacamos onde quer que estejamos trabalhando.

Mais uma vez fica o convite para que você participe mais, e também que leia e aprenda todos os tutoriais.

E não deixe de visitar o recém lançado Fórum. Poste a sua dúvida, qualquer que seja sua área de atuação em TI, Telecom e/ou Programação.