Excel Dateien zusammenführen in nur 6 einfachen Schritten! (+BONUS)

YouTube hat mir neulich ein Video vorgeschlagen, wie ich Excel Dateien zusammenführen kann.
Da das Video über 2 Millionen views hat, muss es ja wohl eine begehrte Lösung sein.
Der nette Mann in dem Video hat gezeigt, wie es mit Excel direkt umgesetzt werden kann und was soll ich sagen….
Ich wollte weinen…

Deshalb hier meine Lösung für das Problem.
Damit erhalte ich ein sauberes und wiederverwendbares Skript, dass ich nicht über Word Dokumente und Excel Dateien hin und her kopieren muss.

Zum Vergleich habe ich dir hier die “Excel Lösung” kurz mit skiziert.

Die Excel Lösung

Die Lösung sah in etwa so aus:

• Erstelle eine neue Excel Datei
• Öffne den VBA Editor
• Kopiere den folgenden Code in den Editor (der Code wurde extra in einem Word Dokument vorgehalten, damit er immer kopiert werden konnte)

Sub GetSheets()
Path = ""
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet in ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

• Passe den Pfad an
• Führ den VBA Code aus

Der ganze Prozess ist so umständlich und fehleranfällig, dass ich dir hier zeigen möchte, wie einfach diese Aufgabe eigentlich sein kann.

Wie kann ich die Arbeitsblätter mehrerer Excel Dateien zusammenführen?

Mit dem os Modul aus Pythons Standard kannst du ganz einfach alle Dateien in einem Verzeichnis durchlaufen. Dazu noch OpenPyxl, dass die Arbeit mit Excel Dateien enorm erleichtert. Durchlauf einfach die Dateien, öffne die Arbeitsblätter und übernimm Zeile für Zeile die Inhalte.

Bibliotheken

Die folgenden Bibliotheken musst du installieren:

• OpenPyxl (hier ein Post dazu)

Excel Dateien zusammenführen (Lösung)

Schritt 1: Pfad festlegen

Wo befinden sich deine Excel Dateien?
Sicher hast du sie irgendwo in einem Ordner zusammen gesammelt.

Und genau den Ordner legen wir uns im ersten Schritt als Variable an.
Warum?
Ganz einfach: Wenn du später dieselbe Aktion an anderer Stelle ausführen willst, musst du nur den Pfad in einem Skript ändern und schon kann’s losgehen!
Es ist kein langes Suchen nötig.

PATH = 'daten'

Hier nehmen wir einen relativen Pfad.
Das heißt, in dem Ordner in dem dein Skript liegt, gibt es einen Ordner daten, in dem wiederum deine Excel Dateien liegen.
Hier kannst du natürlich jeden beliebigen Pfad angeben. Je nachdem, wo deine Dateien abgelegt sind.

In dem Schritt können wir auch direkt noch eine Variable anlegen: die Ergebnisdatei.

Die Arbeitsblätter sollen ja alle in einer neuen Datei gesammelt werden.
Und wo die Datei liegt und unter welchem Namen sie abgespeichert wird, bestimmst natürlich du.

RESULT = 'daten/result.xlsx'

In dem Beispiel lege ich die Datei einfach mit zu den anderen Dateien dazu.
Als Name bietet sich result an, einfach, weil sie … na ja … das Ergebnis des Excel Dateien zusammenführen darstellt.

Schritt 2: Excel Datei für Ergebnisse erzeugen

Um die Ergebnis Excel Datei zu befüllen, müssen wir sie natürlich erstmal anlegen.
Das geht ganz einfach über die Workbook() Klasse von OpenPyxl:

result_workbook = Workbook(write_only=True)

Über den Parameter write_only=True verhindern wir, dass uns openpyxl beim Erzeugen der Excel Datei gleich ein Standard Arbeitsblatt erzeugt.
Wir wollen ja unsere eigenen einfügen.
Das habe ich im Artikel zu Testdaten etwas genauer erklärt.

Schritt 3: Alle Excel Dateien im Pfad durchlaufen

Als Nächstes müssen wir Datei für Datei durchgehen.
Das heißt, wir brauchen eine Möglichkeit einfach jede Datei in dem Ordner in die Finger zu bekommen.

Für den Job ist das os Modul mit der Funktion listdir() genau das Richtige!

import os

from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)

Mit os.listdir(PATH) bekommen wir einfach eine Liste aller Dateinamen, die sich in dem von uns angegebenen Pfad befinden.
Alles, was wir jetzt noch tun müssen ist, daraus einen für Python verständlichen Pfad zusammenzusetzen.

Python versucht, Pfade immer vom aktuellen Skript aus zu erstellen.
Das heißt, wir führen unseren angegebenen Ordnerpfad einfach mit dem Dateinamen zusammen.
Schon haben wir den vollständigen Pfad, um die Datei zu öffnen.

Auch dabei hilft uns das os Modul weiter.
Mit os.path.join() wird der Standard Separator für das Betriebssystem genommen und damit alle übergebenen Teile zusammengesetzt.

Mit dem Pfad können wir jetzt mit Hilfe von OpenPyxl die jeweilige Excel Datei öffnen:

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)
    workbook = load_workbook(file_path)

Und schon durchlaufen wir alle Excel Dateien in dem Ordner und öffnen jede Einzelne davon.

Schritt 4: Alle Arbeitsblätter in jeder Excel Datei durchlaufen

Jetzt haben wir also die Excel Dateien in der Hand.
Dann muss es ja jetzt mit den Arbeitsblättern weiter gehen.
Auch die können wir für jede Excel Datei wieder sehr einfach abfragen.
Dafür hilft uns das Attribut sheetnames, dass an jedem openpyxl Workbook vorhanden ist.

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)
    workbook = load_workbook(file_path)
    for sheet_name in workbook.sheetnames:
        sheet = workbook[sheet_name]

Das Attribut sheetnames liefert uns eine Liste mit den Namen der Arbeitsblätter, die in der Datei vorhanden sind.
Und Listen lassen sich natürlich durchlaufen 😉

Mit jedem Durchlauf bekommen wir also einen Namen für ein Arbeitsblatt der aktuellen Datei an die Hand.
Damit lässt sich jetzt auf das original workbook (also die Originaldatei) zugreifen und das jeweilige Arbeitsblatt heraus laden.

Eine Workbook Datei von openpyxl kann wie ein einfaches Dictionary verwendet werden.
So bekommen wir die Daten für das Arbeitsblatt in die Variable sheet.

Schritt 5: Inhalte übernehmen

Jetzt müssen wir die Daten aus dem original Arbeitsblatt in ein Arbeitsblatt in der neuen Excel Datei übernehmen.

Dafür erzeugen wir uns erstmal ein neues Arbeitsblatt.
Das geht mit openpyxl wieder ganz einfach mit der Funktion create_sheet(), die an jedem Workbook mitgeliefert wird.

Die Funktion kann einfach leer aufgerufen werden. Dann würde allerdings auch einfach ein Arbeitsblatt mit Standardname (etwas wie Sheet 1) angelegt werden.
Da wir hier direkt einen Namen vergeben wollen, müssen wir den nur an die Funktion übergeben.

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)
    workbook = load_workbook(file_path)
    for sheet_name in workbook.sheetnames:
        sheet = workbook[sheet_name]
        result_sheet = result_workbook.create_sheet(sheet_name)

Damit wird das neue Arbeitsblatt direkt mit dem richtigen Namen angelegt.
Über die Variable result_sheet haben wir jetzt Zugriff auf das neue, leere Arbeitsblatt.

Leer ist natürlich doof.
Das soll nicht lange so bleiben.
Also übertragen wir die Daten aus dem original Arbeitsblatt (sheet) in das neue Arbeitsblatt (result_sheet).
Leider muss das Zeile für Zeile erfolgen.
Allerdings geht das wieder fix mit einer Schleife:

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)
    workbook = load_workbook(file_path)
    for sheet_name in workbook.sheetnames:
        sheet = workbook[sheet_name]
        result_sheet = result_workbook.create_sheet(sheet_name)
        for row in sheet:
            result_sheet.append(row)

Ein Arbeitsblatt ist direkt ein iterierbares Objekt.
Wir können also einfach das Arbeitsblatt in einer Schleife durchlaufen und bekommen so Zeile für Zeile die Inhalte geliefert.
Die Zeilen können wir jetzt einzeln über .append() an das neue Arbeitsblatt schreiben.
Arbeitsblätter verhalten sich also genauso wie Listen.

Schritt 6: Ergebnis Excel Datei speichern

Abschließend müssen wir unsere “harte” Arbeit natürlich noch abspeichern.

Wenn also jede Zeile in jedem Arbeitsblatt in jeder Datei durchlaufen ist, dann haben wir einmal alle Arbeitsblätter aus allen Dateien in einer großen Excel Datei zusammengeführt.

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)
    workbook = load_workbook(file_path)
    for sheet_name in workbook.sheetnames:
        sheet = workbook[sheet_name]
        result_sheet = result_workbook.create_sheet(sheet_name)
        for row in sheet:
            result_sheet.append(row)

result_workbook.save(RESULT)

Ganz außen, nachdem alle for Schleifen abgeschlossen sind, rufen wir einmal save() an unserer Ergebnis Excel Datei auf.
Dabei übergeben wir unsere Variable RESULT.
Die haben wir am Anfang ja genau für den Zweck angelegt.
Um zu bestimmen wo und unter welchem Namen unser Ergebnis gespeichert werden soll.

Bonus: Dateitypen filtern

Fairerweise muss ich zugeben, dass das VBA Skript direkt noch nach Dateitypen filtert.

Filename = Dir(Path & "*.xls")

Hast du also auch Nicht-Excel Dateien in deinem Ordner, würde mein Skript aktuell mit einem Fehler abbrechen.

Das lässt sich aber auch ganz einfach beheben.
Dafür führen wir erstmal eine Liste mit Dateiendungen ein, die wir zulassen wollen:

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'
FILTER = ['xls', 'xlsx']

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    file_path = os.path.join(PATH, file)
    workbook = load_workbook(file_path)
    for sheet_name in workbook.sheetnames:
        sheet = workbook[sheet_name]
        result_sheet = result_workbook.create_sheet(sheet_name)
        for row in sheet:
            result_sheet.append(row)

result_workbook.save(RESULT)

In der Variablen FILTER kannst du jetzt alle Dateiendungen aufzählen, die du nutzen möchtest.

Jetzt müssen wir den Filter auch noch anwenden.
Mit jedem Durchlauf von os.listdir() bekommen wir einen Dateinamen inklusive Dateiendung an die Hand gegeben.
Eine Dateiendung ist immer durch einen . (Punkt) abgetrennt.

Wir müssen also nur den Namen an dem Punkt aufbrechen und prüfen, ob der hintere Teil in unserer Liste enthalten ist:

import os

from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook

PATH = 'daten'
RESULT = 'daten/result.xlsx'
FILTER = ['xls', 'xlsx']

result_workbook = Workbook(write_only=True)

for file in os.listdir(PATH):
    if file.rsplit('.')[1] in FILTER:
        file_path = os.path.join(PATH, file)
        workbook = load_workbook(file_path)
        for sheet_name in workbook.sheetnames:
            sheet = workbook[sheet_name]
            result_sheet = result_workbook.create_sheet(sheet_name)
            for row in sheet:
                result_sheet.append(row)

result_workbook.save(RESULT)

Mit rsplit(‘.’) wird der Dateiname von hinten her durchlaufen und beim ersten Vorkommnis von getrennt.
Damit entsteht ein Array mit zwei Teilen: [‘dateiname’, ‘endung’]
Über den Index 1 greifen wir jetzt auf die Endung zu und prüfen mit in einfach, ob diese Endung in unserer Filter Liste enthalten ist.

Nur wenn die Endung enthalten ist, durchlaufen wir die Logik.
Wenn nicht, dann geht’s weiter mit der nächsten Datei.

Zusammenfassung

Warum auch immer du die Arbeitsblätter von mehreren Excel Dateien zusammenführen willst. Mit dem Skript solltest du keine Probleme mehr haben.
Durchlaufe einfach alle Dateien, für jede Datei alle Arbeitsblätter und für jedes Arbeitsblatt kopierst du jede Zeile.
Ein paar wenige Zeilen Python Code können VIELE STUNDEN Arbeitszeit einsparen.
Und bewahren dich vor dem Sprung aus dem nächstgelegenen Fenster 😉