Power Query und das Excel-Binärformat XLSB

Wie hinreichend bekannt ist, werden aktuelle Excel-Mappen seit vielen Jahren im XLSX-Format abgespeichert. Dabei handelt es sich im Wesentlichen um ein komprimiertes XML-Format, welches die alten XLS-Dateien mit der Einführung von Excel 2007 abgelöst hat.

Nicht ganz so bekannt ist das Binärformat. Wahlweise lassen sich Excel-Mappen in diesem Format mit der Endung XLSB abspeichern. Einen Vorteil bringt das vor allem bei sehr großen Arbeitsmappen, denn durch dieses Binärformat schrumpft die Dateigröße zum Teil ganz erheblich. Aber gibt es auch Nachteile?

Bis vor kurzem hätte ich noch geantwortet: Nein, zumindest sind mir keine nennenswerten Einschränkungen bekannt. Wenn du aber mit Power Query arbeitest, gibt es aber doch ein paar Dinge, die näher betrachtet werden sollten.

Werfen wir mal einen Blick darauf!

Das Excel-Binärformat

Beim Speichern einer Arbeitsmappe lässt sich neben dem Standard-Format XLSX unter anderem auch das Binärformat XLSB auswählen:

Speichern einer Arbeitsmappe im Binärformat XLSB

Falls du mit riesigen Arbeitsmappen und entsprechenden Dateigrößen zu kämpfen hast, solltest du dieses Format einmal ausprobieren. Im Artikel 7 ultimative Excel-Tipps zum Verringern der Dateigröße (+ Bonus-Tipp) bin ich darauf schon einmal eingegangen.

Neben einer oftmals verringerten Dateigröße haben sie auch den Vorteil, dass sich in diesem Format auch Makros abspeichern lassen. Bei „normalen“ Arbeitsmappen ist hierfür ja immer das separate XLSM-Format notwendig.
Dieser Punkt könnte aber im Einzelfall auch zu einem Nachteil werden:
Der E-Mail-Versand von XLSM- oder XLSB-Dateien an externe Empfänger dürfte immer häufiger an etwas rigoroseren Mailservern oder Firewalls scheitern.

Kommen wir aber zum eigentlichen Thema: Power Query.

Power Query und das Binärformat

Die gute Nachricht vorweg: Man kann grundsätzlich auch innerhalb von XLSB-Dateien Power Query ohne Probleme und nach Lust und Laune einsetzen.

Aber!
Wenn man jedoch mit Power Query auf eine andere XLSB-Datei zugreift, gibt es einige nicht so schöne Überraschungen.

Der Leser Joachim hat mich unlängst auf dieses Thema aufmerksam gemacht (vielen Dank nochmal an dieser Stelle!). Denn er hatte beobachtet, dass der Zugriff über Power Query auf eine Excel-Binärdatei bei ihm ewig dauert. Das hat mich zu ein paar Tests und Untersuchungen ermuntert, die ich dir hier vorstellen möchte.

Die folgenden 3 Punkte habe ich mir dabei näher angesehen:

• Ladezeit von der Dateiauswahl bis alle Objekte im Navigator-Fenster angezeigt werden
• Verfügbare Datenobjekte
• Anzahl der verfügbaren Spalten

Zeitdauer bis zum Öffnen

Die Zeitmessungen habe ich mit 3 verschiedenen Szenarien durchgeführt, jeweils mit einer XLSX und einer XLSB-Datei. In einer neuen, leeren Arbeitsmappe habe ich über Power Query nacheinander auf diese beiden Arbeitsmappen zugegriffen:
Menü „Daten | Daten abrufen | Aus Datei | Aus Excel-Arbeitsmappe“

Dabei wurde die Zeit gestoppt, beginnend ab dem Moment, wo ich im „Daten importieren“-Dialogfenster die jeweilige Datei ausgewählt und auf die „Importieren“-Schaltfläche geklickt habe. Bis zu dem Zeitpunkt, an dem das Navigator-Fenster von Power Query geöffnet und alle Datenobjekte links in der Baumstruktur vollständig aufgelistet wurden.

Zeitmessung beginnt beim Klick auf ‚Importieren‘

Testdatei 1:
Für den ersten Test habe ich eine Arbeitsmappe mit folgendem Aufbau vorbereitet:

• Insgesamt 4 Arbeitsblätter mit den Namen Blatt1a, Blatt1b, Blatt2, Blatt3
• Blatt1a enthält eine formatierte („intelligente“) Tabelle tbl_Datentabelle1 mit 5.000 Datensätzen und 300 Spalten
• Blatt1b enthält eine formatierte Tabelle tbl_Datentabelle2 mit ebenfalls 5.000 Datensätzen und 50 Spalten
• Blatt2b enthält eine kleine Liste mit 6 Datensätzen und 300 Spalten. Für den Listenbereich wurde im Namensmanager der Name ber_Datentabelle festgelegt
• Blatt3 ist komplett leer

Diese Arbeitsmappe wurde einmal im normalen XLSX-Format gespeichert und hatte eine Dateigröße von rund 13,2 MB.
Eine ansonsten identische Kopie im XLSB-Binärformat kam auf eine Dateigröße von rund 11,1 MB. Das entspricht einer Größenreduktion von etwa 16 %.

Ergebnis:
Zeitdauer für die XLSX-Datei: ca. 5 Sekunden
Zeitdauer für die XLSB-Datei: ca. 45 Sekunden bis zum Navigator-Fenster, weitere ca. 90 Sekunden, bis alle Objekte aufgelistet waren.

Es dauerte mit der Binärdatei also insgesamt ca. 2:15 Minuten, bis das Navigator-Fenster vollständig geladen war und ich dort ein Datenobjekt überhaupt erst auswählen konnte! Verglichen mit den 5 Sekunden bei der XLSX-Datei also um den Faktor 27 länger!

Puh, das war schon ziemlich mühsam.

 

Testdatei 2:
Die zweite Testdatei (jeweils XLSX und XLSB) hatte folgenden Aufbau:

• Insgesamt 3 Arbeitsblätter (Blatt1, Blatt2, Blatt3)
• Blatt1 enthält eine formatierte („intelligente“) Tabelle tbl_Datentabelle mit 15.000 Datensätzen und 300 Spalten
• Blatt2 enthält eine kleine Liste mit 6 Datensätzen und 300 Spalten. Für den Listenbereich wurde im Namensmanager der Name ber_Datentabelle festgelegt
• Blatt3 ist komplett leer

Dateigröße im XLSX-Format: ca. 34 MB
Dateigröße im XLSB-Format: ca. 28 MB

Ergebnis:
Zeitdauer für die XLSX-Datei: 5 Sekunden.
Zeitdauer für die XLSB-Datei: 13 Minuten bis zum Navigator-Fenster, danach weitere 17 Minuten bis zur Anzeige aller Objekte.

Insgesamt also ca. 30 Minuten, bis eine Objektauswahl im Navigator-Fenster überhaupt möglich war!

 

Testdatei 3:
Und schließlich der letzte Test mit einer noch größeren Datei.

• 3 Arbeitsblätter (Blatt1, Blatt2, Blatt3
• Blatt1 enthält eine formatierte („intelligente“) Tabelle tbl_Datentabelle mit 15.000 Datensätzen und 300 Spalten
• Blatt2 enthält die gleichen Daten als Liste (15.000 Datensätze, 300 Spalten) mit dem definierten Namen ber_Datentabelle
• Blatt3 enthält ebenfalls die gleichen Daten als Liste (15.000 Datensätze, 300 Spalten), nur ohne definierten Namen

Dateigröße im XLSX-Format: ca. 79 MB
Dateigröße im XLSB-Format: ca. 61 MB

Ergebnis:
Zeitdauer XLSX-Format: 5 Sekunden.
Zeitdauer XLSB-Format: Kein Ergebnis. Nach ca. 35 Minuten brach der Import mit einer Fehlermeldung ab.

Import bricht nach 35 Minuten ab

Es lässt sich festhalten:
Während die Zugriffszeiten bei den XLSX-Dateien unabhängig von der Dateigröße mehr oder weniger identisch blieben, wurde der Zugriff bei den Binärdateien mit steigender Dateigröße zunehmend unerträglich bis schließlich gar nicht möglich.

Hier nochmal die Übersicht:

Übersicht der verschiedenen Ladezeiten

Angezeigte Datenobjekte im Navigator

Im Navigator-Fenster der XLSX-Datei wurden mir die unterschiedlichen Datenobjekte erwartungsgemäß mit ihren entsprechenden Symbolen aufgelistet:

• 2 Tabellenobjekte (tbl_Datentabelle1 und tbl_Datentabelle2)
• 4 Arbeitsblätter (Blatt1a, Blatt1b, Blatt2 und Blatt3)
• 1 benannter Bereich ber_Datentabelle

In der identisch aufgebauten Binärdatei ist davon folgendes übriggeblieben:

• 4 Arbeitsblätter (Blatt1a, Blatt1b, Blatt2 und Blatt3)
• 1 benannter Bereich ber_Datentabelle

Verfügbare Datenobjekte im Navigator

In der Konsequenz bedeutet das, dass man beim Import einer Excel-Binärdatei die Vorteile einer intelligenten Tabelle leider in die Tonne treten kann. Das heißt, man muss sich auch hier wie bei einer normalen Liste mühsam über das Arbeitsblatt an die gewünschte Quelltabelle herantasten, unnötige Zeilen darüber oder Spalten links davon manuell entfernen und auch die erste Datenzeile in einem eigenen Schritt als Kopfzeile übernehmen.

Sehr unbefriedigend!

Und das war leider noch nicht alles…

 

Verfügbare Spaltenanzahl

Wie eingangs beschrieben, hatte die erste Datentabelle einen Umfang von 5.000 Zeilen und 300 Spalten. In der XLSX-Datei lässt sich im Power Query-Editor auch problemlos auf alle 300 Spalten zugreifen.

In der XLSB-Binärdatei hingegen ist der Zugriff auf maximal 255 Spalten beschränkt:

Nach 255 Spalten ist Schluss

Alles, was danach kommt, lässt sich somit in Power Query nicht mehr weiterverarbeiten. Auf diese Einschränkung macht auch Microsoft aufmerksam:
Power Query: Spezifikationen und Grenzwerte in Excel

Das mag in vielen Fällen kein Problem sein, denn die wenigsten Anwender werden vermutlich Quelltabellen mit mehr als 255 Spalten importieren müssen. Im Einzelfall kann das aber ein K.O.-Kriterium darstellen.

Weitere Tests habe ich an dieser Stelle dann nicht mehr vorgenommen.

Fazit

Auch wenn es in diesem Beitrag vielleicht etwas anders herüberkommt:
Das Excel-Binärformat XLSB hat grundsätzlich weiterhin absolut seine Berechtigung und ist in den allermeisten Fällen auch völlig problemlos zu nutzen. Gerade wenn es darum geht, die Dateigröße einer Arbeitsmappe zu verringern, ist dieses Dateiformat immer noch das Mittel der Wahl.

Auch Power Query-Abfragen innerhalb einer solchen Binärdatei sind nach meiner bisherigen Erfahrung überhaupt kein Problem.

Solltest du aber beabsichtigen oder gezwungen sein, aus einer solchen Binärdatei heraus Daten per Power Query in eine andere Arbeitsmappe zu importieren, dann sei gewarnt und stelle dich auf Ärger ein:

• Massiv längere Ladezeiten beim Einrichten der Abfragen
• sehr zähes Verhalten bei der späteren Bearbeitung
• nicht alle Datenobjekte stehen zur Verfügung („intelligente“ Tabellen)
• Beschränkung beim Import auf maximal 255 Spalten

Für diese konkreten Fälle empfiehlt sich daher nach wie vor, die Datei lieber im klassischen XLSX-Format zu speichern. Die Dateigröße erhöht sich dadurch natürlich, aber Power Query verhält sich auch wieder so, wie man es gewohnt ist.

 
Hast du schon Erfahrungen mit Power Query im Zusammenhang mit XLSB-Dateien gemacht? Dann lass es uns gerne in den Kommentaren wissen!
 

Das könnte dich auch interessieren:

Vorher schon wissen, was hinterher rauskommt

Eingabemöglichkeiten einschränken

Dem Verbrecher auf der Spur

Das doppelte Lottchen, oder: Dublettensuche mit Excel