Mit Power Query können Sie Textdateien nach Excel importieren. Es gibt zwei Sorten von Textdateien: Textdateien im CSV-Format und Textdateien mit fester Satzlänge. Bei CSV-Dateien sind die Daten durch Trennzeichen wie Tabulator, Komma oder Semikolon getrennt. In der ersten Zeile stehen in der Regel die Spaltenüberschriften. Bei Textdateien mit fester Satzlänge fehlen diese Infos. In diesem Tipp erfahren Sie, wie Sie Textdateien mit fester Satzlänge dennoch nach Excel importieren können.

Ausgangsbeispiel

In der Textdatei, deren Daten importiert werden sollen, befinden sich stündliche Wetterdaten, die in der Stadt Hattingen an der Ruhr am 18.07.2023 gemessen wurden. Die Textdatei sieht im Texteditor so aus:

Textdatei mit Wetterdaten

Jede Zeile in der Textdatei hat eine Länge von 57 Zeichen. Um die Datei zu importieren, benötigen Sie Informationen über den Aufbau einer einzelnen Zeile. Sie müssen wissen, an welcher Position in welcher Länge welche Daten stehen. Die Datei wurde zusammengestellt aus Daten, die von einen Webdienst geladen wurde. Sie erhalten vom Anbieter der Daten in der Regel eine Dokumentation über den Aufbau der Datei. Die obige Textdatei hat folgenden Aufbau:

Struktur der Datei mit den Wetterdaten

In der Spalte F der obigen Tabelle stehen die Positionen, die Sie später in Power Query angeben müssen. Die Positionen sind 0-basiert, d. h. alle Positionsangaben sind um 1 niedriger als die Positionen in der Spalte A.

Import der Textdatei

Um die Textdatei mit den Wetterdaten zu übernehmen, gehen Sie bitte folgendermaßen vor:

1. Legen Sie in Excel eine neue Arbeitsmappe an.
2. Klicken Sie im Menüband auf die Registerkarte Daten und dann in der Gruppe Daten abrufen und transformieren auf die Schaltfläche Aus Text/CSV.
3. Suchen Sie die Textdatei im Explorer. Beim Laden der Textdatei analysiert Power Query die Daten. Nach der Analyse sieht das Dialogbild so aus:
   
   Power Query geht davon aus, dass es sich um eine Textdatei mit einem benutzerdefinierten Trennzeichen handelt. Als Trennzeichen werden mehrere hintereinanderstehende Leerzeichen angenommen. Deshalb werden 2 Spalten identifiziert. Da es sich um eine Textdatei mit einer festen Satzlänge handelt, müssen Sie die Parameter ändern.
4. Wählen Sie im Kombinationsfeld Trennzeichen den Eintrag Feste Breite.
5. Geben Sie im Eingabefeld unter dem Trennzeichen die 0-basierten Positionen aus der Spalte F der obigen Dateistruktur an. Die Positionen müssen durch ein Komma voneinander getrennt werden. In unserem Beispiel lautet die Eingabe: 0, 5, 35, 47, 50, 52 und 54.
6. Klicken Sie aus dem Eingabefeld heraus: Danach sieht das Dialogbild folgendermaßen aus:


Laden Textdatei mit fester Breite in Power Query

7. Klicken Sie abschließend auf die Schaltfläche Daten transformieren. Die Daten werden im Power Query Editor dargestellt.

Transformationsschritte im Power Query Editor

Führen Sie folgende Schritte durch, bevor Sie die Daten nach Excel übernehmen:

1. Löschen Sie den zweiten, automatisch eingefügten Schritt Geänderter Typ.
2. Benennen Sie die 7 Spalten bis Column7 folgendermaßen um: PLZ, Ort, Zeitstempel, Temperatur, Luftfeuchtigkeit, Windgeschwindigkeit und Sichtweite.
3. Die Spalten Temperatur und Sichtweite sollen den Datentyp Dezimalzahl erhalten. Markieren Sie beide Spalten und wählen Sie im Menüband auf der Registerkarte Transformieren für den Datentyp den Eintrag Dezimalzahl aus.
4. Die Spalten Luftfeuchtigkeit und Windgeschwindigkeit sollen den Datentyp Ganze Zahl erhalten. Markieren Sie beide Spalten und wählen Sie im Menüband auf der Registerkarte Transformieren für den Datentyp den Eintrag Ganze Zahl aus.
5. Die Werte in den Spalten Temperatur und Sichtweite müssen jeweils durch 10 dividiert werden, da die letzte Ziffer eine Nachkommastelle darstellt. Markieren Sie zunächst die Spalte Temperatur und klicken Sie danach auf der Registerkarte Transformieren auf die Schaltfläche Standard. Wählen Sie den Menüeintrag Dividieren. In dem sich öffnenden Dialogbild gehen Sie die Zahl 10 ein. Klicken Sie auf die Schaltfläche OK.
6. Wiederholen Sie die Schritte für die Spalte Sichtweite.
7. Als letzte Spalte soll die Spalte Zeitstempel bearbeitet werden, um dort gültige Datums- und Uhrzeitwerte zu erhalten. Markieren Sie die Spalte Zeitstempel und klicken Sie danach im Menüband auf der Registerkarte Spalte hinzufügen auf die Schaltfläche Spalte aus Beispielen. Wählen Sie den Eintrag Aus Auswahl. Es erscheint ein neues Dialogbild mit der Spalte1.
8. Geben Sie in der ersten Zeile den folgenden Wert ein: 18.07.2023 00:21.
9. Geben Sie in der zweiten Zeile den folgenden Wert ein: 18.07.2023 01:57.


Spalte aus Beispiel

10. Drücken Sie die ENTER-Taste. Die anderen Zellen in der Spalte werden automatisch gefüllt.
11. Klicken Sie auf die Schaltfläche OK, um das Dialogbild zu schließen. Im Power Query-Editor gibt es eine neue Spalte Benutzerdefiniert. Die automatisch erzeugte Formel ist für unser konkretes Beispiel richtig. Sie hat aber einen Schönheitsfehler. In der Formel sind Tag und Monat als Konstanten angegeben. Die Formel soll korrigiert werden, damit Sie auch Wetterdaten von anderen Tagen übernehmen können.
12. Klicken Sie im Power Query-Editor in der Liste der Anwendeten Schritte auf den letzten Schritt Benutzerdefinierte Spalte hinzugefügt. Das Dialogbild sieht so aus:
    

    Automatisch erzeugte benutzerdefinierte Spalte

13. Geben Sie im Eingabefeld Neuer Spaltenname den Ausdruck Zeitpunkt ein.
14. Ändern Sie die benutzerdefinierte Spaltenformel ab, um die Konstante 18.07 zu ersetzen:.= Text.Combine({Text.Middle([Zeitstempel],6,2), “.”, Text.Middle([Zeitstempel],4,2), “.”, Text.Start([Zeitstempel], 4), ” “, Text.Middle([Zeitstempel], 8, 2), “:”, Text.Middle([Zeitstempel], 10)}). Das Dialogbild sieht danach folgendermaßen aus:

    

15. Die Formel setzt die Datumsangabe mit insgesamt 12 Zeichen neu zusammen. Die Positionsangaben in der Formel sind 0-basiert. Klicken Sie auf die Schaltfläche OK.
16. Markieren Sie die Spalte Zeitpunkt und wählen Sie im Menüband auf der Registerkarte Transformieren für den Datentyp den Eintrag Datum/Uhrzeit
17. Löschen Sie die Spalte Zeitstempel aus der Tabelle.
18. Verschieben Sie die Spalte Zeitpunkt hinter die Spalte Ort. Weitere Transformationsschritte sind nicht notwendig
19. Klicken Sie abschließend im Menüband auf der Registerkarte Datei auf die Schaltfläche Schließen & laden. Die Daten werden in ein neues Tabellenblatt übertragen.

Das Excel-Tabellenblatt hat folgendes Aussehen:

Excel-Tabellenblatt mit übersichtlicher Darstellung der Daten

Fazit

Mit Power Query importieren Sie nicht nur CSV-Textdateien mit Trennzeichen zwischen den Daten, sondern auch Textdateien mit fester Satzlänge. Voraussetzung für den Import ist, dass Sie den Aufbau der Textdatei kennen oder aus den Daten ableiten können.

Hinweis

Die im Power Query einzugebenden Positionszahlen beim Import von Textdateien mit fester Breite können Sie mit der Funktion TEXTVERKETTEN ermitteln. In unserem Beispiel befinden sich die Positionsnummern für Power Query in den Zellen F2 bis F8 des Tabellenblatts mit der Dateistruktur.

=TEXTVERKETTEN(“,”;;F2:F8):

Das Ergebnis lautet: 0,5,35,47,50,52,54