Avec Power Query, il est très facile d'importer plusieurs fichiers Excel en un seul.
Ici, nous allons traiter un cas d'importation de plusieurs fichiers textes, mais ça fonctionne aussi avec des csv ou Excel. Si vous ne connaissez pas bien Power Query, vous trouverez à la fin de cet article un lien vers Udemy pour y suivre une formation.
Plusieurs fichiers de même structure
Dans un répertoire nous avons plusieurs fichiers, comme le montre l'image ci-dessous.
Tous ces fichiers ont exactement la même structure.
Nous voulons fusionner tous ces fichiers en un seul. L'opération est fort simple.
Étape 1 : Importer les fichiers d'un répertoire
Ouvrir le menu
- Données
- Obtenir des données
- À partir d'un fichier
- À partir d'un dossier
Étape 2 : Chemin d'accès à traiter
Dans la boîte de dialogue suivante, indiquer le chemin d'accès du répertoire à traiter.
Étape 3 : Ouvrir les données dans Power Query
Ensuite, il faut ouvrir Power Query en cliquant sur le bouton Transformer les données
Power Query est ouvert et nous voyons tous les fichiers contenus dans le répertoire avec des détails des fichiers
- Nom
- Extension
- Date d'accès
- Date de création
- Date de modification
- Le chemin
- Et ....... Le contenu en toute première position (nous y reviendrons)
Étape 4 : (Facultatif) Appliquer des filtres
Dans notre cas de figure, le répertoire contient uniquement tous les fichiers que vous souhaitons fusionner. Mais vous pouvez parfaitement appliquer des filtres pour exclure certains fichiers.
Filtrer sur l'extension
Si par exemple, votre répertoire contient plusieurs type de fichiers (Excel, csv, ...), vous pouvez très facilement ne conserver qu'un seul type d'extension en appliquant un filtre sur la colonne.
Ou aussi, vous pouvez faire un clic-droit sur le type d'extension à conserver en :
- Sélectionnant le type d'extension.
- Faire un clic-droit.
- Choisir Filtres textuels.
- Puis Est égal à.
Filtre sur le répertoire
De la même façon, il est préférable d'appliquer un filtre sur la colonne Folder Path (Chemin d'accès).
En effet, si le répertoire indiqué à l'étape 2 a des sous-répertoires, l'outil d'importation des fichiers affiche tous les fichiers des dossiers et sous-dossiers.
Pour s'assurer de ne traiter QUE les fichiers du bon répertoire, là aussi il est préférable de filtrer la colonne uniquement sur le nom du dossier à traiter.
Étape 5 : Ne conserver que la colonne "Content"
En fait, les colonnes qui affichent le détail des fichiers ne sont utiles que pour effectuer les filtrages. Une fois que vous avez effectué les filtres nécessaires à votre projet, il faut ne conserver que la colonne Content.
Pour cela, vous faites :
- Un clic-droit sur l'en-tête de la colonne Content.
- Sélectionnez, Supprimer les autres colonnes.
Étape 6 : Déployer les fichiers
Dans la seule colonne qu'il nous reste, vous voyez dans l'en-tête, une icône avec une double-flèche. Cette icône c'est l'icône de déploiement de tous les fichiers.
Immédiatement, une boîte de dialogue vous propose une solution de découpage des colonnes. Seulement, dans notre exemple, Power Query n'a pas compris que le séparateur de colonnes était la tabulation.
Nous avons 2 modifications à réaliser ici :
- Changer le type de délimiteur.
- Utiliser l'encodage UTF-8 (pas obligatoire mais c'est celui qui interprète le mieux les caractères français).
- Valider en appuyant sur le bouton OK.
Power Query s'ouvre avec tous les fichiers fusionnés. ???????? Remarquez le nombre de requêtes qui ont été automatiquement générées. Il n'est pas recommandé de modifier quoi que ce soit dans ces requêtes.
Étape 7 : Apporter quelques transformations aux données
Nous pourrions les transférer telles quelles dans Excel mais il reste 2 modifications à effectuer avant que tout ne soit parfait.
Première ligne en ligne d'en-tête
Tout d'abord, nous allons indiquer que la première ligne de nos données constitue les en-têtes de colonnes avec le menu Transformer > Utiliser la première ligne pour les en-têtes.
De plus, cette action détermine le type de données contenues dans les colonnes. Dans la barre d'en-têtes, chaque type de données est identifié par un pictogramme.
Retirer les autres premières lignes
- Comme chacun des fichiers étaient construits de la même façon, chacun a donc une première ligne d'en-têtes similaire.
- Nous avons utilisé la première pour l'en-tête globale de notre document fusionné, mais il en reste 8 autres dans notre cas.
- Pour les supprimer, il suffit de filtrer la première colonne et de décocher la valeur ticker dans notre exemple.
Étape 8 : Charger dans Excel
Le travail est maintenant terminé. Il ne reste plus qu'à charger les données dans Excel (Accueil > Fermer et Charger)
Les données sont transférées dans Excel et on peut voir le nombre de lignes au total après fusion des 9 fichiers texte.
Formation Power Query
Power Query est l'outil moderne d'importation et de transformation des données. Son utilisation est très simple et ne nécessite pas de connaissance avancé d'Excel ni de connaissance en programmation.
Si vous voulez vous former à l'utilisation de Power Query, voici ma formation sur Udemy très simple d'accès. Vous y trouverez cet exemple comme support de cours.
Cedric
06/08/2024 @ 10:11
Que faire quand les données sont dans un onglet "masqué"?
Frédéric LE GUEN
06/08/2024 @ 10:18
Vous ne pouvez pas les rendre visibles avant de faire la manipulation ?
FRANCK FARDEAU
24/09/2021 @ 09:43
Bonjour
Dans power query, pouvons-nous ajouter depuis une colonne plusieurs autres colonne avec comme délimiteur le retour chariot?
Frédéric LE GUEN
24/09/2021 @ 09:48
Je ne comprends pas bien la question. Vous avez une colonne avec des données séparées par des sauts de lignes c'est bien ça. Et vous voulez-voulez réordonner le contenu d'une cellule en plusieurs colonnes ? Si c'est le cas, voici le lien de la vidéo https://youtu.be/uhZz8FucCAU
Charlotte
23/04/2021 @ 10:11
Bonjour, Merci beaucoup pour votre présentation. Mais est-il possible de joindre les fichier non pas les uns après les autres (vertical) mais en bloc de colonne ?
Frédéric LE GUEN
23/04/2021 @ 10:18
Ce n'est pas le but, c'est mieux d'avoir toutes les données groupées dans une seule colonne. C'est le principe même d'une bonne utilisation d'Excel. Après avec un tableau croisé dynamique, il est facile de présenter les données comme bon vous semble
John
08/03/2021 @ 14:11
Bonjour,
Est-il possible d'ajouter dans la requête, le chemin des dossiers et sous dossiers pour chacune des lignes des fichiers excel ?
Merci.
Frédéric LE GUEN
08/03/2021 @ 15:09
Oui, il suffit de conserver cette information au lieu de la supprimer
cdo
05/03/2021 @ 22:10
Est il possible par le biais de cette méthode de ne pas fusionner les fichiers et de les importer avec une feuille par fichier avec le nom associé? Merci
Frédéric LE GUEN
07/03/2021 @ 18:54
Difficile de répondre juste comme ça sans voir le contexte. Mais ça doit être possible.
Romain
16/12/2020 @ 16:48
Bonjour,
Est il possible lors de l'import depuis un dossier de récupérer le titre des fichiers.
Je parle bien ici du titre du fichier que l'on trouve en faisant un clic droit sur le fichier puis propriétés et détail
Cordialement
Frédéric LE GUEN
17/12/2020 @ 16:44
C'est possible mais c'est compliqué. Je ne sais personnellement pas le faire