Liste todos os arquivos em uma pasta no Excel usando o Power Query - Dicas do Excel

Índice

Assistir vídeo

Pergunta de hoje: Marcia precisa obter uma lista de todos os arquivos PDF da nota fiscal de uma pasta para uma planilha do Excel. Isso é fácil de fazer se você estiver usando o Excel 2016 em um PC com Windows usando as novas ferramentas Get & Transform Data.

Se você tiver o Excel 2010 para Windows ou Excel 2013 para Windows, terá que baixar o suplemento Power Query gratuito da Microsoft. Vá para o seu mecanismo de busca favorito e digite “Download Power Query” para encontrar o link atual. (A Microsoft adora alterar URLs todo trimestre e meu incrível cara da web odeia quando nossos links estão desatualizados, então nem vou tentar colocar um link aqui.)

O vídeo abaixo mostra as etapas completas, mas aqui está a visão geral:

Comece com uma planilha em branco
Dados, obter dados, de arquivo, de pasta
Navegue até a pasta
Clique em Editar em vez de Carregar
Abra a lista suspensa de filtros no tipo de arquivo e remova tudo o que não seja PDF
Abra o filtro na pasta e remova todas as subpastas de lixo
Mantenha apenas o nome do arquivo e a pasta - clique com o botão direito em cada título da coluna e escolha Remover
Arraste o título da pasta para a esquerda do título do arquivo. Isso permite que a mesclagem funcione.
Selecione as duas colunas. Clique em um título. Shift + clique no outro título.
Escolha Adicionar coluna, mesclar colunas, digite um novo nome para a coluna. Clique OK.
Clique com o botão direito do mouse no título da nova coluna e remova as outras colunas
Home, Close & Load
A parte incrível … você pode atualizar a consulta mais tarde. Clique no ícone Atualizar no painel Consultas e conexões.

Embora o Power Query seja incrivelmente poderoso, esta é uma das minhas tarefas favoritas. Freqüentemente, desejo executar uma macro VBA em cada arquivo de uma pasta. Obter uma lista de todos os PDFs em uma pasta é um bom ponto de partida.

Assistir vídeo

Transcrição de vídeo

Aprenda Excel com o podcast, episódio 2181 - Listar arquivos de pastas no Excel!

Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje, alguém tem uma lista de arquivos PDF de nota fiscal em uma pasta e precisa obter a lista de todos esses nomes de arquivo no Excel. Tudo bem, e uma maneira de fazer isso é digitar todos ou copiar e colar do Windows Explorer, mas há uma ótima ferramenta que pode resolver isso. E minha primeira pergunta foi “Bem, qual versão do Excel você tem?” Porque se você tiver o Excel 2016, eles terão esta incrível nova funcionalidade chamada “Get & Transform Data”! Agora, no Office 365, está do lado esquerdo, acho que na versão original do Excel 2016 estava no terceiro grupo, tudo bem, então é só procurar Get & Transform. Se você estiver no Excel 2010 ou Excel 2013 para Windows, poderá baixar o Power Query e terá sua própria guia com exatamente isso.

Agora vamos dar uma olhada rápida nesta pasta, certo, acabei de criar uma pasta falsa aqui com alguns dados falsos. Você verá que nesta pasta há arquivos do Excel e PDFs, só quero os PDFs, e também há algumas subpastas, não quero esses PDFs, quero apenas os PDFs na pasta principal. Então, C: Budgets, vou copiar isso e depois voltar aqui para o Excel, e vamos dizer que queremos obter dados, de um arquivo, de uma pasta inteira, assim, e então digite o caminho da pasta lá, ou use o botão Procurar, qualquer um deles. E quando você obtém esta primeira tela, você definitivamente deseja Editar, e agora estamos no editor do Power Query.

Tudo bem, meu objetivo aqui, eu não preciso do Conteúdo, então vou clicar com o botão direito e dizer Remover essa coluna. Aqui está minha lista de arquivos, eu só quero arquivos PDF, então se houver algo que não seja PDF, eu só quero PDFs, clique em OK, posso ver que são apenas os arquivos PDF. Ah, e então olhe aqui, veja, agora eles estão puxando coisas apenas da pasta original e da pasta Lixo, então eu abro isso e desmarco tudo que não é a pasta original. Certo, agora eu tenho uma pequena lista legal, e esta lista é, você sabe o quê, 9 registros, mas na vida real eu aposto que eles provavelmente têm, você sabe, dezenas ou centenas destes. Tudo bem, não preciso de mais nada agora, posso clicar com o botão direito do mouse e Remover essas colunas.

Certo, agora o que eu realmente preciso aqui é o caminho da pasta e o nome do arquivo juntos. Tudo bem, então vou pegar o FolderPath e arrastá-lo para a esquerda e soltá-lo lá, e então a etapa mágica aqui: no Excel normal, teríamos que fazer concatenação para isso, mas o que vou fazer é: vou mesclar colunas. Vou adicionar coluna e escolher Mesclar colunas, o separador será nenhum, a nova coluna será chamada de nome de arquivo e clique em OK, certo, temos o nome da pasta, a barra e o nome do arquivo , Curtiu isso. Agora, isso é realmente a única coisa de que precisamos, então vou clicar com o botão direito e dizer Remover as outras colunas e, finalmente, Home, Fechar e Carregar, e teremos uma nova planilha com nossos dados. Tudo bem agora, ele vem como uma tabela, então vou apenas copiar isto, Ctrl + C,e, em seguida, venha aqui para onde eu realmente queria os dados aqui e, em Colar valores especiais, clique em OK. Agora não é mais uma mesa, são apenas meus dados puros, assim, e agora, aqui está o que é realmente bonito sobre isso.

Então, configuramos isso uma vez e, uau, demorou menos de 3 minutos para configurar, mas vamos voltar à pasta de orçamentos e mover algumas coisas. Vamos pegar um desses registros de lixo e copiá-lo para a pasta principal, Ctrl + V, tudo bem, então agora há mais coisas aqui, há 10 arquivos PDF em vez de 9. Se eu chegar aqui onde está a consulta, e mais no lado direito da tela, nas Consultas & Conexões, você pode ter que aumentar isso, eu já fiz o meu mais amplo, você verá nossos orçamentos com 9 linhas carregadas. Vou clicar no pequeno ícone Atualizar aqui e, muito rapidamente, os Orçamentos agora têm 10 linhas carregadas. Portanto, ele coleta os novos registros, você configura isso uma vez e, então, poderá apenas atualizar para obter os novos dados.

Bem, este é o ponto do podcast em que normalmente peço que você compre meu livro, mas hoje vou pedir que compre este livro “M is for (DATA) MONKEY” de Ken Puls e Miguel Escobar. Um livro INCRÍVEL que vai te ensinar tudo sobre como usar o Power Query ou Get & Transform Data, tudo o que aprendi sobre o Power Query aprendi neste livro.

OK, conclua este episódio: Nosso objetivo é como importar uma lista de nomes de arquivos para o Excel, se você tem o Excel 2016, pode usar o novo Get & Transform Data. Se você não tem o 2016, mas tem uma versão real do Excel em execução no Windows, pode baixar o suplemento gratuito do Power Query para Excel 2010 ou Excel 2013. Não vai funcionar no seu telefone Android ou seu iPad, ou seu iPhone, ou Surface RT, ou seu Mac, certo, é apenas para versões do Excel para Windows. Então, vamos começar a partir de uma planilha em branco, Dados, Obter Dados, Do Arquivo, Da Pasta, digite o nome da pasta ou Navegar, certifique-se de clicar em Editar em vez de Carregar. E então no Filtro, filtre pelo tipo de arquivo para se livrar de tudo que não seja um PDF, filtre pelo nome da pasta para se livrar de todas as subpastas de lixo. Mantenha apenas o nome do arquivo e a pasta,então, clique com o botão direito nos outros e diga Remover coluna e arraste o título da pasta para a esquerda do arquivo, o que permite que a mesclagem funcione. Selecione as duas colunas e, na guia Adicionar coluna, escolha Mesclar colunas, digite um novo nome, clique em OK e clique com o botão direito do mouse nessa nova coluna e em Remover as outras colunas, Home, Fechar e Carregar, e você receberá sua lista. A parte incrível, você pode atualizar a consulta mais tarde usando este ícone Atualizar nas Consultas e conexões.você pode atualizar a consulta mais tarde usando este ícone Atualizar em Consultas e conexões.você pode atualizar a consulta mais tarde usando este ícone Atualizar em Consultas e conexões.

Bem, ei, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!