Web-Import mit Umwegen

Playlist eines Radiosenders aus dem Internet

In einem Forum suchte ein Fragesteller eine Lösung für diese Aufgabe:

Auf dieser Internetseite: http://de.playlistbase.com/de/playlist/ffh werden verschiedene Playlists veröffentlicht. Das Ziel ist es nun, alle aufgeführten Songs des bisherigen Tages oder auch die eines früheren Datums (einschließlich der nicht in der zuerst gezeigten Darstellung und erst nach expandieren sichtbaren Daten) in eine Excel-Liste zu übertragen.

Als ich mich des Themas annahm, lag eine fast zufriedenstellende Lösung in VBA vor. Die Haupt-Einschränkung war gewiss, dass durch den Transfer der Daten von der Internetseite zu viele grafische Elemente die Leistungsfähigkeit des Programms ausbremsen. Ich überlegte, ob die Möglichkeit besteht, das Ganze in Power Query, also ohne VBA zu realisieren. Ich merkte rasch: Ja es ist machbar, aber …

Die nach dem Aufruf der Seite gezeigten ersten Titel stehen in einer „ganz normalen“ HTML-Tabelle und deren direkter Import bereitet mit Power Query auch keinerlei Schwie­rig­keiten. Das Problem stellt sich erst dann, wenn die Tabelle durch einen Klick auf die entsprechende Schaltfläche links unterhalb der Tabelle erweitert, expandiert wird. Power Query erkennt diese Tabelle dann nicht mehr, weil sie durch den Klick erst während der Laufzeit per Javascript erzeugt wird, der Link zur Seite in dieser Darstellung aber immer noch der gleiche ist. Schade, dadurch ist ein Stück Automatisierung verloren gegangen.

▲ nach oben …

Vorarbeit

Wenn Sie Power Query schon etwas kennengelernt haben dann wissen Sie auch, dass Sie -ähnlich wie in VBA- viel Automatisierung nutzen können. Wie eben schon erwähnt können Sie diesen Teil der Vorarbeit nicht durch Power Query erledigen lassen. Sollten Sie also verschiedene Tage oder Zeiträume erfassen wollen, werden Sie diese Vorarbeit jedes Mal neu per Hand durchführen müssen. Gehen Sie dazu so vor:

  • In einem beliebigen Web-Browser öffnen Sie die Internetseite.
  • Wählen Sie den Zeitraum, der angezeigt werden soll.
  • Da ja normalerweise nicht alle Titel angezeigt werden, muss die Liste expandiert, erweitert werden. Dazu ein Klick unterhalb der Liste auf die entssprechende (in der Abb. gelb markierte) Schaltfläche:

Zeigen Sie mit einem Klick hierauf alle Songs des Tages an

  • Nach einer Kontrolle, ob das die richtigen Daten sind, speichern Sie die Seite auf Ihrer Festpaltte. Das Händling ist von Browser zu Browser unterschiedlich, aber jeder mir bekannte Browser bietet solch eine Möglichkeit. Meist ist es ein Menüpunkt „Speichern unter“ oder ähnlich. Fast immer hilft schon die Tastenkombination StrgS. 💡
  • Am besten legen Sie für diese Dateien ein neues Verzeichnis an, da die Einzel-Datei und das generierte Verzeichnis immer in einer Ebene liegen müssen.
  • Wenn Sie diese Aktion nicht nur einmalig durchführen wollen, dann sollten Sie einen prägnanten aber universell verwendbaren Filenamen verwenden, den Sie immer wieder nutzen. Der Grund dafür: Power Query aktualisiert auf Mausklick die Daten der Abfrage und greift auf die Datei gleichen Namens am gleichen Ort zu. Ich habe beispielsweise das vorgegebene Datum aus diesem Filenamen entfernt; Original:
    FFH Playlist – Datum_ 04.06.17.html und ich habe daraus FFH_Playlist.html gemacht.
  • Achten Sie darauf, dass eine *.htm bzw. *.html-Datei erzeugt wird und ein Verzeichnis gleichen Namens. – Und ja, das dauert einige Sekunden …
  • Merken Sie sich genau den vollen Pfad einschließlich des Dateinamens mit der Erweiterung, idealerweise schreiben Sie sich diesen auf. Sie brauchen ihn gleich noch.

Möchten Sie sich diese Vorarbeit ersparen und sich auf den Power Query-Teil konzentrieren, dann können Sie die bereits kopierte PlayList mit den Rohdaten hier im Blog herunterladen. Es ist eien *.zip-Datei, weil sich dahinter wie schon beschrieben eine Einzeldatei und ein Verzeichnis verbergen. Beide müssen im gleichen (Unter) Verzeichnis liegen.

▲ nach oben …

Der Power Query-Teil

Der Einstieg ist in den Versionen Excel 2010/2013 unterschiedlich zur Version 2016. Darum werden die ersten Schritte an dieser Stelle getrennt beschrieben.

Versionen 2010/13

  • Falls erforderlich, installieren Sie das Power Query Add-In. Mehr dazu hier.
  • Aktivieren Sie das Menü-Register Power Query.
  • Im Menüband wählen Sie den Eintrag Aus dem Web.
  • Ab hier sind die Versionen weitgehend identisch. Weiter geht’s hier.

Version 2016

  • Klicken Sie auf die Registerkarte Daten.
  • Gruppe Abrufen und transformieren | Neue Abfrage | Aus anderen Quellen | Aus dem Web.

Es erscheint ein Abfragedialog. Geben Sie in das Textfenster den vollen Pfad der eben gespeicherten Website ein:

Erster Schritt des Imports der Daten

Erster Schritt des Imports der Daten

In meinem Fall sieht der Pfad so aus: D:\Data\FFH_Playlist.html. Dieses ist schon der universell verwendbare Filename für spätere Wiederverwendbarkeit mit anderen Inhalten. Nach einem OK und kurzer Wartezeit öffnet sich ein Dialogfenster mit dem Navigator. Klicken Sie gerne nacheinander auf die kleinen Symbolischen Icons einer Tabelle. Bei Document werden Sie nicht gerade viel sehen, aber wenn Sie auf Table 0 klicken, dann ist in der Vorschau rechts deutlich zu erkennen, dass das die gewünschten Daten sind:

Vorschau der Daten im Navigator

Vorschau der Daten im Navigator

Klicken Sie nun auf Bearbeiten, um die hier sichtbaren und zu importierenden Daten im Abfrage-Editor zu betrachten und auch gleich den Erfordernissen anzupassen. Sie sehen dann dieses Bild, natürlich mit den entsprechenden Titeln, welche Sie vorher von der Website gespeichert hatten:

Die importierten Daten im Abfrage-Editor, bereit zur Bearbeitung

Die importierten Daten im Abfrage-Editor, bereit zur Bearbeitung

An dieser Stelle die Forderung des Fragestellers für die endgültige Darstellung:

  • Spalte A: Ein Datum (hier nicht genau definiert).
  • Spalte B: Die Uhrzeit, welche in der Tabelle (letzte Spalte) vermerkt ist.
  • Spalte C: Interpret/en
  • Spalte D: Song, Titel

Außerdem soll jedes einzelne Wort bei den Songs und den Interpreten Groß geschrieben werden also nicht nur das erste Wort; und zwar wirklich jedes einzelne. Beispiel: Im Original steht dort Water under the bridge, daraus soll dann Water Under The Bridge werden.


Die importierte Tabelle sieht schon einmal recht ordentlich und auch geordnet aus. Mehrere Spalten, und auch die relevanten Inhalte sind -mit Ausnahme des Datums- alle vorhanden. Zu Beginn stellt sich aber die Frage, ob alle Spalten für die spätere Darstellung gebraucht werden. Dazu ist es sinnvoll, jede der Spalten, die nicht ganz offensichtlich einen verwertbaren Inhalt enthält, einmal kurz zu checken und anschließend zu entscheiden, ob sie gelöscht werden kann oder nicht. Das betrifft im Grunde genommen alle Spalten mit Ausnahme Column2 und Column7. Da ist auf den ersten Blick klar, dass diese Inhalte später gezeigt werden sollen.

Statt jedes Mal alle Zeilen der Spalten durchzublättern, klicke ich in der Überschrift auf das Dropdown-Symbol  und sehe dann ja sofort, welche Inhalte in den Zeilen dieser Spalte stehen. Column1, Column3 und Column6 enthalten nur leere Felder. Da fällt die Entscheidung zum löschen der Spalten ganz leicht. Column4 und Column5 enthalten auch nur in den Text Kaufen bzw. Lyrisch und sind für die spätere Darstellung auch nicht relevant. – Ich klicke jetzt in die Überschrift Column2, Strg und dann ein Klick in Column7. Jetzt sind die beiden Spalten markiert, die verwertbare Inhalte haben. Rechtsklick in eine der beiden Überschriften und im Kontextmenü klicken Sie auf Andere Spalten entfernen. Und Voila, keine überflüssigen, störenden Spalten sind mehr zu sehen. 😎

Spätestens jetzt sollten Sie der Abfrage einen „sprechenden“ Namen geben. Im rechten Seitenfenster erkennen sie, dass die Query derzeit den Namen der Tabelle hat: Table 0. Ein Klick in das Textfeld, StrgA um den gesamten Inhalt zu markieren und dann einfach den neuen Namen schreiben: Import (bereinigt). Und wenn Sie schon beim ändern der Namen sind, gönnen Sie in der Überschrift Column7 auch etwas vernünftiges; ein Klick in die Überschrift, F2 markiert alles und dann schreiben Sie Zeit.

Bevor Sie sich an Column2 machen, sollten Sie erst einmal auf die Schaltfläche Schließen & laden klicken. Dadurch wird einerseits das Fenster mit dem Abfrage-Editor geschlossen und ein neues Arbeitsblatt mit diesen Daten erstellt. Und es schadet auch nicht, wenn Sie per StrgS die gesamte Datei noch einmal an diesem Stand speichern.

Jetzt geht es an die Analyse der ersten Spalte. Was auffällt: Es sind offensichtlich 2 logische Zeilen in einer physischen, einer Abfrage-Zeile. In der 1. Zeile steht der Titel des Songs, in der 2. Zeile der oder die Interpreten. Und jede Sub-Zeile mit den Interpreten hat noch einen „Anhang“, welcher ohne Leerzeichen direkt an den Interpreten positioniert worden ist. Hier stellen sich 2 Forderungen:

  • Die 2 Zeilen einer Zelle müssen in 2 Spalten aufgeteilt werden.
  • Der Text Geben Sie Ihre Meinung! muss in jeder Zeile entfernt werden.

Auch wenn Sie die Änderungen an dieser Stelle in der Excel-Tabelle vornehmen könnten, was wahrscheinlich sogar für sie einfacher wäre, bringt das nicht wirklich viel. Power Query kann das gewiss genauso gut, ja sogar besser; denn wenn sie später weitere Daten aus dem Internet laden, geschieht dieser Vorgang ganz ohne ihr Zutun. Dazu müssen Sie erst einmal die Abfrage wieder öffnen. Das geht beispielsweise durch einen Doppelklick auf die Abfrage Import (bereinigt) im rechten Seitenfenster, Sie können auch auf diesen (grün hinterlegten) Eintrag zeigen und im sich für kurze Zeit öffnenden Dialogfenster unten auf Bearbeiten klicken; oder Sie klicken im Menü-Register des Excel auf Abfrage und dann auf Bearbeiten. Und ja, es gibt noch mehr Möglichkeiten … 🙄

Der Abfrage-Editor ist nun wieder geöffnet. Und sie sollten erst einmal zwei der einfacheren Schritt durchführen: Der überflüssige Text soll entfernt werden und die Sache nit der Großschreibung jedes Wortes. Da ich aus Prinzip faul bin und auch dazu neige, Tippfehler einzubauen, gehe ich für das löschen folgenden Weg:

  • Ich klicke in die erste Zeile der Spalte Column2.
  • Unterhalb der tabellarischen Darstellung wird mir dann ja der Inhalt der markierten Zelle angezeigt. Dort markiere ich genau jenen Text, den ich in allen Zeilen dieser Spalte entfernen will:

Exakt den zu ersetzenden Text markieren

Exakt den zu ersetzenden Text markieren

  • Ein kleiner Tipp dazu: Shift und bzw. haben mir bei der exakten Markierung sehr geholfen, weil vor „Geben“ kein Lerrraum ist.
  • Diese Markierung kopiere ich per StrgC in die Zwischenablage.
  • Rechtsklick in die Überschrift Column2 und im Kontextmenü den Punkt Werte ersetzen… wählen.
  • Bei Zu suchender Wert den Inhalt der Zwischenablage per StrgV einfügen, Ersetzen durch lassen Sie leer, denn der gesuchte Wert soll ja durch nichts ersetzt, sprich gelöscht werden. – OK und freuen. 😉 

Die Forderung, jedes Wort in Großbuchstaben darzustellen hört sich komplex an ist aber mit wesentlich weniger Schritten als die Lösch-Aktion eben zu realisieren. Rechtsklick in die Überschrift und im Kontextmenü finden Sie etwas oberhalb der Mitte den Punkt Transformieren. Beim zeigen darauf öffnet sich automatisch ein Untermenü und hier wählen Sie naturgemäß Ersten Buchstaben im Wort großchreiben. – Das war’s auch schon. So leicht kann eine schwierig erscheinende Aufgabe zu lösen sein. 😆 

Um nun die 1. und die 2. Zeile in einer Zelle in 2 Spalten zu trennen müssen Sie wissen, warum das ein 2-Zeiler ist. Ganz offensichtlich ist, dass die Spalte breit genug ist, um den kompletten Text aufzunehmen. Es ist also nicht so, dass aus diesem Grunde ein Umbruch vorgenommen worden ist. Grundsätzlich können Sie davon ausgehen, dass hier eine gewollte Zeilenschaltung vorliegt. Und ich versichere Ihnen, das ist hier auch der Fall. Markieren Sie die Überschrift Column2 mit einem Rechtsklick, Spalte teilen | Nach Trennzeichen… Es öffnet sich ein Dialog und es ist durchaus denkbar, dass Power Query von sich aus zu erkennen versucht hat, wie jede einzelne Textspalte aufgeteilt werden soll:

Power Query schlägt fälschlicherweise einen Wagenrücklauf -CHR(13)- vor ...

Power Query schlägt fälschlicherweise einen Wagenrücklauf -CHR(13)- vor …

Diese Einstellung ist nicht exakt das, was sie brauchen; sollte ihre Version von Power Query das so alleine erkannt haben, dann werden Sie einen Teil des Weges zu Fuß gehen müssen. Wählen Sie dazu nach einem Klick auf das Dropdown-Symbol bei Trennzeichen eingeben erst einmal den Eintrag —Benutzerdefiniert—. Klicken Sie in die leere Textzeile darunter; dann ganz unten Sonderzeichen einfügen und wählen Sie dort nicht Wagenrücklauf (das hatte Power Query von alleine gewählt) sondern Zeilenvorschub (was korrekt ist). Der Unterschied: In der 2. Textzeile steht dann nicht #(cr) sondern #(lf). Ein kleiner aber feiner Unterschied. 💡 Denn beim Wagenrücklauf würden aus den 2 Zeilen in einer Zelle dann tatsächlich 2 Zeilen in einer Spalte, was ja nicht wirklich gewünscht ist. Mit der Zeilenschaltung (entspricht in Excel übrigens ZEICHEN (10) als Funktion) erreichen Sie das gewünschte Ziel: Es werden 2 Spalten, die an der richtigen Stelle getrennt worden sind. – Und ja, jetzt wo Sie es wissen: Sie können die Zeichenfolge auch direkt in das Feld eingeben oder bei fehlerhafter Vorgabe durch Power Query selbst ändern.

Durch Erfahrung klug geworden … Hier ist im Laufe der Zeit die Sicherheit wichtiger geworden als die Bequemlichkeit. Zu oft habe ich gedacht: „Da wird schon nichts passieren.“ Und dann hat sich der Rechner doch aufgehängt und ein gutes Stück Arbeit war verloren. Darum finde ich es an der Zeit, die Datei jetzt erst (noch) einmal zu speichern. Sicher ist sicher…

Vom Prinzip her ist das Ziel erfüllt. Die beiden ersten Überschriften sollten noch angepasst werden, geben Sie der 1. Spalte den Namen Song und der 2. Spalte Interpret. Bleibt jetzt nur noch ein größerer Schritt, bei dem sie eine grundsätzliche Entscheidung treffen müssen. Es geht um das Datum.

▲ nach oben …

Wenn Sie diese Prozedur des einlesens einer Playlist nur ein einziges Mal durchführen wollen bzw. werden, dann ist es gewiss am einfachsten, wenn sie die direkt in die nach dem Import durch Power Query erstellte und gespeicherte Excel-Tabelle eine Spalte mit dem gewünschten Datum eintragen. Allerdings wird diese Spalte nicht automatisch ein Teil der ursprünglichen Tabelle sein. Gehen Sie nun so vor:

  • Schließen Sie in die Abfrage durch Schließen & laden.
  • Klicken Sie in die durch Power Query erstellte Excel-Tabelle, die derzeit auch nur als einzige geöffnet ist.
  • Tragen Sie in D1 die Überschrift Datum ein.
  • In D2 schreiben Sie das gewünschte Datum als Formel. Beispielsweise: ="04.06.2017". Das ist dann zwar ein Text, aber das lässt sich problemlos bei Bedarf in Power Query ändern bzw. passiert später als „Nebenprodukt“ eines anderen Vorgangs von alleine.
  • Anschließend ein Klick irgendwo in die Intelligente Tabelle.
  • Im Register Tabellentools | Entwurf | Gruppe Eigenschaften markieren Sie Tabellengröße ändern.
  • Per Shift erweitern Sie den Tabellenbereich um eine Spalte nach rechts und bestätigen Sie die Vorgabe (hier $A$1:$D$80) per OK.
  • Jetzt einen Doppelklick auf das Ausfülltkästchen und das Datum wird bis zur letzten Zeile der Tabelle nach unten kopiert.
  • Wenn jetzt ihr Datum in jeder Zeile um einen Tag vorangeschritten ist, dann haben sie das Datum vermutlich ganz normal als kalendarischen Datum und nicht als Formel bzw. Text eingegeben. In dem Fall könnten sie in D2 noch einmal das Datum mit voran gestelltem Auslassungszeichen schreiben: '04.06.2017 und dann wiederum per Doppelklick nach unten kopieren.
  • Legen Sie jetzt eine neue Abfrage auf der Basis dieser Daten an. Dazu wählen Sie den Punkt Aus Tabelle bzw. Von Tabelle.
  • Korrigieren Sie den Datentyp in der Spalte Zeit und der Spalte Datum auf jeweils genau diesen Typ. Bei der Gelegenheit wird dann auch das Datum zum echten kalendarischen Datum.

Wenn Sie allerdings diese Form des Imports öfter einmal durchführen wollen, dann bietet sich eine andere Vorgehensweise an.

Es bleibt ihnen vielleicht auch noch eine Entscheidung in Sachen festes oder aktuelles Datum. In jedem Fall werden Sie das Datum in Power Query als neue Spalte einfügen, darum müssen Sie sich für kurze Zeit aus dem nur-Klick-Modus verabschieden und eine kleine Formel schreiben.

Wechseln Sie zum Menü-Register Spalte hinzufügen und klicken Sie hier auf Benutzerdefinierte Spalte. Als Neuer Spaltenname geben Sie statt Benutzerdefiniert den Begriff Datum ein. Ich klicke dazu in das Feld, StrgA (um alles zu markieren) und schreibe dann einfach los. Im Feld Benutzerdefinierte Spaltenformel: gebe ich nach dem = diese Formel in exakt dieser Schreibweise ein:
DateTime.LocalNow()

Benutzerdefinierte Spalte mit einer Formel der Power Query-Sprache M

Benutzerdefinierte Spalte mit einer Formel der Power Query-Sprache M

Anschließend ändern Sie den Datentyp auf Datum und dann sieht das ganze schon sehr gut aus. – Alternativ können Sie auch gleich die Formel
DateTime.Date(DateTime.LocalNow())
verwenden, dann haben Sie gleich (nur) das Datum in der Spalte. Hiermit wird also stets das aktuelle Datum in die Spalte eingetragen. Möchten Sie ein definiertes, also beliebiges Datum in die Zelle eintragen, dann verwenden Sie folgende Formel wenn die Spalte ein echtes Datum (hier im Beispiel den 1. Juni 2017) enthalten soll:
Date.FromText("01.06.2017")
Wenn es nur reiner Text sein soll, schreiben Sie ganz einfach nur "01.06.2017" mit den Anführungsstrichen nach dem = in das Feld. Hinweis: Alle Funktions-Namen sind in Power Query case-sensitiv! Also müssen sie die vorgegebene Groß- Kleinschreibung unbedingt beachten.

Bleibt nur noch ein Punkt zu erledigen: Die Spalten müssen anders angeordnet werden, damit sie der Vorgabe bzw. dem Wunsch des Fragestellers in dem Forum entsprechen. Der einfachste Weg: Klicken Sie jeweils in die Überschrift der zu verschiedenen Spalte und ziehen Sie diese dorthin, wo die korrekte/gewünschte Position ist. Alternativ bietet sich auch ein Rechtsklick in die Überschrift und im Kontextmenü dann der Punkt Verschieben an.

Ein letztes Mal Schließen & laden, die Arbeit ist getan. Zumindest für diese PlayList.

▲ nach oben …

Verschiedenes

Hinweis: Und dieser ganze Aufwand für ein einziges Mal? Nun ja, wenn Sie das per Hand machen sollten wäre das gewiss erheblich mehr Arbeit. Eine funktionierende Lösung in VBA zu schreiben ist auch nicht unbedingt in 5 Minuten erledigt. Außerdem kann ich kann mir gut vorstellen, dass solch eine Song-Liste nicht nur einmal erstellt wird sondern eventuell über einen gewissen Zeitraum täglich. Dann müssen Sie nur den Import (siehe Kapitel Vorarbeit) jedes Mal durchführen und die Daten unter den gleichen Namen an gleicher Stelle speichern. Sie überschreiben die existierenden Files. Danach wechseln Sie zur Abfrage Songs & Interpreten, klicken auf die Schaltfläche Aktualisieren und sofort ist der aktuelle Stand in dieser Aufstellung vorhanden.

Bedenken Sie aber folgendes: Wenn Sie über diesen Zeitraum eine Chronologie erstellen wollen, werden Sie vor jeder Aktualisierung die gesamte durch Power Query erstellte Excel-Tabelle in ein neues Arbeitsblatt kopieren und an eventuell vorhandene Daten anfügen. Dann ist eine Auswertung beispielsweise mit PivotTable nicht nur möglich, sondern auch wirklich problemlos möglich.

Hinweis: Wenn Sie mehr Erfahrung mit Power Query haben, werden Sie dieses oder jenes „schlanker“ gestalten. Diese Anleitung ist aber für Einsteiger in Sachen Power Query gedacht und da halte ich diese Step-by-step Vorgehensweise für ausgesprochen sinnvoll, damit jeder der einzelnen Schritte nachvollzogen, kontrolliert und verstanden werden kann. – Ich bin bei der Erarbeitung dieses Beitrages auch während des Einlesen der Daten aus dem Netz zuerst mehrere andereWege gegangen, teils anspruchsvoller (die expandierte Tabelle über den Umweg „Entwicklertools“ zu markieren und dann zu kopieren), teils einfacher durch direktes kopieren aus der Website. Ich empfinde dieses hier als den besten Weg für alle Stufen auf der Excel-Wissensleiter. – Vielleicht kennt ja der eine oder andere Leser dieses Beitrags eine Möglichkeit, die expandierte komplette Tabelle direkt einzulesen, also ohne den Umweg des speicherns auf der Festpaltte. Vielleicht hat diese (expandierte) Ansicht ja noch einen Link, den ich im Browser und damit auch per Power Query öffnen kann. Und ja, ich kenne auch die Datenquelleneinstellungen … 😉 

▲ nach oben …

Dieser Beitrag wurde unter Daten zusammenführen, Datentyp anpassen, Datum & Zeit, Filtern & Sortieren, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M), Spalten bearbeiten, Web-Abfragen abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.