Importar dados de uma pasta com vários ficheiros (Power Query)

Utilize Power Query para combinar múltiplos ficheiros com o mesmo esquema armazenado numa única pasta numa só tabela. Por exemplo, todos os meses pretende combinar livros de orçamento de vários departamentos, em que as colunas são iguais, mas o número de linhas e valores difere em cada livro. Depois de a configurar, pode aplicar transformações adicionais como faria com qualquer origem de dados importada individual e, em seguida, atualizar os dados para ver os resultados de cada mês.

Uma descrição geral conceptual da Combinação de ficheiros de pastas

Nota Este tópico mostra como combinar ficheiros de uma pasta. Também pode combinar ficheiros armazenados no SharePoint, Armazenamento de Blobs do Azure e Azure Data Lake Storage. O processo é semelhante.

Versões mais recentes Office 2013



Mantenha-o simples:

Certifique-se de que todos os ficheiros que pretende combinar estão contidos numa pasta dedicada sem ficheiros desnecessários. Caso contrário, todos os ficheiros na pasta e as subpastas que selecionar serão incluídos nos dados a combinar.
Cada ficheiro deve ter o mesmo esquema com cabeçalhos de coluna consistentes, tipos de dados e número de colunas. As colunas não têm de estar na mesma ordem em que a correspondência é feita por nomes de colunas.
Se possível, evite objetos de dados não relacionados para origens de dados que possam ter mais do que um objeto de dados, como um ficheiro JSON, um livro do Excel ou uma base de dados do Access.

Cada um destes ficheiros segue um padrão simples, apenas uma tabela de dados em cada ficheiro.

Selecione Dados > Obter Dados > da Pasta >de Ficheiros. É apresentada a caixa de diálogo Procurar .
Localize a pasta que contém os ficheiros que pretende combinar.
É apresentada uma lista dos ficheiros na pasta na caixa de diálogo caminho da Pasta <>. Verifique se todos os ficheiros que pretende estão listados.
Selecione um dos comandos na parte inferior da caixa de diálogo, por exemplo Combinar > Combinar & Carregar. Existem comandos adicionais abordados na secção Acerca de todos esses comandos.
Se selecionar qualquer comando Combinar , é apresentada a caixa de diálogo Combinar Ficheiros . Para alterar as definições de ficheiro, selecione cada ficheiro na caixa Ficheiro de Exemplo, defina a Origem do Ficheiro, o Delimitador e a Deteçãode Tipo de Dados conforme pretendido. Também pode selecionar ou desmarcar a caixa de verificação Ignorar ficheiros com erros na parte inferior da caixa de diálogo.
Selecione OK.

Result

Power Query cria automaticamente consultas para consolidar os dados de cada ficheiro numa folha de cálculo. Os passos e colunas de consulta criados dependem do comando que escolher. Para obter mais informações, consulte a secção Acerca de todas essas consultas.

Selecione Dados > Obter Dados > da Pasta >de Ficheiros. É apresentada a caixa de diálogo Procurar.
Localize a pasta que contém os ficheiros que pretende combinar.
É apresentada uma lista dos ficheiros na pasta na caixa de diálogo caminho da Pasta <>. Verifique se todos os ficheiros que pretende estão listados.
Selecione um dos comandos na parte inferior da caixa de diálogo, por exemplo Combinar > Combinar & Transformar. Existem comandos adicionais abordados na secção Acerca de todos esses comandos.

É apresentada a Editor do Power Query.
A coluna Valor é uma coluna de Lista estruturada. Selecione o ícone Expandir e, em seguida, selecione Expandir para Novas linhas.
A coluna Valor é agora uma coluna Registo estruturada. Selecione o ícone Expandir . É apresentada uma caixa de diálogo pendente.
Mantenha todas as colunas selecionadas. Poderá querer desmarcar a caixa de verificação Utilizar o nome da coluna original como prefixo . Selecione OK.
Selecione todas as colunas que contêm valores de dados. Selecione Base, a seta junto a Remover Colunas e, em seguida, selecione Remover Outras Colunas.
Selecione Base > Fechar & Carregar.

Result

Cada uma destas origens de dados pode ter mais do que um objeto para importar. Um livro do Excel pode ter várias folhas de cálculo, tabelas do Excel ou intervalos com nome. Uma base de dados do Access pode ter várias tabelas e consultas.

Selecione Dados > Obter Dados > da Pasta >de Ficheiros. É apresentada a caixa de diálogo Procurar.
Localize a pasta que contém os ficheiros que pretende combinar.
É apresentada uma lista dos ficheiros na pasta na caixa de diálogo caminho da Pasta <>. Verifique se todos os ficheiros que pretende estão listados.
Selecione um dos comandos na parte inferior da caixa de diálogo, por exemplo Combinar > Combinar & Carregar. Existem comandos adicionais abordados na secção Acerca de todos esses comandos.
Na caixa de diálogo Combinar Ficheiros :
- Na caixa Ficheiro de Exemplo , selecione um ficheiro para utilizar como dados de exemplo utilizados para criar as consultas. Não pode selecionar um objeto ou selecionar apenas um objeto. No entanto, não pode selecionar mais do que um.
- Se tiver muitos objetos, utilize a caixa Procurar para localizar um objeto ou as Opções de Visualização juntamente com o botão Atualizar para filtrar a lista.
- Selecione ou desmarque a caixa de verificação Ignorar ficheiros com erros na parte inferior da caixa de diálogo.
Selecione OK.

Result

Power Query cria automaticamente uma consulta para consolidar os dados de cada ficheiro numa folha de cálculo. Os passos e colunas de consulta criados dependem do comando que escolher. Para obter mais informações, consulte a secção Acerca de todas essas consultas.

Para obter mais flexibilidade, pode combinar explicitamente ficheiros no Editor do Power Query com o comando Combinar Ficheiros. Suponhamos que a pasta de origem tem uma mistura de tipos de ficheiros e subpastas e pretende direcionar ficheiros específicos com o mesmo tipo de ficheiro e esquema, mas não com outros. Isto pode melhorar o desempenho e ajudar a simplificar as suas transformações.

Selecione Dados > Obter Dados > da Pasta > de Ficheiros. É apresentada a caixa de diálogo Procurar .
Localize a pasta que contém os ficheiros que pretende combinar e, em seguida, selecione Abrir.
É apresentada uma lista de todos os ficheiros na pasta e subpastas na caixa de diálogo >caminho da Pasta< . Verifique se todos os ficheiros que pretende estão listados.
Selecione Transformar Dados na parte inferior. O Editor do Power Query é aberto e apresenta todos os ficheiros na pasta e as subpastas.
Para selecionar os ficheiros que pretende, filtre colunas, como Extensão ou Caminho da Pasta.
Para combinar os ficheiros numa única tabela, selecione a coluna Conteúdo que contém cada Binário (normalmente a primeira coluna) e, em seguida, selecione Base > Combinar Ficheiros. É apresentada a caixa de diálogo Combinar Ficheiros .
Power Query analisa um ficheiro de exemplo, por predefinição, o primeiro ficheiro na lista, para utilizar o conector correto e identificar colunas correspondentes.

Para utilizar um ficheiro diferente para o ficheiro de exemplo, selecione-o na lista pendente Ficheiro de Exemplo.
Opcionalmente, na parte inferior, selecione Ignorar ficheiros com errospara excluir esses ficheiros do resultado.
Selecione OK.

Result

Existem vários comandos que pode selecionar e cada um tem um objetivo diferente.

Combinar e Transformar Dados Para combinar todos os ficheiros com uma consulta e, em seguida, iniciar a Editor do Power Query, selecione Combinar > Combinar e Transformar Dados.
Combinar e Carregar Para apresentar a caixa de diálogo Ficheiro de exemplo, crie uma consulta e, em seguida, carregue para a folha de cálculo, selecione Combinar > Combinar e Carregar.
Combinar e Carregar Para Para apresentar a caixa de diálogo Ficheiro de exemplo, crie uma consulta e, em seguida, apresente a caixa de diálogo Importar , selecione Combinar > Combinar e Carregar Para.
Carregar Para criar uma consulta com um passo e, em seguida, carregar para uma folha de cálculo, selecione Carregar > Carregar.
Carregar Para Para criar uma consulta com um passo e, em seguida, apresentar a caixa de diálogo Importar , selecione Carregar > Carregar Para.
Transformar DadosPara criar uma consulta com um passo e, em seguida, iniciar a Editor do Power Query, selecione Transformar Dados.

No entanto, combina ficheiros, são criadas várias consultas de suporte no painel Consultas no grupo "Consultas auxiliares".

Uma lista das consultas criadas no painel Consultas

Power Query cria uma consulta "Ficheiro de Exemplo" com base na consulta de exemplo.
Uma consulta de função "Transformar Ficheiro" utiliza a consulta "Parameter1" para especificar cada ficheiro (ou binário) como entrada para a consulta "Ficheiro de Exemplo". Esta consulta também cria a coluna Conteúdo que contém o conteúdo do ficheiro e expande automaticamente a coluna Registo estruturada para adicionar os dados da coluna aos resultados. As consultas "Transformar Ficheiro" e "Ficheiro de Exemplo" estão ligadas, para que as alterações à consulta "Ficheiro de Exemplo" sejam refletidas na consulta "Transformar Ficheiro".
A consulta que contém os resultados finais encontra-se no grupo "Outras consultas". Por predefinição, tem o nome da pasta a partir da qual importou os ficheiros.

Para uma investigação mais aprofundada, clique com o botão direito do rato em cada consulta e selecione Editar para examinar cada passo de consulta e para ver como as consultas funcionam em conjunto.

Para iniciar o processo de combinação de múltiplos ficheiros, primeiro certifique-se de que todos os ficheiros que pretende combinar estão contidos numa única pasta de ficheiros.

Nota: Os ficheiros excel, Text, CSV, JSON, XML e Access são suportados.

Clique no separador Power Query e, em seguida, em De Ficheiro > Da Pasta.
Clique no botão Procurar para localizar a pasta.

Será apresentada uma lista dos ficheiros na pasta. Selecione a opção Editar para abrir o Editor do Power Query. Neste caso, carregámos 4 livros do Excel.
Depois de verificar que todos os ficheiros que pretende estão listados, clique no botão Combinar Binários na coluna Conteúdo .
Cada ficheiro é analisado e o formato de ficheiro correto é determinado, como texto, Excel ou JSON. Neste caso, é apresentada uma lista de folhas de cálculo do primeiro livro do Excel. Selecione a folha de cálculo que pretende utilizar e, em seguida, clique em OK.
O processo de transformação combinar binários criará automaticamente uma consulta para consolidar os dados de cada ficheiro e apresentar uma pré-visualização. Se estiver satisfeito com o resultado, clique em Fechar & Carregar.

O processo combinar binários consolidará as folhas de cálculo listadas numa única folha de cálculo.
Se os ficheiros de dados originais forem alterados, pode sempre atualizar os dados importados. Clique em qualquer parte do intervalo de dados e, em seguida, aceda a Ferramentas de Consulta > Atualizar. Além disso, pode aplicar facilmente passos adicionais de transformação ou extração ao modificar a consulta exemplar criada automaticamente, sem ter de se preocupar em modificar ou criar passos adicionais de consulta de função; quaisquer alterações à consulta exemplar são geradas automaticamente na consulta de função ligada.

Problemas Conhecidos

Ao carregar o procedimento combinar binários para o Excel, poderá ver a seguinte mensagem de erro:

Combinar mensagem de erro Binários. Este é um erro conhecido que está a ser resolvido.

Se ignorar os erros ao clicar em OK, o processo combinar binários será concluído e carregará corretamente os seus dados, apesar do erro. Estamos a trabalhar para corrigir o erro.