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. Oder du buchst meinen Online-Kurs Daten importieren und aufbereiten mit Power Query
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…
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
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
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
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
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
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
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
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
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
Ü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
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
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
Hier ändere ich das Zahlenformat, so dass mir Tausenderpunkte angezeigt werden:
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
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
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
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.
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.
SUPER!
Ganz neue Perspektiven tun sich mir da auf mit dem Query-Newsletter.
Vielen Dank und freundliche Grüsse aus Zürch
Gern geschehen! PowerQuery ist wirklich genial.
Schöne Grüße,
Martin
Super Tipp,
kann ich wirklich gut gebrauchen. Danke!
Gruss
Rosa
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
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
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
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
Hallo Martin,
vielen Dank für diesen hilfreichen Tipp! Dazu habe ich eine Frage: Ich möchte eine Liste aller Excel-Dateien eines Verzeichnisses auslesen. Bei der Abfrage mit Power-Query werden sämtliche Attribute der Dateien aufgelistet. Allerdings fehlt mir die Eigenschaft „Titel“, die ich für meine Auswertung benötige.
Wie gelingt es mir, den „Titel“ ebenfalls mit auszulesen? Vielen Dank vorab für die Antwort!
Gruß Rico
Hallo Rico,
meines Wissens geht das mit Power Query gar nicht. Bei den angebotenen Attributen handelt es sich um reine Dateiattribute auf Betriebssystem-Ebene, nicht jedoch um Excel-interne Eigenschaften, wie Titel o.ä.
Das dürfte vermutlich nur per VBA möglich sein.
Schöne Grüße,
Martin
Hat wunderbar funktioniert.
bin begeistert wie einfach das geht.
Besten Dank für den wertvollen Tip.
LG Günter
Hallo Günter,
sehr gern geschehen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für den Artikel. Anstatt einer Liste aller Dateien, bräuchte ich allerdings eine Liste der Ordner, um unsere Ordnerstruktur abzubilden. Hättest du da eine Idee, wie man das bewerkstelligen kann.
Beste Grüße
Florian
Hallo Florian,
das funktioniert ganz ähnlich. Du gehst die Schritte wie oben beschrieben durch, bis die Daten im Power-Query-Editor eingelesen sind. Dann markierst du die Spalte „Folder Path“ und entfernst alle anderen Spalten. Jetzt musst du nur noch die doppelten Einträge entfernen (Rechtsklick auf den Spaltenkopf „Folder Path“, dann „Duplikate entfernen“) und schon hast du nur die Liste aller Verzeichnisse.
Schöne Grüße,
Martin
Hallo, gibt es die Option einen leeren Ordnerpfad aufzuzeigen? Wenn ich aktuell eine Ordnerstruktur importiere, und einen leeren TestOrdner erstelle, nimmt mir PQ das ganze beim aktualisieren nicht mit auf. Liegt in diesem Ordner allerdings eine Datei (egal ob befüllt oder leer) nimmt es PQ schon auf.
Hallo Raymkay,
meines Wissens ist es tatsächlich nicht möglich, einen leeren Ordner darzustellen. Letztendlich versucht Power Query mit dieser Funktion ja immer, Dateien in irgendeiner Form zu importieren. Somit fallen leere Verzeichnisse einfach unter den Tisch.
Schöne Grüße,
Martin
Hallo Martin,
also ganz ehrlich – ich liebe Excel auch!
Es macht Freude damit zu arbeiten und bei den von Dir gezeigten Möglichkeiten bekommt man Lust auf mehr.
Deine Seite finde ich wirklich toll.
Danke für die vielen Infos.
Ich möchte meine sämtlichen Dateien mit Pfad über eine Linksammlung als Bestandsaufnahme für mich abbilden.
Da hilft Query auch.
Ich möchte jedoch alle Dateien, auch die in den Unterverzeichnissen mit Pfad ausgegeben bekommen.
Vielen Dank
Grüße
Achim
Hallo Achim,
aber genau das macht die oben im Artikel beschriebene Abfrage ja: Es werden sämtliche Dateien in sämtlichen Unterverzeichnissen eingelesen und aufgelistet.
Oder habe ich dich irgendwie falsch verstanden?
Schöne Grüße,
Martin
Hi Martin,
ich benötige noch eine Zusatzfunktion zusätzlich zum
Einlesen von Dateinamen in Excel. Das geht bekanntermassen ja mit Power Query und funktioniert auch super.
In meinem Verzeichnis liegen Produktdatenblätter (PDF).
Eine bestimmte (markierte Anzahl) Auswahl solcher PDF-Dateien soll nun in einen neuen Ordner kopiert werden. Geht das und hast du ein Buch oder ähnliches, welches derartige Arbeiten beschreibt?
Besten Dank im Voraus für eine Antwort.
Georg
Hallo Georg,
solche Dateioperationen sind mit Power Query nicht möglich, das geht am Anwendungsgebiet einfach vorbei (Daten importieren und transformieren). Um so etwas innerhalb von Excel überhaupt bewerkstelligen zu können, müsstest du mit VBA-Programmierung drangehen.
Schöne Grüße,
Martin
Hallo Martin,
besten Dank für den Tipp. Gut zu wissen, was geht und was nicht. Das alleine spart schon enorm Zeit. Da muss ich mir dann mal was überlegen.
Vielen Dank und Grüsse
Georg
Vielen Dank für diesen Artikel. Ich habe nun folgendes Problem. Ich habe knapp 100 csv files mit Kurszeitreihen. Alle Dateien sind nach dem gleichen Schema aufgebaut. Damit ich die einzelne Datei in eine Pivot Tabelle einlesen kann, müssen Zeilen gelöscht werden und Werte Punkt mit Komma ersetzt werden. Ansicht kein Problem, wenn ich es für jeden csv file separat mache. Lässt sich das auch für einen kompletten Ordner machen? Praktisch wie eine Schablone und dann sollte power query auch jede Zeitreihe in ein separates Blatt einfügen. Ist das denn auch möglich?
Liebe Grüße
Hallo Henning,
wie man einen Massenimport von allen Dateien in einem Verzeichnis macht, habe ich in diesem Artikel beschrieben:
https://www.tabellenexperte.de/massen-import-mit-powerquery/
In der zusammengefassten Tabelle lassen sich dann bequem alle Anpassungen vornehmen (Leerzeilen löschen, Werte ersetzen etc.). Allerdings ist dann eben alles in einer einzigen Tabelle, und nicht in vielen einzelnen Arbeitsblättern. Das wäre bei 100 Dateien (= 100 Blättern) auch nicht empfehlenswert.
Aber da die Gesamttabelle auch den Namen der Quelldatei enthält, kannst du in der Pivot-Tabelle ganz bequem einen entsprechenden Filter auf einzelne Dateien setzen.
Schöne Grüße,
Martin
Hallo, Martin.
Ein sehr schöner Beitrag.
Stelle allerdings fest, dass es im aktuellen Excel etwas anders aussieht. Nutze Microsoft Office 365 ProPlus.
Darin sieht es nach Aufruf von >> Daten >> Daten abrufen >> Aus Datei >> Aus Ordner >> Pfad >> nicht so aus.
Wird es im eBook dazu ein Update geben, oder ist es bereits überarbeitet?
Und: Habe das mal soweit verfolgt, was Du oben beschreibst.
Kann es sein, dass über das Query auch Daten und nicht nur die Pfade abgerufen werden?
Hab ich da etwas ausgewählt, was mir auch die Dateien in die Arbeitsmappe einbindet?
Gruß
Wilhelmine
Hallo Wilhelmine,
dankeschön für dein Feedback. Das „Problem“ mit Power Query ist, dass es von Microsoft laufend weiterentwickelt wird. Daher kommen manchmal neue Funktionen dazu, die es zum Zeitpunkt des Artikels noch nicht gab. Und manchmal werden auch einfach nur Menüpunkte oder Schaltflächen umbenannt – so wie auch in diesem Fall. Nach der Ordnerauswahl heißt die Schaltfläche jetzt nicht mehr „Bearbeiten“, sondern „Daten transformieren“. Ansonsten hat sich aber bezogen auf den Artikel nichts Wesentliches geändert.
Ich habe tatsächlich eingeplant, das E-Book in dieser Hinsicht wieder einmal anzupassen, ist aber noch nicht passiert.
Und ja, Power Query kann nicht nur die Verzeichnisstrukturen einlesen, sondern auch Dateiinhalte. Das habe ich im Folgeartikel beschrieben:
Massen-Import mit Power Query
Schöne Grüße,
Martin
Hallo Martin,
super vielen Dank für die ganzen Beschreibungen!
Du weißt sicherlich auch eine gute Lösung für folgendes: ich möchte in der Tabelle dann die Dateien direkt als Link zur Datei haben.
Sinn ist, dass ich von allen Dateien in einer Ordnerstruktur eine Liste habe (mit den Angaben modifiziert wann, erstellt wann…).
Und der User soll einfach auf die Datei klicken können, um sie zu öffnen.
Im Prinzip geht das manuell ja und es zeigt mir dann so: file///C:\Users\test.txt
Wenn ich beim Powerquery mit „Zusammenführen“ die Spalte „Ordner“ und „Dateiname“ verknüpfe, steht dann in der Zelle
C:\Users\test.txt
Wie bekomme ich das nun so formatiert, dass es gleich ein Link ist?
Herzlichen Gruss! Michaela
Hallo Michaela,
ob das direkt mit Power Query möglich ist, wage ich zu bezweifeln. Aber wenn Du in der Ausgabetabelle hinterher eine zusätzliche Spalte einfügst und dort über die HYPERLINK-Funktion auf die mit PQ vorbereite Spalte mit dem Pfad zugreifst, sollte das schon klappen,.
Schöne Grüße,
Martin
Moin Michaela,
Moin Martin,
ich habe 2 Möglichkeiten gefunden, die evtl. helfen könnten.
Möglichkeit 1:
In Power Query im Erweiterten Editor diese Zeile hinzufügen:
= Table.AddColumn(Quelle, „Link“, each Uri.Combine([Folder Path], [Name]))
Möglichkeit 2 habe ich auf dieser Seite gefunden:
https://www.thebiccountant.com/2017/09/12/powerquery-return-clickable-hyperlinks-to-excel/
Vielleicht hilft es ja weiter.
Gruß Marcel
Hallo Marcel,
vielen Dank für diese interessanten Lösungen! Bei Variante 1 wird bereits der vollständige Linktext erzeugt („file:///…..“), aber er ist noch nicht direkt klickbar, oder? Man muss in der Tabelle in Excel erst mit F2 in den Bearbeitungsmodus wechseln und mit Enter bestätigen, damit ein klickbarer Link entsteht. Oder habe ich etwas übersehen?
So oder so, in meinen Augen zwei hilfreiche Varianten.
Schöne Grüße,
Martin
Moin Martin,
ja da hast du vollkommen Recht. Ist mir in dem Text ganz entfallen. *hust*
Ich habe auch schon etliches Ausprobiert um das ganze direkt klickbar zu machen, aber Excel wandelt das wohl erst dann in einen Hyperlink um, wenn es einmal bearbeitet wird. Vorher ist es für Excel scheinbar nur Text.
Ein weiteren kleinen Nachteil hat beides auch noch. Sobald die Abfrage aktuallisiert wird, muss in beiden Varianten der Hyperlink wieder erzeugt bzw. die Formel durch ersetzten des Hochkommas wieder aktiviert werden.
Gruß Marcel
Hi Marcel,
dann bin ich ja beruhigt, dass mir nichts durchgerutscht ist 😉
Auch wenn die beiden Varianten vielleicht nicht 100% perfekt sind, so sind sie zumindest eine sehr gute Basis und für viele Fälle bestimmt ausreichend.
Schöne Grüße,
Martin
Hallo Martin,
danke für deinen Artikel. Leider funktioniert es bei mir nicht. Grund ist die Überschreitung der maximalen Länge des Dateipfades. Hast du eine Idee, wie man diesen Fehler beheben bzw. überspringen kann?
Beste Grüße
Karsten
Hallo Karsten,
du könntest höchstens versuchen, mehrere separate Abfragen zu erstellen und diese jeweils ein paar Verzeichnisse tiefer beginnen zu lassen. So dass die maximale Pfadlänge nicht überschritten wird. Und die Ergebnisse dann aneinander anfügen. Aber wenn der Pfad zu lang ist, ist er zu lang 🙁
Schöne Grüße,
Martin