Pour extraire l'arborescence d'un dossier avec les détails de tous les fichiers, il faut procéder de la façon suivante
- Connecter un dossier à Power Query
Power Query c'est l'outil qui simplie le travail sur la manipulation et l'importation des données (formation complète ici)
- Conserver les colonnes utiles
Power Query ramène énormément d'informations, mais seules certaines sont à conserver
- Déployer les attributs des fichiers
Pour obtenir des informations complémentaires, comme la taille des fichiers, il faut déployer la colonne Attribut
Utilisation de Power Query
Pour ce travail, nous allons utiliser l'outil Power Query. Il faut donc que vous ayez au minimum Excel 2013. Une fois l'extraction terminée, vous pourrez utiliser les graphiques en rayons de soleil ou compartimentage pour représenter la hiérarchie dans vos dossiers.
Étape 1 : Se connecter à un répertoire
- Menu Données
- Cliquez sur Obtenir des données
- Puis À partir d'un fichier
- Et enfin, À partir d'un dossier
- Dans la boîte de dialogue, cliquez sur l'icône Parcourir ...
- Dans la fenêtre de sélection du dossier, choisissez celui dont vous voulez extraire l'arborescence.
Votre dossier est prêt à être chargé dans Power Query
- Cliquez sur OK
- Et enfin sur Modifier.
Ensuite, le résultat se charge dans Power Query.
Étape 2 : Ajouter la taille de vos fichiers
Power Query affiche beaucoup de détails sur les fichiers mais la taille des fichiers n'est pas visible. Du moins, pas pour l'instant.
La colonne Attributes contient des données essentielles que nous allons afficher.
1. Cliquez sur la double-flèche de la colonne Attributes
2. Sélectionnez seulement la case Size (Taille de vos fichiers en français)
Étape 3 : Conserver seulement 3 colonnes
Seules 3 colonnes nous intéressent dans l'importation des répertoires avec Power Query
- Le chemin
- Le nom des fichiers
- La taille
L'ordre de sélection est très important dans Power Query. Sélectionnez dans cet ordre
- La colonne Folder Path
- Puis la colonne Name (avec la touche Ctrl)
- Et enfin la colonne Size
- Faites un clic-droit sur les en-têtes
- Sélectionnez Supprimer les autres colonnes
Non seulement les autres colonnes sont supprimées, mais en plus, les colonnes sont réordonnées en respectant la sélection.
Étape 4 : Renommer les colonnes
Cette étape n'est pas indispensable mais pour les francophones c'est mieux ????.
- Double-cliquez dans la colonne Folder Path et écrivez Chemin.
- Double-cliquez dans la colonne Name et écrivez Nom.
- Double-cliquez dans la colonne Size et écrivez Taille.
Et c'est déjà terminé ????????????. 4 étapes ont suffi pour extraire la liste des fichiers, leurs tailles et de leurs répertoires.
Les étapes qui suivent ne sont pas nécessaires mais vont être utiles pour la création de graphiques Rayons de soleil ou Compartimentage
Étape 5 : Supprimer les chemins communs
Comme Power Query a ramené le chemin en entier, nous allons retirer le chemin commun à tous les fichiers. C'est la chaîne de l'étape 1.
- Faites un clic-droit sur la colonne Chemin
- Sélectionnez l'option Remplacer les valeurs
- Saisissez la chaîne à rechercher (ici C:\Users\fred_\OneDrive\20_EXCEL\)
- Laissez vide la chaîne de remplacement
Étape 6 : Séparer les sous-répertoires
Dans n'importe quel language informatique cette étape est un enfer. ???????? Mais dans Power Query, cela ce fait en un clic ????????
- Sélectionner la colonne Chemin
- Clic-droit sur l'en-tête de colonne Chemin
- Fractionner la colonne
- Par délimiteur ...
Là, vous indiquez que le délimiteur c'est
- Option Personnalisée
- Symbole \
- Choisissez l'option Chaque occurrence du délimiteur
Et vous avez maintenant tous les niveaux hiérarchiques depuis votre répertoire initial.
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ée d'Excel ni de connaissance en programmation.
Si vous voulez vous former à l'utilisation de Power Query, je vous ai conçu cette formation sur Udemy très simple d'accès (la vidéo de cet exemple est gratuite)
PIGE
Frédéric LE GUEN
aude
Miren
FOA
GUYOT
YJL
ROUVIERE
O2LaMontagne
Videlier Muriel
Fanny Cheminan
ScoobyDoos
Marion GAIGNARD
David
David
Yza
Erick
Benjamin
Julien
Vigreux julien
John
Romain
FRANCAIS
Renaud
Stéphane
OLIVIER HOUPIN
Nieto
20/12/2022 @ 10:46
Bonjour
question "bête" la size (taille) est exprimé en quoi ?
20/12/2022 @ 11:09
En bites (1 Mo = 1024 bites)
08/09/2022 @ 11:44
Bonjour,
Je ne connaissais pas, c'est génial!
Par contre, impossible d'afficher la date de création contenu. C'est possible?
Merci
21/09/2022 @ 18:10
Dans la colonne "Record" vous avez cette information.
28/07/2022 @ 14:21
Bonjour,
Je dois faire du tri dans un serveur alors merci pour ce tuto très pratique, je ne savais pas que ça existait, ça va changer ma vie je le sens 😉
En revanche, je suis confrontée à un problème particulier :
[DataFormat.Error] Le nom de fichier est plus long que la limite maximale définie par le système.
=> je ne peux pas renommer le fichier car il est lié dans un outil autre
connaissez vous une possibilité de contourner cette problématique ?
Merci par avance de votre retour
Bien cordialement,
Miren
29/07/2022 @ 21:46
Merci pour le commentaire très positif.
Mais pour votre erreur, je ne peux pas vous aider ni sans voir la situation ni connaitre le contexte
18/08/2022 @ 14:40
Bonjour,
le contexte : je dois analyser le "poids" d'une serveur sur lequel sont stockés des fichiers dont les noms sont créés automatiquement via des concaténation (code + libellé) dans un outil de PLM => les noms de fichiers peuvent donc être parfois très longs, a priori trop longs par rapport aux limites du système
dans mon exemple : DataFormat.Error : Le nom de fichier « Z:\Sites\simulation\documentLibrary\Produit fini\Patisserie\041688UC MADELEINE SURGELÉE FOURRÉE À LA PÂTE À TARTINER CHOCOLAT ET NOISETTES 56G\Documents » est plus long que la limite maximale définie par le système
=> dans ce cas, l'extraction semble se terminer en erreur et les données suivantes ne sont pas traitées. par ailleurs, le résultat n'affiche que 30 lignes dans Excel, même s'il a pu traiter jusqu'à la ligne 118.
je ne sais pas s'il existe un moyen d'ignorer les erreurs : Power Query ne traiterait pas les lignes en erreur mais enverrai toutes les autres (ça me ferait déjà de quoi travailler je pense 😉
Merci de votre retour si vous avez une piste.
nota : je l'ai utilisé sur une arborescence plus simple et avec des noms de fichiers "normaux" et ça a fonctionné à la perfection 🙂
19/07/2022 @ 17:24
Super Tuto, Bravo!
Comment faire si je ne veux pas descendre jusqu'au dernier niveau (niveau fichier) d'une arborescence très chargée svp?
Je cherche à m’arrêter au niveau 2 des dossiers avec leur nom, chemin et taille + les fichiers qui seraient aussi présents à ce niveau hiérarchique.
Merci 😉
22/07/2022 @ 21:55
Dans ce cas, il faut changer les options de la boite de dialogue de séparation. Il y est ainsi possible de ne pas faire le découpage sur tous les /
07/07/2022 @ 14:24
Bonjour, super tuto
Est-il possible d'ajouter une colonne "Durée" ?
Merci de vos explications
Bernard
20/05/2022 @ 11:22
Bonjour, super tuto ! Merci !
Mais j'obtiens toujours un message d'erreur : "Evaluation ran out of memory and can't continue."
Je ne fais pourtant le test que sur une petite arborescence de 70 fichiers et je n'en obtiens que 35 après le "Load" alors que je les vois bien tous dans la fenêtre "Power Query Editor" ...
Quelqu'un aurait-il une idée ?
Merci
20/05/2022 @ 13:53
Aucune idée
18/05/2022 @ 15:06
Bonjour,
Excellent tutoriel sur la fonctionnalité de Power Query.
En revanche, comment faire pour obtenir la liste complète?
J'ai un dossier sur le réseau qui contient environ 60 000 fichiers.
Lorsque je lance la requête je n'ai que 400 fichiers en cliquant sur Fermer et charger et un message d'erreur dans le volet de droite me disant que la liste est trop longue et que la mémoire est trop chargée... 32Go tout de même sur le PC.
Si je reste longtemps... appuyé sur CTRL + pagedown, il charge la suite des lignes dans la requête mais 50 par 50.
Un copier coller dans un éditeur de texte me donne la liste complète (je ne suis pas allé jusqu'à 60 000 mais 12 250) ensuite une ouverture du fichier TXT sous Excel permet de mettre en forme et trier/filtrer.
Je suis preneur de toute solution .
Merci d'avance.
12/05/2022 @ 11:05
Bonjour,
Vraiment excellent ce tuto.
Merci beaucoup.
12/05/2022 @ 11:15
Merci beaucoup
28/04/2022 @ 14:44
Merci pour ce tuto, qui vient de m'épargner beaucoup de temps pour avoir une liste de fichiers contenus dans un dossier.
Je vais le partager avec ceux qui ne connaissent pas.
28/04/2022 @ 16:01
Merci beaucoup pour ce commentaire 😉
03/01/2022 @ 09:38
Bonjour, merci beaucoup pour ce tutoriel ! Savez-vous s'il est possible d'insérer le lien hypertexte des documents ?
16/11/2021 @ 08:36
Bonjour,
Tuto nickel, cela évite bien des complications.
Cependant, j'aimerais avoir uniquement les noms des répertoires et non tout le reste (sous-dossier, fichiers, etc....)
Est-ce possible ?
Merci par avance
Cordialement
16/11/2021 @ 12:49
Oui, il suffit d'appliquer un filtre avec les critères que vous voulez
05/11/2021 @ 17:48
Bonjour, merci beaucoup c'est très clair !
J'ai une question cependant : je dois realiser la cartographie d'un dossier, en ajoutant les noms des differents auteurs des fichiers de ce dossier.
Les noms se trouvent sous "proprietes"- "details" -"origines"- "auteurs" mais comment ajouter cette information directement dans une colonne en face de chaque fichier avec Power query ?
05/11/2021 @ 20:20
Non, ça ne marche pas pour les propriétés du fichier
20/10/2021 @ 10:10
Merci pour ce tuto très utile. Sauriez vous comment contourner les problèmes qui aboutissent à l'arrêt du chargement de la requête et au message d'erreur :"[DataSource.NotFound]" ? Bien cordialement.
20/10/2021 @ 10:31
Bonjour,
Non pas du tout. C'est assez curieux comme erreur d'ailleurs
31/12/2021 @ 12:44
Bonjour, je pense que ça peut arriver par une erreur de lecture, liste trop longue, caractères non standard ( cyrilliques par exemple ), qu'en pensez-vous ?
Sinon travail très intéressant !
13/08/2021 @ 09:06
Bonjour !
Merciiiiiiii ! J'ai encore appris quelque chose.! J'avais l'impression de bien connaitre Excel, jusqu'à ce que je découvre ce site, et particulièrement l'utilisation de Power Query.
Quel gain de temps mais surtout quelle facilité d'utilisation...
Pour ma part cette technique fonctionne également très bien sur un serveur même si c'est plus lent qu'en local.
Je vous ai ajouté à mes favoris car vos explications sont claires, détaillées et accessibles .
Encore merci .
13/08/2021 @ 13:34
On en a apprend tous les jours avec Excel et même après 25 ans à travailler avec tous les jours 😉
Power Query c'est vraiment fantastique. Une fois qu'on a commencé à travailler avec, on ne peut plus s'en passer.
Merci pour les commentaires positifs. Je publie régulièrement sur le site les dernières innovations apportées à Excel comme Power Query ou les fonctions matricielles dynamiques (FILTRE, UNIQUE, SEQUENCE, ...) ou encore LAMBDA
03/06/2021 @ 22:50
Je découvre ce power query... un bien bel outil. Est-il possible d'obtenir les informations de l'utilisateur du fichier : le propriétaire du fichier me conviendrait, bien que je préfèrerais l'information du dernier utilisateur à avoir accédé au fichier ?
04/06/2021 @ 09:16
Pas à ma connaissance. Peut-être sur un site anglais
18/05/2021 @ 13:43
Bonjour,
Merci merci merci
18/05/2021 @ 17:54
De rien (si ça vous aide, c'est cool)
27/03/2021 @ 11:13
Bonjour, merci beaucoup pour cet article, çà marche effectivement en réseau local. En revanche lorsque je test à partir d'un document basé sur serveur, j'ai un message d'erreur qui s'affiche. Dois-je comprendre que cette manipulation fonctionne uniquement pour dossier en local?
Cordialement
27/03/2021 @ 11:32
Bonjour, Merci pour le commentaire positifs. Si cette technique fonctionne sur un serveur, je n'en sais strictement rien. Par contre, vous pouvez lire cet article que j'ai terminé cette semaine sur la gestion des niveaux de confidentialités. Peut-être que ça va solutionner votre problème. Si ça fonctionne, merci de l'indiquer en commentaire.
07/05/2021 @ 10:31
Bonjour,
Intervention un peu tardive par rapport à la question, mais je découvre l'outil et du coup le même problème sur notre réseau informatique. J'ai réussi à éviter cette erreur en créant la requête en local (J'ai copié quelques fichiers localement). Dans un second temps, j'ai modifié le chemin cible des données pour pointer vers le répertoire réseau. Cela a fonctionné, les données se sont bien mises à jour.
Merci pour votre article M. LE GUEN!
Julien
08/03/2021 @ 14:30
Bonjour,
Est-il possible de visualiser par ligne, dans chaque sous dossier de l'arborescence le nom du fichier excel et les colonnes de chaque fichier Excel ?
08/03/2021 @ 15:24
Ben c'est déjà le cas
11/12/2020 @ 15:29
Bonjour
Est il possible de récupérer le titre d'un fichier dans query? Attention je parle bien du champ titre et non du champ nom du fichier
Cordialeme,t
18/11/2020 @ 11:36
Bonjour, Cet exemple est parfait , et fonctionne très bien.
Mais , les repertoires vide sont exclus de la liste ontenue. Pourquoi? Que faire pour les obtenir aussi?
10/11/2020 @ 11:55
Bon tuto, mais si vous avez des dossiers vides (sans fichier), ils n'apparaitront pas dans le tableau de données.
Est-ce qu'il y a une solution à ce problème ?
11/11/2020 @ 13:32
Bonjour
Effectivement, avec cette technique, les dossiers vides ne sont pas détectés.
Vous pouvez utiliser l'ancienne technique DOS avec la commande suivante DIR *.* /o/s ou encore DIR "C:\users\moi\mes dossiers\*.*" /o/s
Mais après, le traitement des données est une vraie galère
Pour ouvrir la console DOS il faut taper "cmd" dans la zone de recherche windows
30/10/2020 @ 16:00
Très pratique, extrêmement clair. Un immense merci pour votre aide. Je sais à présent comment procéder et j'en suis ravie.
01/10/2020 @ 19:25
Bonjour,
Mille mercis pour ceci, ceci m'aide beaucoup pour créer des index de dataroom....
Juste une petite question : auriez-vous une idée ou une piste de réflexion pour arriver à indiquer le nombre de pages que feraient chaque document. La plupart du temps, ce sont dans mon cas des pdf...
D'avance merci pour votre retour et bravo pour vos posts...
Bonne soirée
16/07/2019 @ 12:50
Super !
J'avais l'impression de bien connaitre Excel, jusqu'à ce que je découvre ce site...
J'ai encore appris quelque chose.
Notamment concernant l'utilisation de Power Query.
Une petite remarque pourra être utile à d'autres lecteurs équipés comme moi d'Office 2016. Vous avez écrit - en vous référant je pense à 2013 :
Menu Données ok
Cliquez sur Obtenir des données en fait sous 2016 il faut chercher "Nouvelle Requête"
Puis A partir d'un fichier ok
Et enfin A partir d'un dossier ok
Sinon encore merci
16/07/2019 @ 14:19
Héhé 🙂 Power Query est un outil remarquable qui a maintenant 3 ou 4 ans d'existence et qui va remplacer petit à petit des manipulations qui demandaient beaucoup de temps. Je vais essayer de faire plus d'articles mais je manque de temps actuellement.
Merci pour votre retour sur le contenu du site. Je suis actuellement le dernier MVP Microsoft Excel francophone qui soit en activité et donc il n'y a que sur ce site que vous verrez les articles sur les dernières nouveautés d'Excel. Soyez à l'écoute à la rentrée de Septembre pour les nouveautés de la rentrée 🙂
Le meilleur moyen pour avoir les alertes sur les nouveautés c'est de souscrire à ma page Facebook. Sinon, vous pouvez toujours partager les liens des articles auprès de vos contacts ou réseau pour diffuser les trucs et astuces.
25/07/2022 @ 13:43
Bonjour,
Monsieur Le Guen, je tenais à vous remercier pour cet article clair et concis qui m'a aidé beaucoup à comprendre comment exporter une arborescence complète d'un répertoire.
Merci aussi pour chaque commentaire fait au sujet.
En suivant les étapes, l'export, en réseau, d'une très profonde arborescence d'un répertoire a été un succès !
Merci encore 🙂
Bien à vous tous,
F.