Ordnerstrukturen mit PowerQuery in Excel einlesen 7

Ein komplettes Verzeichnis mit PowerQuery in Excel einlesen und auswerten
 

PowerQuery entwickelt sich immer mehr zu einem persönlichen Favoriten von mir in Excel. Mit wenig Aufwand lassen sich hier Dinge erledigen, die vorher nicht oder nur sehr umständlich möglich waren.

Heute zeige ich dir, wie man damit den Inhalt eines Verzeichnisses auf deinem Computer ausliest, alle enthaltenen Dateinamen in sämtlichen Unterverzeichnissen in einer Excel-Tabelle auflisten und sich mit wenig Aufwand einen schnellen Überblick über die Speicherbelegung schaffen kann.

Und so geht’s:

PowerQuery – was ist das?

PowerQuery ist vereinfacht gesprochen eine Sammlung von mächtigen Abfragewerkzeugen, um fast beliebige Daten in Excel zu importieren und/oder zu transformieren.

Für Excel 2010 und 2013 gib es PowerQuery als Add-In, das man kostenlos bei Microsoft herunterladen kann. Seit Excel 2016 ist die Funktion bereits direkt im Programm enthalten und in der Registerkarte “Daten” in der Befehlsgruppe “Daten abrufen und transformieren” zu finden.

Wenn du noch keinerlei Erfahrung mit PowerQuery hast, empfehle ich dir zuerst diese beiden Blog-Artikel:
Einführung in Power-Query – Teil 1
Einführung in Power-Query – Teil 2

Dort ist auch beschrieben, wie du an das Add-In kommst und wie es installiert wird.

Ein Verzeichnis mit PowerQuery einlesen

Auch wenn in Excel 2016 kein separates Add-In mehr notwendig ist, werde ich der Einfachheit halber trotzdem den Begriff “PowerQuery” weiterhin verwenden.

Ziel der folgenden Übung ist es, einen kompletten Verzeichnisbaum ausgehend von einem bestimmten Unterverzeichnis in Excel einzulesen und alle enthaltenen Dateien aufzulisten. Dazu öffne ich die Registerkarte “Daten”. Hinter der Schaltfläche “Daten abrufen” verbergen sich alle möglichen Datenquellen. Hier wähle ich die Option “Aus Datei” und dann “Aus Ordner”:

Ein Verzeichnisbaum soll eingelesen werden...

Ein Verzeichnisbaum soll eingelesen werden…

Damit öffnet sich ein kleines Eingabefenster, in dem man sich zum gewünschten Verzeichnis durchklicken kann, das man gerne einlesen möchte:

Den gewünschten Ordner auswählen

Den gewünschten Ordner auswählen

Ein Klick auf die OK-Schaltfläche führt zu einem Vorschaufenster, in dem einige Dateien aufgelistet werden. Solltest du hier feststellen, dass du im falschen Verzeichnis gelandet bist, kannst du die Aktion über die Abbrechen-Schaltfläche rückgängig machen und dann nochmal mit dem richtigen Ordern starten.

Vorschau auf das Ergebnis

Vorschau auf das Ergebnis

Wenn alles passt, klickst du auf “Bearbeiten”, um den Abfrage-Editor zu starten. Hier bekommt man schon einen Ausblick auf das Ergebnis der Abfrage zu sehen, das wir jetzt noch ein bisschen anpassen werden. Auf die beiden Spalten “Date accessed” und “Date modified” kann ich verzichten und werde sie daher aus der Abfrage entfernen. Bei gedrückt gehaltener Strg-Taste klicke ich nacheinander auf die beiden Spaltenköpfe, so dass beide Spalten grün markiert sind. Im Menüband klicke ich auf “Spalten entfernen” und wählen dann nochmal die Option “Spalten entfernen”:

Markierte Spalten entfernen

Markierte Spalten entfernen

Damit verschwinden die beiden markierten Spalten und es erscheint rechts im Fenster in der Liste der angewendeten Schritte ein neuer Eintrag “Entfernte Spalten”.

Liste der angewendeten Schritte

Liste der angewendeten Schritte

Und diese Liste ist auch schon eines der vielen “Schmankerl” im Abfrageditor: Jeder durchgeführte Schritt und damit jede Veränderung der Abfrage wird einzeln aufgelistet und kann bei Bedarf mit nur einem Klick auf das kleine Kreuz davor wieder entfernt und somit rückgängig gemacht werden. Außerdem kann man später die Auswirkungen jedes Schrittes auf das Ergebnis nochmal nachvollziehen, indem man von oben beginnend nacheinander jeden Eintrag anklickt und zuschaut, wie sich das Abfrageergebnis verändert. Genial!

Bleiben wir doch noch kurz in der Liste der angewendeten Schritte, denn hier verbergen sich noch ein paar weitere Informationen. Wenn ein Schritt markiert wird, dann erscheint in einer Bearbeitungszeile oberhalb der Tabelle eine Formel, die etwas Ähnlichkeit mit einer normalen Excel-Formel hat. Es handelt sich dabei um eine PowerQuery-interne Formel- und Abfragesprache in der man z.B. sehen kann, welche Spalten genau durch diesen Schritt entfernt werden.

Die Formel in der Bearbeitungszeile

Die Formel in der Bearbeitungszeile

Hinter dem ersten Listeneintrag “Quelle” befindet sich ein kleines Zahnrad. Ein Klick darauf öffnet das Eingabefenster, in dem der Quellordner ausgewählt werden kann. So lässt sich auch später die Abfrage jederzeit auf ein neues Verzeichnis “umbiegen”.

Die Daten-Quelle anpassen

Die Daten-Quelle anpassen

Jetzt geht’s aber weiter und ich markiere wieder den Eintrag “Entfernte Spalten”, um das bisherige Abfrageergebnis angezeigt zu bekommen. Eine Besonderheit stellt dabei die Spalte “Attributes” dar. Ein Klick auf das Doppelpfeilsymbol öffnet ein Fenster mit allen möglichen Dateiattributen. Da mich nur die Dateigröße interessiert, entferne ich zuerst den Haken bei “Alle Spalten auswählen” und setze ihn dann bei “Size”:

Weitere Attribute auswählen

Weitere Attribute auswählen

Ein letzter Klick auf OK und schon wird mir zu jeder Datei die Dateigröße in Bytes angezeigt. Und das soll es auch schon gewesen sein. Über einen Klick auf die Schaltfläche “Schließen & laden” wird die Abfrage endgültig ausgeführt:

Die Abfrage ausführen und laden

Die Abfrage ausführen und laden

Je nachdem, wie groß der von dir ausgewählte Verzeichnisbaum ist, kann das schon ein paar Sekunden dauern. Irgendwann sollte dann jedoch eine formatierte Tabelle in einem neuen Arbeitsblatt erstellt sein. Neben dem zusätzlichen Menü “Tabellentools” erscheint jetzt auch ein Menü “Abfragetools” sowie rechts ein Fenster, in dem der Abfragename und die Anzahl der gelieferten Datensätze angezeigt wird.

Abfrageergebnis als Tabelle

Abfrageergebnis als Tabelle

Über einen Doppelklick auf diesen Abfragenamen oder über die Bearbeiten-Schaltfläche links oben im Menüband gelangt man bei Bedarf wieder zurück zum Abfrage-Editor.

Auswerten der Ergebnisse

Die erzeugte Tabelle ist ja schon mal ganz interessant, einen noch besseren Überblick über den belegten Speicherplatz liefert jedoch eine Pivot-Tabelle. Da unser Abfrageergebnis praktischerweise schon als formatierte Tabelle vorliegt, ist die Pivot-Tabelle im Handumdrehen erstellt.

Einfach die Registerkarte “Einfügen” öffnen und auf die Schaltfläche “PivotTable” klicken und danach im nächsten Fenster die Vorgaben mit OK übernehmen:

Eine Pivot-Tabelle einfügen

Eine Pivot-Tabelle einfügen

Nun muss aus der Liste der PivotTable-Felder nur noch das Feld “Folder Path” in den Zeilenbereich und das Feld “Attributes.Size” in den Wertebereich gezogen werden und schon haben wir unsere (fast) fertige Pivot-Tabelle:

Die gewünschten Felder platzieren

Die gewünschten Felder platzieren

Jetzt fehlt nur noch ein kleiner Feinschliff. Zuerst wird das Zahlenformat angepasst. Über einen Rechtsklick auf einen beliebigen Wert in der Wertespalte gelange ich zur Option “Wertfeldeinstellungen…”:

Wertfeldeinstellungen aufrufen

Wertfeldeinstellungen aufrufen

Hier ändere ich das Zahlenformat, so dass mir Tausenderpunkte angezeigt werden:

Das Zahlenformat anpassen

Das Zahlenformat anpassen

Zum Schluss soll die Pivot-Tabelle absteigend nach der Verzeichnisgröße sortiert werden und das war’s dann auch:

Nach der Größe absteigend sortieren

Nach der Größe absteigend sortieren

Mit wenig Aufwand haben wir uns jetzt über eine PowerQuery-Abfrage einen Überblick über unsere Speicherbelegung verschafft. Und das Allerbeste: Die Daten können jederzeit mit nur zwei Klicks aktualisiert werden. Zuerst werden die neuesten Abfrageergebnisse eingeladen:
Tabelle markieren, Abfragetools öffnen und auf die Schaltfläche “Aktualisieren” klicken:

Abfrage aktualisieren

Abfrage aktualisieren

Damit läuft im Hintergrund die PowerQuery-Abfrage los und liefert die aktuellsten Ergebnisse von der Festplatte. Und anschließend wird noch die Pivot-Tabelle aufgefrischt:
Pivot-Tabelle markieren, PivotTable-Tools öffnen und auf “Aktualisieren” klicken:

Pivot-Tabelle aktualisieren

Pivot-Tabelle aktualisieren

Wenn das kein Komfort ist, weiß ich es auch nicht!

Im nächsten Artikel gehen wir noch einen Schritt weiter. Ich werde dir zeigen, wie man PowerQuery nutzen kann, um nicht nur die Dateinamen und -größen einzulesen, sondern gleich die Dateiinhalte in einem Rutsch in eine einzige Tabelle lädt.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu “Ordnerstrukturen mit PowerQuery in Excel einlesen

  • Ray

    Hallo,
    danke für diese Info. Habe bis jetzt noch nie damit gearbeitet – wird sich aber äbndern.
    Man könnte so, sofern richtig verstanden, auch Änderungen in Ordnern erkennen… also wenn neue Datein hinzugefügt, gelöscht oder sich die Grösse ändert.
    LG Ray

    • Martin Weiß Autor des Beitrags

      Hallo Ray,

      ja, das wäre grundsätzlich möglich. Du müsstest dazu halt die Ergebnisse der jeweils letzten Abfrage zuvor in einem anderen Bereich sichern, denn jede neue Abfrage liest jeweils den kompletten aktuellen Stand ein.

      Schöne Grüße,
      Martin

  • Dieter

    Hallo Martin,
    ich warte gespannt auf den neuen Beitrag (oder habe ich den nicht gefunden?).
    Ich muss > 1.500 Exceldateien (xls und xlsx) in eine Tabelle zusammenfassen.
    Die Daten ab Zeile 9 sollen übernommen werden. Die Tabellen sind alle gleich, die Anzahl der Datensätze in den Tabellen variiert.
    Danke für schnelle Rückinfo.
    Gruß
    Dieter

    • Martin Weiß Autor des Beitrags

      Hallo Dieter,

      nein, Du hast den neuen Artikel nicht übersehen, es gibt ihn noch nicht. Daher muss ich Dich noch um ein paar Tage Geduld bitten.

      Schöne Grüße,
      Martin