Massen-Import mit PowerQuery 9

Die Inhalte vieler Dateien mit wenigen Klicks in eine einzige Tabelle importieren.
 

Im letzten Artikel habe ich gezeigt, wie man mit Hilfe von PowerQuery einen kompletten Verzeichnisbaum in Excel einliest und somit mit minimalem Aufwand eine Übersicht über alle enthaltenen Dateien erhält, nebst Erstellungsdatum und Dateigröße.

Jetzt gehen wir einen Schritt weiter und lesen die Inhalte aller Dateien eines Verzeichnisses in einem Rutsch in eine einzige Excel-Tabelle ein.

Wie das geht und welche Voraussetzungen dabei zu beachten sind, erfährst du im folgenden Beitrag.

Solltest du PowerQuery noch nicht kennen, empfehle ich dir, zuerst diese beiden Blog-Artikel zu lesen:
Einführung in Power-Query – Teil 1
Einführung in Power-Query – Teil 2

Die Ausgangslage

Ein nicht ganz unübliches Szenario im Leben eines Datenanalysten oder Controllers:
Es liegt eine Vielzahl von gleichartig aufgebauten Dateien vor, deren Inhalte für weitere Analysen alle in einer großen Excel-Tabelle zusammengefasst werden sollen.

Das können z.B. regelmäßige Exporte aus einem ERP-System wie SAP sein, oder die monatlichen Umsatzreports aus den verschiedenen Niederlassungen. Die Anzahl der Dateien schwankt, ebenso die Anzahl der Datensätze innerhalb der einzelnen Dateien. Lediglich der Aufbau, also die Spaltenanordnung, ist vorgegeben und immer identisch.

Der motivierte Controller hat nun jeden Monat (oder gar jede Woche) das Vergnügen, alle Dateien einzeln zu öffnen, die Inhalte in seine vorbereitete Auswertungstabelle zu kopieren und die Quelldateien danach wieder zu schließen. Eine sehr spannende und zufriedenstellende Tätigkeit – insbesondere bei sehr vielen Quelldateien – bei der Fehler auch völlig ausgeschlossen sind…

Und die Erde ist eine Scheibe.

Das geht sicherlich besser, oder?

Der schlaue Controller…

…nutzt natürlich – PowerQuery!

Zu Demonstrationszwecken habe ich sechs Dateien mit Auftragsinformationen erstellt und in einem gemeinsamen Verzeichnis abgespeichert:

Liste mit den Quelldateien

Liste mit den Quelldateien

Natürlich könnten das auch viel mehr Dateien sein, für das heutige Beispiel sollte das aber genügen. Alle sechs Dateien haben den gleichen Aufbau, lediglich Inhalt und Anzahl der enthaltenen Datensätze sind unterschiedlich:

Aufbau der Quelldateien

Aufbau der Quelldateien

In den nächsten Schritten werden wir diese Dateien mit Hilfe von PowerQuery importieren.

In der Registerkarte “Daten” klickst du auf die Schaltfläche “Daten abrufen”. Im angebotenen Menü wählst du “Aus Datei” und dann “Aus Ordner”:

Eine neue Abfrage erstellen

Eine neue Abfrage erstellen

Im nächsten Fenster wird das Quellverzeichnis benötigt, in dem die Dateien gespeichert sind. Hier kannst du dich über die “Durchsuchen”-Schaltfläche zum gewünschten Ort durcharbeiten:

Quellordner angeben

Quellordner angeben

Ein Klick auf die OK-Schaltfläche bringt dich zu einer Auflistung aller Dateien in diesem Verzeichnis. Wenn du den letzten Artikel zum Einlesen einer Ordnerstruktur gelesen hast, wird dir das sehr bekannt vorkommen. Der wesentliche Unterschied liegt darin, dass wir jetzt mit der “Kombinieren”-Schaltfläche weitermachen:

Vorschau auf das Quellverzeichnis

Vorschau auf das Quellverzeichnis

Hier gibt es drei Möglichkeiten:

Kombinieren und bearbeiten
Damit wird der Abfrage-Editor geöffnet und die Daten werden erst dort eingeladen. Diese Option ist dann gefragt, wenn du vor dem Import in eine Excel-Tabelle noch weitere Transformationen an den Daten vornehmen möchtest.

Kombinieren und laden
Damit erfolgt der Import ohne den Umweg über den Abfrage-Editor direkt in Excel und die Daten werden alle in ein neues Arbeitsblatt der aktuell geöffneten Arbeitsmappe geladen.

Kombinieren und laden in…
Auch hier wird der Import direkt nach Excel durchgeführt, allerdings kann man noch angeben, wohin genau die Daten geladen werden sollen. Das ist ganz hilfreich, wenn du schon eine entsprechende Tabelle vorbereitet hast.

Der Einfachheit halber habe ich mich für die zweite Option entschieden (Kombinieren und laden). Im nächsten Fenster
möchte Excel wissen, welche Daten genau importiert werden sollen. Ich übernehme im ersten Dropdown-Feld den Eintrag “Erste Datei”. Damit wird im unteren Teil des Fensters auf der linken Seite eine Liste der enthaltenen Arbeitsblätter angezeigt. Da sich die zu importierenden Daten in Tabelle1 befinden, markiere ich diesen Eintrag und bekomme rechts zur Kontrolle eine kleine Vorschau.

Vorschau auf die erste Datei

Vorschau auf die erste Datei

Ein Klick auf OK startet den Import. Je nach Anzahl und Umfang der Dateien kann dieser Vorgang unterschiedlich lange dauern. Da es sich in meinem Beispiel nur um sechs Dateien handelt, ist das Ergebnis nach wenigen Augenblicken zu sehen:

Der Import ist abgeschlossen

Der Import ist abgeschlossen

Excel hat ein neues Arbeitsblatt angelegt und darin eine neue formatierte Tabelle erstellt, die die Inhalte sämtlicher sechs Quelldateien enthält. Die zusätzlich erzeugte Spalte “Source.Name” gibt Auskunft darüber, aus welcher Datei die jeweiligen Daten stammen.

Außerdem hat sich rechts der Arbeitsbereich mit den Abfragen und Verbindungen geöffnet. Im oberen Bereich werden ein paar Verbindungseinträge gezeigt, die wir hier nicht weiter beachten müssen (dahinter verbirgt sich die Logik, die beim Import umgesetzt wurde).

Interessant für uns ist der letzte Eintrag in der Liste, der bei mir “Import-1” heißt (nach dem Namen des Quellverzeichnisses). Hier sieht man auch, wie viele Datensätze insgesamt geladen wurden.

Eine kleine Anpassung

Da wir den Import direkt in Excel durchgeführt haben, wurden die Dateien so geladen, wie sie im Quellverzeichnis stehen. Das heißt, die Sortierung erfolgt nach dem Dateinamen (Feld “Source.Name”).

Sortierung nach dem Dateinamen

Sortierung nach dem Dateinamen

Schöner wäre es jedoch gewesen, wenn die Liste gleich nach dem Datum sortiert worden wäre.

Dazu muss nur die Abfrage ein wenig angepasst werden: Mit einem Doppelklick auf den Abfrageeintrag “Import-1” wird der Abfrage-Editor gestartet.

Abfrage bearbeiten

Abfrage bearbeiten

Dort kann man über das Dropdown-Feld der Spalte “Datum” die Liste aufsteigend sortieren lassen:

Sortieren der Datumsspalte

Sortieren der Datumsspalte

Über die Schaltfläche “Schließen & Laden” wird die nun sortierte Liste zurück an Excel übergeben. Natürlich hätte man auch direkt die formatierte Tabelle in Excel sortieren können, ohne nochmal den Umweg über den Abfrage-Editor zu gehen. Der Vorteil in meiner Vorgehensweise liegt jedoch darin:

Jetzt ist die Sortierung fest in der Abfrage hinterlegt. Wenn du also beim nächsten Mal die Daten erneut einliest, kommen sie gleich nach Datum sortiert in Excel an.

Wie man die Daten nochmal einliest? Ein Rechtsklick auf die Abfrage und dann die Option “Aktualisieren”. Und schon wird die Abfrage erneut ausgeführt und alle Dateien werden erneut importiert.

Daten erneut einlesen

Daten erneut einlesen

Vielleicht hast du ja ein paar andere Anwendungsfälle für solch einen Massenimport auf Knopfdruck. Dann lass es uns unten in den Kommentaren wissen.

 

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.

9 Gedanken zu “Massen-Import mit PowerQuery

  • Tim

    Hi,

    danke erst mal für die Powerquery-Tutorials. Kleine Frage:
    Wird beim Aktualisieren der Abfrage das komplette Verzeichnis neu eingelesen (also auch etwaige neue Dateien), oder nur solche Dateien, die bereits eingelesen (und vlt. geändert wurden)?

    Schöne Grüße
    -Tim

    • Martin Weiß Autor des Beitrags

      Hi Tim,

      beim Aktualisieren wird das komplette Verzeichnis neu eingelesen. Und damit auch neu hinzugekommene oder geänderte Dateien.
      Dateien hingegen, die seit dem letzten Import aus dem Verzeichnis gelöscht wurden, verschwinden dann auch aus der importierten Excel-Tabelle.

      Schöne Grüße,
      Martin

  • Stephan

    Kann es sein, dass da in Excel 2016 einiges ein wenig anders funktioniert ? Ich finde z.B. keinen “Kombinieren-Button”.
    Nach vielem Probieren habe ich es dann an ganz anderer Stelle im Abfrage-Editor über Content und dem Symbol “Binärdateien kombinieren” geschafft, den Massenimport aus mehreren Dateien anzustoßen.
    Wenn es dann endlich funktioniert, ist das ein tolles Feature, das wirklich Zeit sparen kann.
    Es wäre aber hilfreich, wenn im Blog auf die Unterschiede zwischen dem PowerQuery als Addon und Excel 2016 eingegangen würde.
    Beste Grüße, Stephan

    • Martin Weiß Autor des Beitrags

      Hallo Stephan,

      danke für den Hinweis. Es ist natürlich immer gut möglich, dass manche Dinge in Excel 2016 etwas anders funktionieren. In diesem Fall dürfte es jedoch daran liegen, dass Dein PowerQuery-AddOn vermutlich nicht mehr ganz aktuell ist. Ich habe es gerade nochmal unter Excel 2010 probiert, und hier gibt es auch den “Kombinieren”-Button (meine Version: 2.48.4792.941). Microsoft aktualisiert das AddOn immer mal wieder, von daher lohnt es sich, ab und zu mal eine neue Version herunterzuladen (aktuelle Version: 2.49.4831.381 vom 22.09.2017)

      Schöne Grüße,
      Martin

      • Stephan

        Hallo Martin,
        Excel 2016 blockiert die Installation des PowerQuery-Addon mit dem Hinweis, dass die Funktionen nun vollständig in Excel 2016 integriert sind. Ich hatte die Installation der o.g. Version tatsächlich schon versucht, aber ohne Erfolg. Das heißt, dass wir uns mit der im Handling zum Teil deutlich veränderten PowerQuery-Implementierung des Excel 2016 arrangieren müssen. Weiß nicht, ob man im Blog hier und da auf die Unterschiede eingehen kann. PowerQuery ist aber in jedem Fall ein geniales Werkzeug und der Blog ein super Hilfe für den Einstieg.
        Beste Grüße, Stephan

        • Martin Weiß Autor des Beitrags

          Hallo Stephan,

          dann lag hier wohl ein Missverständnis vor: Ich bin davon ausgegangen, dass Du NICHT mit Excel 2016 arbeitest, denn mein Blogartikel basiert auf Excel 2016. Und hier muss natürlich kein AddOn installiert werden, diese gilt nur für Excel 2013 und 2010. Möglicherweise gibt es dann noch einen Unterschied zwischen Excel 2016 Einzellizenz und Excel 2016 im Rahmen von Office 365. Letzteres wird von mir eingesetzt. Leider sind das Versionsthema und die damit verbundenen Unterschiede auch für mich nicht immer ganz zu durchschauen…

          Schöne Grüße,
          Martin

          • Stephan

            Hallo Martin,
            das ist ein wirklich interessanter Fall, denn auch ich benutze das Excel im Rahmen einer Office365-Lizenz. Dennoch scheint es Unterschiede zu geben, wie man dieser Notiz entnehmen kann:
            http://www.excel-ticker.de/aenderungen-an-den-funktionen-im-menueband-zum-abruf-externer-daten/
            Meine Excel-Version ist 1701 (Build 7766.2099). Wie der Link beschreibt, gibt es zur Version 1703 (Build 7920.1000) eine Änderung in der Menüstruktur (und wohl auch in der Ausprägung bestimmter Funktionen). Das beantwortet die Frage, warum ich nicht alle im Blog beschriebenen Arbeitsschritte 1:1 nachvollziehen kann.
            Da sieht man, wie aktuell die Weiterentwicklung von PowerQuery durch Microsoft verfolgt wird.
            Grüße, Stephan

          • Martin Weiß Autor des Beitrags

            Hallo Stephan,

            vielen Dank für den Hinweis und den Link dazu, das klingt nach einer guten Erklärung. Das ganze Thema läuft ja bei Microsoft unter “Power-BI” und wird ganz offensichtlich massiv vorangetrieben.

            Schöne Grüße,
            Martin