Importer des données à partir d’un dossier contenant plusieurs fichiers (Power Query)

Utilisez Power Query pour combiner plusieurs fichiers avec le même schéma stocké dans un seul dossier dans une même table. Par exemple, chaque mois, vous souhaitez combiner des classeurs de budget de plusieurs services, où les colonnes sont identiques, mais le nombre de lignes et de valeurs diffèrent dans chaque classeur. Une fois que vous l’avez configuré, vous pouvez appliquer des transformations supplémentaires comme vous le feriez avec n’importe quelle source de données importée unique, puis actualiser les données pour afficher les résultats pour chaque mois.

Vue d’ensemble conceptuelle de la combinaison de fichiers de dossiers

Remarque Cette rubrique montre comment combiner des fichiers à partir d’un dossier. Vous pouvez également combiner des fichiers stockés dans SharePoint, Stockage Blob Azure et Azure Data Lake Storage. Le processus est similaire.

Versions plus récentes Office 2013

Restez simple :

Assurez-vous que tous les fichiers que vous souhaitez combiner sont contenus dans un dossier dédié sans fichiers superflus. Sinon, tous les fichiers du dossier et les sous-dossiers que vous sélectionnez sont inclus dans les données à combiner.
Chaque fichier doit avoir le même schéma avec des en-têtes de colonne, des types de données et un nombre de colonnes cohérents. Les colonnes ne doivent pas être dans le même ordre que la mise en correspondance est effectuée par des noms de colonnes.
Si possible, évitez les objets de données non liés pour les sources de données qui peuvent avoir plusieurs objets de données, tels qu’un fichier JSON, un classeur Excel ou une base de données Access.

Chacun de ces fichiers suit un modèle simple, une seule table de données dans chaque fichier.

Sélectionnez Données > Obtenir des données > à partir d’un fichier > à partir d’un dossier. La boîte de dialogue Parcourir s’affiche.
Recherchez le dossier contenant les fichiers que vous souhaitez combiner.
Une liste des fichiers du dossier s’affiche dans la boîte de dialogue <chemin d’accès du dossier> boîte de dialogue. Vérifiez que tous les fichiers souhaités sont répertoriés.
Sélectionnez l’une des commandes en bas de la boîte de dialogue, par exemple Combiner > Combiner & Charger. Vous trouverez d’autres commandes décrites dans la section À propos de toutes ces commandes.
Si vous sélectionnez une commande Combiner , la boîte de dialogue Combiner des fichiers s’affiche. Pour modifier les paramètres de fichier, sélectionnez chaque fichier dans la zone Exemple de fichier , définissez l’origine du fichier, le délimiteur et la détection du type de données comme vous le souhaitez. Vous pouvez également cocher ou décochez la case Ignorer les fichiers avec des erreurs en bas de la boîte de dialogue.
Sélectionnez OK.

Result (Résultat)

Power Query crée automatiquement des requêtes pour consolider les données de chaque fichier dans une feuille de calcul. Les étapes de requête et les colonnes créées dépendent de la commande que vous choisissez. Pour plus d’informations, consultez la section À propos de toutes ces requêtes.

Sélectionnez Données > Obtenir des données > à partir d’un fichier > à partir d’un dossier. La boîte de dialogue Parcourir s’affiche.
Recherchez le dossier contenant les fichiers que vous souhaitez combiner.
Une liste des fichiers du dossier s’affiche dans la boîte de dialogue <chemin d’accès du dossier> boîte de dialogue. Vérifiez que tous les fichiers souhaités sont répertoriés.
Sélectionnez l’une des commandes en bas de la boîte de dialogue, par exemple Combiner > Combiner & Transformer. Vous trouverez d’autres commandes décrites dans la section À propos de toutes ces commandes.

Le Éditeur Power Query s’affiche.
La colonne Valeur est une colonne List structurée. Sélectionnez l’icône Développer , puis sélectionnez Développer vers de nouvelles lignes.
La colonne Valeur est désormais une colonne Record structurée. Sélectionnez l’icône Développer . Une boîte de dialogue déroulante s’affiche.
Conservez toutes les colonnes sélectionnées. Vous pouvez effacer la zone Utiliser le nom de colonne d’origine comme préfixe case activée. Sélectionnez OK.
Sélectionnez toutes les colonnes qui contiennent des valeurs de données. Sélectionnez Accueil, la flèche en regard de Supprimer les colonnes, puis supprimer d’autres colonnes.
Sélectionnez Accueil > Fermer & Charger.

Result (Résultat)

Chacune de ces sources de données peut avoir plusieurs objets à importer. Un classeur Excel peut avoir plusieurs feuilles de calcul, tableaux Excel ou plages nommées. Une base de données Access peut avoir plusieurs tables et requêtes.

Sélectionnez Données > Obtenir des données > à partir d’un fichier > à partir d’un dossier. La boîte de dialogue Parcourir s’affiche.
Recherchez le dossier contenant les fichiers que vous souhaitez combiner.
Une liste des fichiers du dossier s’affiche dans la boîte de dialogue <chemin d’accès du dossier> boîte de dialogue. Vérifiez que tous les fichiers souhaités sont répertoriés.
Sélectionnez l’une des commandes en bas de la boîte de dialogue, par exemple Combiner > Combiner & Charger. Vous trouverez d’autres commandes décrites dans la section À propos de toutes ces commandes.
Dans la boîte de dialogue Combiner des fichiers :
- Dans la zone Exemple de fichier , sélectionnez un fichier à utiliser comme exemple de données pour créer les requêtes. Vous ne pouvez pas sélectionner un objet ou sélectionner un seul objet. Mais vous ne pouvez pas en sélectionner plusieurs.
- Si vous avez de nombreux objets, utilisez la zone Rechercher pour localiser un objet ou les options d’affichage , ainsi que le bouton Actualiser pour filtrer la liste.
- Cochez ou décochez la case Ignorer les fichiers avec des erreurs en bas de la boîte de dialogue.
Sélectionnez OK.

Result (Résultat)

Power Query crée automatiquement une requête pour consolider les données de chaque fichier dans une feuille de calcul. Les étapes de requête et les colonnes créées dépendent de la commande que vous choisissez. Pour plus d’informations, consultez la section À propos de toutes ces requêtes.

Pour plus de flexibilité, vous pouvez combiner explicitement des fichiers dans le Éditeur Power Query à l’aide de la commande Combiner des fichiers. Supposons que le dossier source comporte un mélange de types de fichiers et de sous-dossiers, et que vous souhaitez cibler des fichiers spécifiques avec le même type de fichier et le même schéma, mais pas d’autres. Cela peut améliorer les performances et simplifier vos transformations.

Sélectionnez Données > Obtenir des données > à partir d’un fichier > à partir d’un dossier. La boîte de dialogue Parcourir s’affiche.
Recherchez le dossier contenant les fichiers que vous souhaitez combiner, puis sélectionnez Ouvrir.
La liste de tous les fichiers du dossier et des sous-dossiers s’affiche dans la boîte de dialogue >chemin d’accès du dossier< . Vérifiez que tous les fichiers souhaités sont répertoriés.
Sélectionnez Transformer les données en bas. Le Éditeur Power Query s’ouvre et affiche tous les fichiers du dossier et tous les sous-dossiers.
Pour sélectionner les fichiers souhaités, filtrez les colonnes, telles que Extension ou Chemin d’accès au dossier.
Pour combiner les fichiers en une seule table, sélectionnez la colonne Contenu qui contient chaque fichier binaire (généralement la première colonne), puis sélectionnez Accueil > Combiner des fichiers. La boîte de dialogue Combiner des fichiers s’affiche.
Power Query analyse un exemple de fichier, par défaut le premier fichier de la liste, pour utiliser le connecteur approprié et identifier les colonnes correspondantes.

Pour utiliser un autre fichier pour l’exemple de fichier, sélectionnez-le dans la liste déroulante Exemple de fichier .
Si vous le souhaitez, en bas, sélectionnez Ignorer les fichiers avec des erreurspour exclure ces fichiers du résultat.
Sélectionnez OK.

Result (Résultat)

Vous pouvez sélectionner plusieurs commandes et chacune a un objectif différent.

Combiner et transformer des données Pour combiner tous les fichiers avec une requête, puis lancer le Éditeur Power Query, sélectionnez Combiner > Combiner et transformer des données.
Combiner et charger Pour afficher la boîte de dialogue Exemple de fichier, créer une requête, puis charger dans la feuille de calcul, sélectionnez Combiner > Combiner et charger.
Combiner et charger dans Pour afficher la boîte de dialogue Exemple de fichier, créer une requête, puis afficher la boîte de dialogue Importer , sélectionnez Combiner > Combiner et charger dans.
Charge Pour créer une requête en une seule étape, puis charger dans une feuille de calcul, sélectionnez Charger > Charger.
Charger dans Pour créer une requête en une seule étape, puis afficher la boîte de dialogue Importer , sélectionnez Charger > Charger dans.
Transformer des donnéesPour créer une requête en une étape, puis lancer le Éditeur Power Query, sélectionnez Transformer les données.

Quelle que soit la combinaison de fichiers, plusieurs requêtes de prise en charge sont créées dans le volet Requêtes sous le groupe « Requêtes d’assistance ».

Liste des requêtes créées dans le volet Requêtes

Power Query crée une requête « Exemple de fichier » basée sur l’exemple de requête.
Une requête de fonction « Transformer le fichier » utilise la requête « Parameter1 » pour spécifier chaque fichier (ou binaire) comme entrée de la requête « Exemple de fichier ». Cette requête crée également la colonne Content contenant le contenu du fichier et développe automatiquement la colonne d’enregistrement structurée pour ajouter les données de colonne aux résultats. Les requêtes « Transformer le fichier » et « Exemple de fichier » sont liées, de sorte que les modifications apportées à la requête « Exemple de fichier » soient répercutées dans la requête « Transformer le fichier ».
La requête contenant les résultats finaux se trouve dans le groupe « Autres requêtes ». Par défaut, il est nommé d’après le dossier à partir duquel vous avez importé les fichiers.

Pour plus d’informations, cliquez avec le bouton droit sur chaque requête et sélectionnez Modifier pour examiner chaque étape de requête et voir comment les requêtes fonctionnent de concert.

Pour démarrer le processus de combinaison de plusieurs fichiers, vérifiez d’abord que tous les fichiers que vous souhaitez combiner sont contenus dans un seul dossier de fichiers.

Remarque : Les fichiers Excel, Texte, CSV, JSON, XML et Access sont pris en charge.

Cliquez sur Power Query onglet, puis sur À partir d’un fichier > à partir d’un dossier.
Cliquez sur le bouton Parcourir pour localiser votre dossier.

Une liste des fichiers du dossier s’affiche. Sélectionnez l’option Modifier pour ouvrir le Éditeur de requête. Dans ce cas, nous avons chargé 4 classeurs Excel.
Une fois que vous avez vérifié que tous les fichiers souhaités sont répertoriés, cliquez sur le bouton Combiner des fichiers binaires dans la colonne Contenu .
Chaque fichier est analysé et le format de fichier correct est déterminé, tel que texte, Excel ou JSON. Dans ce cas, une liste de feuilles de calcul du premier classeur Excel s’affiche. Sélectionnez la feuille de calcul que vous souhaitez utiliser, puis cliquez sur OK.
Le processus de transformation combiner les fichiers binaires crée automatiquement une requête pour consolider les données de chaque fichier et afficher un aperçu. Si vous êtes satisfait du résultat, cliquez sur Fermer & Charger.

Le processus de combinaison de fichiers binaires consolidera ensuite les feuilles de calcul répertoriées dans une seule feuille de calcul.
Si les fichiers de données d’origine changent, vous pouvez toujours actualiser les données importées. Cliquez n’importe où dans la plage de données, puis accédez à Outils de requête > Actualiser. En outre, vous pouvez facilement appliquer des étapes de transformation ou d’extraction supplémentaires en modifiant la requête exemplaire créée automatiquement, sans avoir à vous soucier de modifier ou de créer des étapes de requête de fonction supplémentaires ; toutes les modifications apportées à la requête exemple sont générées automatiquement dans la requête de fonction liée.

Problèmes connus

Lorsque vous chargez la procédure combiner des fichiers binaires dans Excel, le message d’erreur suivant peut s’afficher :

Message d’erreur Combiner les fichiers binaires. Il s’agit d’un bogue connu qui est résolu.

Si vous ignorez la ou les erreurs en cliquant sur OK, le processus de combinaison de fichiers binaires se termine et charge correctement vos données en dépit de l’erreur. Nous travaillons actuellement à la correction de l’erreur.