Lister tous les fichiers d'un dossier dans Excel à l'aide de Power Query - Conseils Excel

Table des matières

Regarder la vidéo

La question du jour: Marcia a besoin d'obtenir une liste de tous les fichiers PDF de la facture fiscale d'un dossier dans une feuille de calcul Excel. C'est facile à faire si vous utilisez Excel 2016 sur un PC Windows à l'aide des nouveaux outils Obtenir et transformer des données.

Si vous disposez d'Excel 2010 pour Windows ou d'Excel 2013 pour Windows, vous devrez télécharger le complément gratuit Power Query de Microsoft. Accédez à votre moteur de recherche préféré et tapez "Télécharger Power Query" pour trouver le lien actuel. (Microsoft adore changer d'URL tous les trimestres et mon super web déteste quand nos liens sont obsolètes, donc je ne vais même pas essayer de mettre un lien ici.)

La vidéo ci-dessous vous montrera les étapes complètes, mais voici l'aperçu:

Commencer à partir d'une feuille de calcul vierge
Données, obtenir des données, à partir d'un fichier, à partir d'un dossier
Naviguez jusqu'au dossier
Cliquez sur Modifier au lieu de Charger
Ouvrez la liste déroulante des filtres sur le type de fichier et supprimez tout ce qui n'est pas un PDF
Ouvrez le filtre sur le dossier et supprimez tous les sous-dossiers de déchets
Conserver uniquement le nom de fichier et le dossier - cliquez avec le bouton droit sur chaque en-tête de colonne et choisissez Supprimer
Faites glisser l'en-tête du dossier vers la gauche de l'en-tête du fichier. Cela permet à la fusion de fonctionner.
Sélectionnez les deux colonnes. Cliquez sur un titre. Maj + clic sur l'autre en-tête.
Choisissez Ajouter une colonne, Fusionner les colonnes, Tapez un nouveau nom pour la colonne. Cliquez sur OK.
Cliquez avec le bouton droit de la souris sur l'en-tête de la nouvelle colonne et Supprimer les autres colonnes
Accueil, fermeture et chargement
La partie étonnante… vous pouvez actualiser la requête plus tard. Cliquez sur l'icône Actualiser dans le panneau Requêtes et connexions.

Bien que Power Query soit incroyablement puissant, c'est l'une de mes tâches préférées. Je souhaite fréquemment exécuter une macro VBA sur chaque fichier d'un dossier. Obtenir une liste de tous les PDF dans un dossier est un bon point de départ.

Regarder la vidéo

Transcription vidéo

Apprenez Excel à partir du podcast, épisode 2181 - Liste des fichiers de dossiers dans Excel!

Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui, quelqu'un a une liste de fichiers PDF de factures fiscales dans un dossier, et ils doivent obtenir la liste de tous ces noms de fichiers dans Excel. D'accord, et une façon de le faire est de les taper tous ou de les copier et coller à partir de l'Explorateur Windows, mais il existe un excellent outil qui peut résoudre ce problème. Et ma première question était "Quelle est la version d'Excel que vous utilisez?" Parce que si vous avez Excel 2016, ils auront cette nouvelle fonctionnalité étonnante appelée «Obtenir et transformer des données»! Maintenant, dans Office 365, c'est sur le côté gauche, je pense que dans la version originale d'Excel 2016, c'était dans le troisième groupe, d'accord, alors recherchez simplement Get & Transform. Si vous êtes dans Excel 2010 ou Excel 2013 pour Windows, vous pouvez télécharger Power Query et vous aurez votre propre onglet avec ces éléments précis.

Maintenant, jetons un coup d'œil à ce dossier, d'accord, je viens de créer un faux dossier ici avec de fausses données. Vous verrez qu'il y a des fichiers Excel dans ce dossier et des PDF, je ne veux que les PDF, et il y a aussi des sous-dossiers, je ne veux pas ces PDF, je veux seulement les PDF dans le dossier principal. Donc C: Budgets, je vais copier cela, puis revenir ici dans Excel, et nous allons dire que nous voulons obtenir des données, à partir d'un fichier, à partir d'un dossier entier, comme ceci, puis tapez le chemin du dossier là-dedans ou utilisez le bouton Parcourir, l'un ou l'autre. Et lorsque vous obtenez ce premier écran, vous voulez vraiment modifier, et nous sommes maintenant dans l'éditeur Power Query.

D'accord, donc mon objectif ici, je n'ai pas besoin du contenu, donc je vais faire un clic droit et dire Supprimer cette colonne. Il y a ma liste de fichiers, je ne veux que des fichiers PDF, donc s'il y a quelque chose qui n'est pas un PDF, je veux seulement des PDF, cliquez sur OK, je peux voir que ce sont juste les fichiers PDF. Ah, et puis regardez par ici, voyez, maintenant ils tirent des choses uniquement du dossier d'origine, et du dossier Garbage, alors j'ouvre ceci, et je décoche tout ce qui n'est pas le dossier d'origine. Bon, alors maintenant j'ai une jolie petite liste, et cette liste est, vous savez quoi, 9 disques, mais dans la vraie vie je parie qu'ils en ont probablement, vous savez, des dizaines ou des centaines de ceux-ci. D'accord, je n'ai pas besoin d'autre chose maintenant, je peux donc cliquer avec le bouton droit de la souris et supprimer ces colonnes.

Bon, maintenant ce dont j'ai vraiment besoin ici, c'est que j'ai besoin du chemin du dossier et du nom de fichier ensemble. D'accord, je vais donc prendre FolderPath et le faire glisser vers la gauche et le déposer là-bas, puis l'étape magique ici: dans Excel ordinaire, nous devrions faire une concaténation pour cela, mais ce que je vais faire est, je Je vais fusionner les colonnes. Je vais donc ajouter une colonne et choisir Fusionner les colonnes, le séparateur sera Aucun, la nouvelle colonne s'appellera FileName, puis cliquez sur OK, d'accord, nous avons donc le nom du dossier, la barre oblique et le nom du fichier , comme ça. Maintenant, c'est en fait la seule chose dont nous avons besoin, donc je vais faire un clic droit et dire Supprimer les autres colonnes, puis enfin Accueil, Fermer et charger, et nous obtenons une toute nouvelle feuille avec nos données. Très bien maintenant, il se présente sous forme de tableau, et je vais donc simplement copier ceci, Ctrl + C,puis venez ici où je voulais vraiment les données ici, et Coller les valeurs spéciales, cliquez sur OK. Maintenant, ce n'est plus une table, ce sont juste mes données pures, comme ça, et maintenant, voici la très belle chose à ce sujet.

Nous avons donc configuré cela une fois, et wow, cela a pris moins de 3 minutes à configurer, mais revenons à ce dossier Budgets et déplaçons quelques éléments. Prenons l'un de ces enregistrements Garbage, et nous le copierons dans le dossier principal, Ctrl + V, d'accord donc maintenant il y a plus de choses ici, il y a 10 fichiers PDF au lieu de 9. Si je viens ici où se trouve la requête, et sur le côté droit de l'écran, dans les requêtes et connexions, vous devrez peut-être élargir cela, j'ai déjà élargi le mien, vous verrez nos budgets avec 9 lignes chargées. Je vais cliquer sur la petite icône Actualiser ici, et très rapidement Budgets a maintenant 10 lignes chargées. Il s'agit donc de récupérer les nouveaux enregistrements, vous l'avez configuré une fois, puis vous pourrez simplement actualiser pour obtenir les nouvelles données.

Eh bien, c'est le point dans le podcast où je vous demande habituellement d'acheter mon livre, mais aujourd'hui, je vais vous demander d'acheter ce livre «M is for (DATA) MONKEY» de Ken Puls et Miguel Escobar. Un livre incroyable qui vous apprendra tout sur l'utilisation de Power Query ou de Get & Transform Data, tout ce que j'ai appris sur Power Query que j'ai appris dans ce livre.

OK, résume cet épisode: notre objectif est de savoir comment importer une liste de noms de fichiers dans Excel, si vous avez Excel 2016, vous pouvez utiliser le nouveau Get & Transform Data. Si vous n'avez pas 2016, mais que vous avez une vraie version d'Excel sous Windows, vous pouvez télécharger le complément gratuit Power Query pour Excel 2010 ou Excel 2013. Cela ne fonctionnera pas sur votre téléphone Android, ou votre iPad, votre iPhone, votre Surface RT ou votre Mac, c'est vrai, c'est uniquement pour les versions Windows d'Excel. Nous allons donc commencer à partir d'une feuille de calcul vierge, des données, obtenir des données, du fichier, du dossier, entrez le nom du dossier ou Parcourir, assurez-vous de cliquer sur Modifier au lieu de Charger. Et puis sur le filtre, filtrez sur le type de fichier pour se débarrasser de tout ce qui n'est pas un PDF, filtrez sur le nom du dossier pour se débarrasser de tous les sous-dossiers poubelles. Conservez uniquement le nom du fichier et le dossier,alors cliquez avec le bouton droit sur ces autres et dites Supprimer la colonne, puis faites glisser l'en-tête du dossier à gauche du fichier, ce qui permet à la fusion de fonctionner. Sélectionnez les deux colonnes, puis sur l'onglet Ajouter une colonne, choisissez Fusionner les colonnes, tapez un nouveau nom, cliquez sur OK, puis cliquez avec le bouton droit sur cette nouvelle colonne et supprimez les autres colonnes, Accueil, Fermer et charger, et cela vous donnera votre liste. La partie étonnante, vous pouvez actualiser la requête plus tard en utilisant cette icône Actualiser dans les requêtes et connexions.vous pouvez actualiser la requête plus tard en utilisant cette icône Actualiser dans les requêtes et connexions.vous pouvez actualiser la requête plus tard en utilisant cette icône Actualiser dans les requêtes et connexions.

Eh bien, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!