Power Query: Exceldateien importieren & Ordner auslesen
Die Möglichkeit, mit Power Query die Inhalte ganzer Dateiordner auszulesen, gehört zu meinen absoluten Favoriten: Ob z.B. im Rechnungswesen in mehreren Dateien gelieferte Schnittstellendaten, Konsolidierung jeder Art oder im #Controlling das automatisierte Einsammeln von Planungsdaten bis hin zur Überwachung von Planungs- oder Abschlussprozessen: All dies lässt sich mit dieser Funktion zuverlässig realisieren: Die Dateiinhalte werden ausgelesen und in einer großen Tabelle zusammengefasst, d.h. untereinander geschrieben und können dann ggf. mit Pivots beliebig analysiert werden. Im Vergleich zur Konsolidierungsfunktion in Excel braucht man hier die Dateien nicht vorher zu öffnen und zu adressieren: Mit einem Klick werden sämtliche in dem Ordner (incl. Unterordner) befindliche Tabellen zusammengefasst.
In unserer Quick-Import-Datei haben wir das schon so vorbereitet, dass dieser Import vollautomatisch abläuft: Man braucht nur den Ordnerpfad reinkopieren (in Blatt „Parameter“, Zelle B1), dann Daten – „Alle aktualisieren“ und voilà: Im Blatt „Ergebnis“ finden sich sämtliche Daten – vollautomatisch (ohne VBA, dies ist reine Power Query-Funktionalität, allerdings wird die Option für schnelles Kombinieren verwendet). Probieren Sie es aus!
Die Datei kann hier heruntergeladen werden: [wpfilebase tag=file id=10 tpl=filebrowser /]
Sie werden bemerken, dass nur die Spalten importiert werden, die die gleiche Überschrift haben wie das erste Arbeitsblatt im Ordner. Diese Funktionalität bietet sich also ideal für Dateien mit identischem Spaltenaufbau an. Was man machen kann, wenn:
- der Spaltenaufbau nicht identisch ist
- die Arbeitsblätter keine Überschriften haben
- man vorher kontrollieren möchte, was man importiert
- man doch eigentlich nur die Arbeitsblätter der aktuellen Datei so zusammenfassen möchte
- man nur ausgewählte Spalten importieren möchte
- man nur ausgewählte Dateien oder Arbeitsblätter aus den Ordnern #importieren möchte
- man die eingelesenen Zeilen filtern oder bearbeiten möchte
- zusätzliche Spalten hinzufügen, Felder trennen….
- und wie man damit Planungs- oder Abschlussprozesse überwacht
erfahren Sie in unseren folgenden Blogbeiträgen. Dort erläutern wir dann auch den Aufbau der Quick-Info-Datei, hier zeigen wir zunächst die Schritt-für-Schritt-Anleitung dieser Methode.
Schritt-für-Schritt-Anleitung
Man nutzt dafür die Funktion in Power Query, die eigentlich für das Auslesen der sogenannten Metadaten (Name, Änderungsdatum, Dateipfad usw.) aus Dateiordnern bestimmt ist:
Power Query – „Externe Daten abrufen“ – „Aus Datei“ –„Aus Ordner“
Im Dialog dann auf „Durchsuchen“ gehen und den Pfad auswählen, in dem sich die Dateien befinden:
Hier öffnet sich die erste Verzweigungsebene der in der xlsx-Datei gespeicherten Ordnerstruktur:
Um an den Inhalt der Excel-Dateien zu kommen, fügen wir eine benutzerdefinierte Spalte hinzu: „Spalte hinzufügen“ – „benutzerdefinierte Spalte hinzufügen“. Wir geben der Spalte einen neuen Namen (ExcelDateiinhalt) und geben folgende Formel ein: „Excel.Workbook([Content])“:
In der Tabelle erscheint die neue Spalte ganz rechts – dort klicken wir auf die Pfeile im Dropdownfeld:
Dann öffnet sich die nächste Ebene des Inhalts: Der Name der einzelnen Arbeitsblätter in den Dateien wird angezeigt, dann kommt eine Spalte, die auf den Inhalt verweist (Content) sowie weitere Metadaten.
Da wir die (einheitlichen) Spaltenüberschriften in unseren neuen Tabellen ja nur einmal benötigen, definieren wir noch eine neue Spalte, die das entsprechend umsetzt:
Über diese Spalte kommen wir nun an unseren Inhalt so ran, dass die Überschriftenzeile des ersten eingelesenen Blattes als Überschriftenzeile für die neue Tabelle verwendet wird und die restlichen Daten ohne duplizierte Überschriften eingelesen werden.
Das Überschriftenfeld wird wieder durch den Klick auf die Pfeile erweitert. Im folgenden Dialog wählt man nur noch unten das Häckchen „Ursprünglichen Spaltennamen als Präfix verwenden“ ab:
Nun erscheint der Dateiinhalt ganz rechts in der Tabelle und wir haben das, was wir wollten. Als letztes noch die nicht benötigten Spalten löschen:
Nun auf „Start – Abfrage – Schließen und Laden in“ klicken:
Hier kann man auswählen, wo die Ergebnisse ausgegeben werden sollen. Wir bleiben aber beim Default-Wert Tabelle.
Die Ergebnisse stehen dann im Arbeitsblatt zur Verfügung.
Im nächsten Beitrag gibt es hier schöne Erweiterungen: Mit Filter- und Kontrolloptionen behalten Sie alles sehr gut im Überblick und bleiben flexibel: Datenimport automatisieren
Sie haben noch gar kein Power Query? Dann aber schnell herunterladen!
Brauchen Sie noch einen Xpertenrat?
Weitere Tutorials?
Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!