E aí, FERA! Hoje, vamos desbravar a selva densa e misteriosa sobre como importar XML no Excel usando Power Query. Se você já se pegou pensando “Como eu faço isso?”, então você está no lugar certo. Vamos decifrar esse enigma juntos, passo a passo, como Indiana Jones decifrando um antigo hieróglifo.
Então, pegue seu chicote e seu chapéu, e vamos nessa!

XML: O Mapa do Tesouro

XML, ou eXtensible Markup Language, é uma linguagem de marcação que define um conjunto de regras para codificar documentos de forma que seja legível tanto para humanos quanto para máquinas.

Imagine que é como um mapa do tesouro, onde cada marca, símbolo e direção é meticulosamente descrito, de uma forma ordenada, evidenciando aonde está a informação que você procura.

Um exemplo simples de um arquivo XML seria algo assim:

<xml>
    <pessoa id="1">
        <nome>pedro</nome>
        <idade>30</idade>
        <profissao>pescador</profissao>
    </pessoa>
    <pessoa id="2">
        <nome>lucas</nome>
        <idade>40</idade>
        <profissao>medico</profissao>
    </pessoa>
</xml>

Podemos ver que nesse arquivo temos um relatório de pessoas, idade e profissão, e se você olhar bem, perceberá que está disposto de uma forma hierárquica.

Importando XML para o Excel: A Trilha Batida

Antes do Power Query, a maneira mais comum de importar XML COM CLIQUE DO MOUSE no Excel é através do recurso Mapa XML.

Você escolhe os marcos (os campos do XML), segue a trilha (importa o arquivo) e voilà! Você encontrou o tesouro (dados XML).

Quer ver como funciona? pega o exemplo de comando acima, e cria uns arquivos, e salva com a extensão “.xml” em uma pasta ai em sua máquina. Ah, altere os dados né?!

Primeiro passo é ter habilitada a guia desenvolvedor, que se você já mexe com vba, tens habilitada:

Feito isso, vá na nova guia desenvolvedor e clique na opção de importação para que possamos importar um exemplo de mapeamento de arquivo XML.

Lá embaixo, no canto inferior direito, selecione a opção do mapa XML, para usarmos como base, onde, vamos dizer para o Excel, quais campos queremos importar em todos arquivos da pasta.

Clique em “Adicionar”, localize um dos arquivos que servirá como “treinador”, e clique em abrir, e por fim, nesta caixa a seguir, clique em “OK”.

Com isso, os campos do XML são apresentados, para que você possa clicar e arrastar para o local aonde quer despejar a tabela:

Clique em cima da pastinha “pessoa” e arraste até o local onde você deseja depositar os dados, quando importarmos todos arquivos de uma só vez:

Depois disso, basta selecionar a opção de importar, e selecionar os arquivos:

E com isso o Excel cria uma tabela, colocando todos os itens das mesmas TAGS, um abaixo do outro, de uma forma muito inteligente:

Importando XML para o Excel: A Rota Power Query

Aqui é onde o Power Query entra em cena. É como ter seu próprio guia de selva pessoal que se adapta às suas necessidades. Você pode importar arquivos XML, transformá-los e até mesmo atualizá-los automaticamente.

Da mesma forma que antes, aqui também vamos usar exclusivamente o MOUSE, mas para os mais curiosos, vou explicar também o comando que gera por trás dos panos.

Antes de mais nada, coloque seus arquivos em uma pasta especifica só com xml lá dentro, e depois clique em importar dados da pasta:

Vai abrir uma tela te mostrando os arquivos que tem nesta pasta:

Clique em “Transformar Dados”, e depois quando abrir a tabela, basta expandir os arquivos:

Agora é só selecionar a tabela que será “o mapa”, assim como ocorreu lá no modo de mapa xml:

Prontinho, a tabela está criada:

Bastado agora carregar para o Excel, escolher um lugar, e pronto!

A Vantagem no Power Query

Como o Power Query está pegando todos os arquivos da pasta, isso significa dizer que se algum arquivo for incluído, excluído ou até substituído, bastará clicar em ATUALIZAR TUDO e pronto, a tabela estará atualizada, sem precisar refazer todas as etapas.

Aqui o Power Query se sai melhor, pois para reaproveitar o MAPA XML do modo “Trilha Batida”, teríamos que criar uma macro, mas que seria algumas vezes mais trabalhosa que o comando Power Query que criamos.

A Realidade dos Arquivos XML

Os arquivos XML são incrivelmente versáteis e são usados em uma variedade de aplicações, desde feeds de notícias e podcasts até armazenamento de dados e configurações de software.

No entanto, é importante lembrar que os arquivos XML são arquivos de texto, o que significa que eles não têm a mesma performance de leitura que um banco de dados SQL, onde os dados são fortemente tipados.

Portanto, se você está lidando com grandes volumes de dados, pode ser necessário considerar outras opções.

E quando o XML tem subtabelas?

Primeiramente subtabelas é como se dentro do tesouro encontrado tivesse outro mapa, com caminho de outro tesouro.
Ai você respira fundo e vai em busca do outro tesouro, e ao chegar lá, adivinha… outro mapa…
Estressante! não é mesmo?!

Eu destaquei essa seção, logo depois de falar das duas formas de importar, justamente para te mostrar como se faz em uma e noutra, e comparar.

Veja abaixo o nosso xml com subtabelas:

<xml>
    <pessoa id="1">
        <nome>pedro</nome>
        <idade>30</idade>
        <profissao>pescador</profissao>
        <habilidades>
            <dirige>
                <carro>SIM</carro>
                <moto>NAO</moto>
            </dirige>
            <cozinha>NAO</cozinha>
        </habilidades>
    </pessoa>
    <pessoa id="2">
        <nome>lucas</nome>
        <idade>40</idade>
        <profissao>medico</profissao>
        <habilidades>
            <dirige>
                <carro>NAO</carro>
                <moto>SIM</moto>
            </dirige>
            <cozinha>NAO</cozinha>
        </habilidades>
        <observacao>detalhista ao extremo</observacao>
    </pessoa>
</xml>

Veja só que em ambas pessoas surgiu a tag “dirige” e nela, abre mais duas tags para cada veículo.
Se isso não bastasse, na pessoa 2, tem a tag “observação” que não tem na pessoa 1.

Crie um arquivo xml com os dados acima, e faça o passo a passo de importação com mapa XML e verás que retornará o seguinte resultado:

Aqui eu chamo de “coisa linda”, pois apenas com o mouse, todas as colunas são abertas numa “lambada” só.

No Power Query, será que funciona da mesma forma?
Importe o arquivo criado, e verás que vai abrir como demonstrado a seguir:

Ou seja, para abrir a coluna “habilidades” você precisa criar nesse botãozinho com duas flechinhas tortas.
Ao fazer isso ele vai abrir, e veja só o que eu falei do tesouro, se tornando realidade:

Para abrir o cambo “habilidades.dirige” você deverá abri-lo também:

Isso pode TE AJUDAR MUITO, pois você só vai expandindo os campos que REALMENTE PRECISA, aumentando a velocidade de leitura caso você tenha muitas etapas de tratamento após essas etapas.

O grande perigo dos dois modos de importação

Quando você estiver trabalhando com xml, podem haver casos que o XML tenha mais graus hierárquicos, como mostro no vídeo acima, e você pode se precaver o máximo, mas, “algum campinho” acaba vazando.

Quer um exemplo? Nota Fiscal de venda de produtos, que a cada ano muda a versão do XML, incluindo mais campos.

Esse é um problema tanto para importação com MAPA quanto a importação com Power Query, uma vez que o script de importação, foi montado com base em um arquivo base, mas, se campos novos surgirem, teremos que alterar nossos importadores (Mapa XML ou Power Query).

Mas, eu nunca apresento um problema sem ter uma solução, e nesse caso eu já fiz 3 LIVES (totalizando 5 horas de aula) demonstrando passo a passo, como resolver esses casos.

Hoje essas SUPER AULAS que estão disponíveis, com todo o material de apoio, para os alunos da formação Power Query 2.0 [minha comunidade].

Nessas duas primeiras eu mostro a importação do modo básico ao avançado, criando um algoritmo em loop no Power Query para abrir todos os campos SEMPRE, e nunca perder campos.
SIM, é isso mesmo que você leu, nessas aulas você aprenderá como juntar “receitas de bolo” e “notas fiscais” na mesma tabela, e se amanhã, surgir um “receituário médico”, ele vai entender.

Parece mágica mas não, é Power Query.

Essa aula a seguir, mostro uma super dica de criar uma importação em estágios, onde você NUNCA MAIS vai importar o mesmo XML mais de uma vez, e sim, você cria um CONVERSOR DE XML em tabelas Excel, e depois basta importar no Power Query, como você importa arquivos de uma pasta (por exemplo).

Torne-se um especialista em tratamento de dados, em especial com Power Query, SQL e Python com a FORMAÇÃO POWER QUERY 2.0, que vai muito além do Power Query.

Conteúdo Complementar no Youtube

No inicio do meu canal do Youtube, lá em 2019, eu criei esse video aqui (clique aqui) , onde mostro as duas formas de importação que mencionei no inicio deste post.

Assista e faça junto comigo, praticando nos arquivos xml que você tem ai no seu e-mail, daquelas comprinhas que você fez on-line recentemente.

Conclusão

E aí, FERA, chegamos ao final de nossa jornada. Dominar a importação de XML no Excel com Power Query é como encontrar um tesouro escondido. É uma habilidade que pode abrir portas para novas oportunidades e desafios em sua carreira como analista de dados.

Mas lembre-se, a jornada do aprendizado nunca termina. Você deve estar sempre pronto para explorar novos territórios e enfrentar novos desafios.

Por hoje é isso FERA!
Forte abraço do