Listen Sie alle Dateien in einem Ordner in Excel mithilfe von Power Query - Excel-Tipps auf

Inhaltsverzeichnis

Schau Video

Die heutige Frage: Marcia muss eine Liste aller PDF-Dateien der Steuerrechnung aus einem Ordner in eine Excel-Tabelle abrufen. Dies ist einfach, wenn Sie Excel 2016 auf einem Windows-PC mit den neuen Tools zum Abrufen und Transformieren von Daten verwenden.

Wenn Sie über Excel 2010 für Windows oder Excel 2013 für Windows verfügen, müssen Sie das kostenlose Power Query Add-In von Microsoft herunterladen. Gehen Sie zu Ihrer bevorzugten Suchmaschine und geben Sie "Power Query herunterladen" ein, um den aktuellen Link zu finden. (Microsoft liebt es, URLs vierteljährlich zu ändern, und mein großartiger Web-Typ hasst es, wenn unsere Links veraltet sind. Deshalb werde ich nicht einmal versuchen, hier einen Link einzufügen.)

Das folgende Video zeigt Ihnen die vollständigen Schritte, aber hier ist die Übersicht:

Beginnen Sie mit einem leeren Arbeitsblatt
Daten, Daten abrufen, aus Datei, aus Ordner
Navigieren Sie zum Ordner
Klicken Sie auf Bearbeiten statt Laden
Öffnen Sie die Filter-Dropdown-Liste für den Dateityp und entfernen Sie alles, was keine PDF-Datei ist
Öffnen Sie den Filter für Ordner und entfernen Sie alle Garbage-Unterordner
Behalten Sie nur Dateiname und Ordner bei - klicken Sie mit der rechten Maustaste auf jede Spaltenüberschrift und wählen Sie Entfernen
Ziehen Sie die Ordnerüberschrift links neben die Dateiüberschrift. Dadurch kann die Zusammenführung funktionieren.
Wählen Sie beide Spalten aus. Klicken Sie auf eine Überschrift. Umschalt + Klicken Sie auf die andere Überschrift.
Wählen Sie Spalte hinzufügen, Spalten zusammenführen, Geben Sie einen neuen Namen für die Spalte ein. OK klicken.
Klicken Sie mit der rechten Maustaste auf die Überschrift für die neue Spalte und klicken Sie auf Andere Spalten entfernen
Home, Schließen & Laden
Das Erstaunliche… Sie können die Abfrage später aktualisieren. Klicken Sie im Bereich "Abfragen und Verbindungen" auf das Symbol "Aktualisieren".

Während Power Query erstaunlich leistungsfähig ist, ist dies eine meiner Lieblingsaufgaben. Ich möchte häufig ein VBA-Makro für jede Datei in einem Ordner ausführen. Das Abrufen einer Liste aller PDF-Dateien in einem Ordner ist ein guter Ausgangspunkt.

Schau Video

Video-Transkript

Lernen Sie Excel aus dem Podcast, Folge 2181 - Ordnerdateien in Excel auflisten!

Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Bei der heutigen Frage hat jemand eine Liste mit PDF-Dateien für Steuerrechnungen in einem Ordner und muss die Liste aller dieser Dateinamen in Excel abrufen. Okay, und eine Möglichkeit, dies zu tun, besteht darin, sie alle einzugeben oder aus dem Windows Explorer zu kopieren und einzufügen, aber es gibt ein großartiges Tool, das dies lösen kann. Und meine erste Frage war: "Nun, welche Excel-Version haben Sie?" Denn wenn Sie Excel 2016 haben, werden sie diese erstaunliche neue Funktionalität namens "Get & Transform Data" haben! In Office 365 befindet es sich auf der linken Seite. In der Originalversion von Excel 2016 befand es sich in der dritten Gruppe. Also suchen Sie einfach nach Get & Transform. Wenn Sie sich in Excel 2010 oder Excel 2013 für Windows befinden, können Sie Power Query herunterladen und haben eine eigene Registerkarte mit genau diesen Informationen.

Werfen wir jetzt einen kurzen Blick auf diesen Ordner. Okay, ich habe hier gerade einen gefälschten Ordner mit einigen gefälschten Daten erstellt. Sie werden sehen, dass sich in diesem Ordner Excel-Dateien und PDFs befinden. Ich möchte nur die PDFs und es gibt auch einige Unterordner. Ich möchte diese PDFs nicht, ich möchte nur die PDFs im Hauptordner. Also C: Budgets, ich werde das kopieren und dann hierher zurück zu Excel kommen, und wir werden sagen, dass wir Daten abrufen wollen, aus einer Datei, aus einem ganzen Ordner, so und dann Geben Sie dort den Ordnerpfad ein oder verwenden Sie entweder die Schaltfläche Durchsuchen. Und wenn Sie diesen ersten Bildschirm erhalten, möchten Sie ihn definitiv bearbeiten, und jetzt befinden wir uns im Power Query-Editor.

Okay, also mein Ziel hier, ich brauche den Inhalt nicht, also klicke ich mit der rechten Maustaste und sage Entfernen Sie diese Spalte. Es gibt meine Liste von Dateien, ich möchte nur PDF-Dateien. Wenn es also etwas gibt, das kein PDF ist, möchte ich nur PDFs. Klicken Sie auf OK. Ich kann sehen, dass es nur die PDF-Dateien sind. Ah, und dann schauen Sie hier rüber, sehen Sie, jetzt ziehen sie Dinge nur aus dem Originalordner und aus dem Garbage-Ordner, also öffne ich das und deaktiviere alles, was nicht der Originalordner ist. Okay, jetzt habe ich eine nette kleine Liste, und diese Liste enthält, weißt du was, 9 Datensätze, aber im wirklichen Leben haben sie wahrscheinlich Dutzende oder Hunderte davon. Okay, ich brauche jetzt keine anderen Sachen, also kann ich mit der rechten Maustaste klicken und diese Spalten entfernen.

Okay, jetzt brauche ich hier wirklich den Ordnerpfad und den Dateinamen zusammen. Okay, also werde ich FolderPath nehmen und es nach links ziehen und dort ablegen, und dann den magischen Schritt hier: In normalem Excel müssten wir dafür eine Verkettung durchführen, aber was ich tun werde, ist ich Ich werde Spalten zusammenführen. Also werde ich Spalte hinzufügen und Spalten zusammenführen auswählen, das Trennzeichen wird Keine sein, die neue Spalte wird Dateiname heißen und auf OK klicken, in Ordnung, also haben wir den Ordnernamen, den Schrägstrich und den Dateinamen , so wie das. Nun, das ist eigentlich das einzige, was wir brauchen, also klicke ich mit der rechten Maustaste und sage Entfernen der anderen Spalten und schließlich Start, Schließen und Laden, und wir erhalten ein brandneues Blatt mit unseren Daten. Okay, jetzt kommt es als Tabelle und ich werde es einfach kopieren, Strg + C,und dann kommen Sie hierher, wo ich die Daten wirklich haben wollte, und fügen Sie Sonderwerte ein. Klicken Sie auf OK. Jetzt ist es keine Tabelle mehr, es sind nur meine reinen Daten, und jetzt ist hier das wirklich Schöne daran.

Also haben wir dies einmal eingerichtet und wow, die Einrichtung hat weniger als 3 Minuten gedauert, aber kehren wir zu diesem Ordner "Budgets" zurück und verschieben einige Dinge. Nehmen wir einen dieser Garbage-Datensätze und kopieren ihn in den Hauptordner, Strg + V, in Ordnung. Jetzt gibt es hier mehr Dinge, 10 PDF-Dateien statt 9. Wenn ich hierher komme, wo sich die Abfrage befindet, und auf der rechten Seite des Bildschirms, in den Abfragen und Verbindungen, müssen Sie diese möglicherweise erweitern. Ich habe meine bereits erweitert. Sie sehen unsere Budgets mit 9 geladenen Zeilen. Ich werde hier auf das kleine Aktualisierungssymbol klicken, und Budgets hat jetzt sehr schnell 10 Zeilen geladen. Sie nehmen die neuen Datensätze auf, richten sie einmal ein und können sie dann einfach aktualisieren, um die neuen Daten zu erhalten.

Nun, dies ist der Punkt im Podcast, an dem ich Sie normalerweise auffordere, mein Buch zu kaufen, aber heute werde ich Sie bitten, dieses Buch „M is for (DATA) MONKEY“ von Ken Puls und Miguel Escobar zu kaufen. Ein ERSTAUNLICHES Buch, in dem Sie alles über die Verwendung von Power Query oder Get & Transform Data lernen. Alles, was ich über Power Query gelernt habe, habe ich aus diesem Buch gelernt.

OK, zum Abschluss dieser Episode: Unser Ziel ist es, eine Liste von Dateinamen in Excel zu importieren. Wenn Sie über Excel 2016 verfügen, können Sie die neuen Get & Transform-Daten verwenden. Wenn Sie nicht über 2016 verfügen, aber eine echte Excel-Version unter Windows ausgeführt wird, können Sie das kostenlose Power Query-Add-In für Excel 2010 oder Excel 2013 herunterladen. Es funktioniert nicht auf Ihrem Android-Telefon oder Ihr iPad oder Ihr iPhone oder Surface RT oder Ihr Mac, richtig, es ist nur für Windows-Versionen von Excel. Wir beginnen also mit einem leeren Arbeitsblatt, Daten, Daten abrufen, Aus Datei, Aus Ordner, geben den Ordnernamen ein oder Durchsuchen. Stellen Sie sicher, dass Sie auf Bearbeiten anstatt auf Laden klicken. Filtern Sie dann im Filter nach dem Dateityp, um alles zu entfernen, was kein PDF ist, und filtern Sie nach dem Ordnernamen, um alle Garbage-Unterordner zu entfernen. Behalten Sie nur den Dateinamen und den Ordner.Klicken Sie also mit der rechten Maustaste auf die anderen und sagen Sie Spalte entfernen. Ziehen Sie dann die Ordnerüberschrift links neben die Datei, damit die Zusammenführung funktioniert. Wählen Sie beide Spalten aus, wählen Sie dann auf der Registerkarte Spalte hinzufügen die Option Spalten zusammenführen, geben Sie einen neuen Namen ein, klicken Sie auf OK, klicken Sie mit der rechten Maustaste auf diese neue Spalte und entfernen Sie die anderen Spalten, Startseite, Schließen und Laden. Daraufhin wird Ihre Liste angezeigt. Das Erstaunliche ist, dass Sie die Abfrage später aktualisieren können, indem Sie dieses Aktualisierungssymbol in den Abfragen und Verbindungen verwenden.Sie können die Abfrage später aktualisieren, indem Sie dieses Aktualisierungssymbol in den Abfragen und Verbindungen verwenden.Sie können die Abfrage später aktualisieren, indem Sie dieses Aktualisierungssymbol in den Abfragen und Verbindungen verwenden.

Hey, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von!