Wikipedia-Tabellen in Google Spreadsheet importieren und in Google-maps darstellen

Wollen Sie mal wissen, wie Sie Daten aus dem Internet an eine Tabellenkalkulation übergeben können und diese dann weiter verarbeiten und in Google-maps importieren können? Dieser Aufsatz stellt zunächst dar, wie Sie Wikipedia-Tabellen in Google Spreadsheet (zu deutsch: google Tabellen) importieren . Im zweiten Teil erfahren Sie dann, wie Sie die diese Daten weiterverarbeiten diese dann im 3. Teil nach Google Maps übergeben. Keine Angst: Es ist ganz einfach. Alles was Sie benötigen ist ein Google Mail Account – und den gibt’s ja bekanntermaßen kostenlos. Für den dritten Teil (Darstellung der Werte in Google-maps) benötigen Sie zusätzlich noch einen Yahoo-Account – ebenfalls kostenlos.

Die ganze Vorgehensweise beschreibe ich am Beispiel der Wikipedia-Tabelle “Liste der größten Städte der Europäischen Union“- absteigend sortiert nach Anzahl der registrierten Einwohner. Diese importieren wir zunächst in unser Online-Tabellenkalkulationsprogramm “Google Tabellen”. Ich möchte an dieser Stelle nicht unerwähnt lassen, dass Sie jede x-beliebige Tabelle mit geografischen Informationen aus dem Internet importieren können und diese letztendlich in einer Landkarte darstellen können. Es muss nicht unbedingt Wikipedia sein.

1. Teil: Wikipedia-Daten in Google Tabellen importieren

Wir legen los, melden uns zunächst bei unserem Google-Account an und wechseln nach Google Text und Tabellen (Google Spreadsheet). Dort erstellen wir eine neue Tabelle und nennen diese “Europa”. In einem weiteren Fenster (oder Tab) öffnen wir den Wikipedia-Quellartikel Liste der größten Städte Europas, den wir importieren möchten. Da wir die Web-Adresse des Originalartikels gleich für unsere Tabelle benötigen, kopieren wir diese gleich in den Zwischenspeicher (Adresse in der Adressleiste markieren und mit der rechten Maustaste kopieren).

In Zelle A1 unseres Spreadsheets geben wir eine Formel mit folgendem Syntax ein:

=importhtml(„Quelladresse„;“table„;n)

importhtml: Spreadsheet-Befehl zum Importieren von html-Daten
Quelladresse: Adresse des Quellartikels
table: Gibt an, dass wir ein Tabelle importieren möchten
n: Gibt an, welche Tabelle importiert werden soll (z.B: „1“ für die erste Tabelle, „2“ für die zweite Tabelle, usw.)

In unserem Beispiel wäre das die Tabelle 2, da das Inhaltsverzeichnis des Wikipedia-Artikels als erste Tabelle angesehen wird. In unserem Beispiel lautet der Befehl also: =ImportHtml(„http://de.wikipedia.org/wiki/Liste_der_gr%C3%B6%C3%9Ften_St%C3%A4dte_der_EU“;“table“;2)

Hinweis: die Formel sollte eingetippt werden – wobei Sie die Wikipedia-Adresse einfügen können. Wenn die komplette Formel hineinkopiert wird kommt es zu einer Fehlermeldung.

Sobald SIe =ImportHtml in die Zelle eintragen, wird schon der Syntax angezeigt. Wobei dieser entsprechend dem obigen Beispiel angepasst werden muss. Die Umlaute der eingefügten Adresse werden übrigens automatisch umgewandelt – also nicht wundern, wenn es etwas „komisch“ aussieht.

Das war’s auch schon: Die Wikipedia-Tabelle wurde in die Tabellenkalkulation importiert.

2. Teil: Tabelle editieren

Etwas unsauber sieht die Spalte C (Einwohner) aus. Das liegt daran, weil in der Wikipedia-Tabelle zwei Zahlenformate hinterlegt sind. Darum kümmern wir uns gleich.

Doch zunächst möchten wir die Zellen editierbar machen, denn in den Zellen stehen bislang nur die Import-Verweise – noch keine absoluten Werte. Wir markieren also die komplette Tabelle, klicken mit der rechten Maustaste auf den markierten Bereich und wählen „kopieren“. mit einem Klick in die Symbolleiste: „Bearbeiten/ Nur Werte einfügen“ überschreiben wir das ganze nun mit absoluten (nicht mehr mit Wikipedia verknüpften) Werten.

Da die Städtenamen noch alle mit einem Sternchen* versehen sind wählen wir „Bearbeiten / suchen und ersetzen“ und löschen alle Sternchen* mit einem Schlag.

Nun um Spalte C (Anzahl der Einwohner) lesbarer zu machen, fügen wir eine weiter Spalte rechts daneben ein. In dieser Spalte geben wir in die oberste Zelle die Formel „=left(C2;15)/1“ (ohne Anführungszeichen) ein, kopieren die Zelle und fügen sie bis zum Ende der Tabelle ein. Wir markieren Spalte D, kopieren diese und fügen wieder die Werte ein (wie oben beschrieben). Anschließend lässt sich Spalcte C löschen. Die neue Spalte nennen wir wieder „Einwohner“.

Das wars auch schon. Zuletzt können wir noch die “uninteressanten” Spalten F+H (stand und Quelle) löschen. Die Spalte “Staat” können wir noch vor die Stadt kopieren. Ich selbst habe durch “suchen und ersetzen” noch “Vereinigtes Königreich” durch “England” ersetzt.

Mit etwas Übung gelingen die ganzen Schritte sehr schnell – ich hab etwa 5 Min benötigt. Excel-Profis tun sich sicherlich auch etwas leichter. Meine Tabelle sieht nun so aus.

Und nun kommt das geilste (sorry für den Ausdruck – aber es ist so). DIe Koordinaten der Städte lassen wir nun automatisch in unsere Tabelle eintragen. Mit Hilfe dieser Koordinaten können wir alle Städte mit einem Schlag in Google Maps oder Google Earth mitsamt der Werte in der Landkarte anzeigen lassen. Wir erstellen eine sog. “KML-Datei”.

3. Teil: Daten an Google-Maps oder Google-Earth übergeben.

Es gibt eine geniale Formel für Google-Spreadsheets, mit der sie Google automatisch die Längen- und Breitengrad der Städte einfügen lassen. Hierfür fügen wir zunächste vor dem Namen der Stadt eine neue Spalte C ein. Dort wird im Spaltenkopf “Hilfsspalte2″ eingetragen und in den darunterliegenden Zelle folgendes geschrieben und herunterkopiert:

http://maps.google.com/maps/geo?output=csv&q=

Nach der Stadt fügen wir nun 4 Spalten ein. Die bennen wir dann nacheinander “Hilfsspalte 2″, Hilfsspalte 3″, “latitude” und “longitude”.
Es ist zu beachten, dass unter “Datei, Tabelleneinstellungen” das Gebietsschema “USA” ausgewählt ist. Außerdem ist zu beachten, dass Google nur eine begrenzte Zahl an Daten gleichzeitig importieren kann. In diesem Fall machen wir das in zwei Schritten: Wir geben die o.g. Formel bis zur Hälfte der Tabelle ein, kopieren wieder die Daten und fügen Sie als Werte (wie oben beschrieben) ein. Anschließend machen wir das Gleiche nochmals mit dem Rest der Tabelle.

In der obersten Zelle der Hilfsspalte 2 geben wir nun folgende Formel ein und kopieren auch diese bis an’s Tabellenende:

=ImportData(CONCATENATE(C2,D2))

Nach Drücken der Eingabetaste wird nun als Ergebnis der Wert “200″ angezeigt. Ebenso werden Werte in Hilfspalte 3 eingetragen – was die bedeuten weiss ich auch nicht so genau. Entscheidend aber ist, dass in den beiden Folgespalte der Längen- und Breitengrad angezeigt werden.
Wer will, kann jetzt nochmals die ganze Tabelle kopieren, als Wert einfügen und überflüssige Spalten (z.B. die Hilfsspalten) löschen. Ich verzichte darauf und meine Tabelle sieht jetzt so aus.

Damit die Daten auch übergeben werden können wählen wir

1. „Freigeben“ , „als Webseite veröffentlichen“,

2. Wir veröffentlichen unser sheet und wählen den Link „weitere Veröffentlichkeitsoptionen“. Im nächsten Fenster geben wir folgendes ein:

Wir drücken dann auf die Schaltfläche „URL generieren und kopieren uns die Adresse.

Mit der CSV-Adresse unseres Tabellenblatts im Zwischenspeicher wechseln wir zur Seite pipes.yahho.com

Dort loggen wir uns ein (falls Sie noch nicht über ein yahoo-Account verfügen, müssen Sie natürlich vorher einen anlegen – kostet ja nix). Mit der Schaltfläche „create a pipe“ legen wir ein pipe mit den folgenden Einstellungen an:

Meine pipe ist freigegeben und kann auch dort betrachtet werden.

Unter dem Tab „View Sources“ können Sie Ihre Pipe editieren (sie müssen dafür angemeldet sein). Die einzelnen Elemente ziehen Sie per drag and drop in den Arbeitsbereich: „Fetch CSV“ steht ganz am Anfang. Dieses Element finden Sie unter „Sources“. Es ist dafür zuständig, dass Ihre veröffentlichte CSV-Datei importiert wird. Die Adresse tragen SIe (wie im Beispiel) ein. Unter „Use the following column names“ geben wir den Spalten dann nochmals neue Namen – dabei ist es wichtig, dass auch „longitude und „latitude“ eintragen. denn das kann von der pipe interpretiert werden (es klappt auch einigermaßen ohne die Längen und Breitengrade – diesen Schritt hätten wir also auch in unserem Spreadsheet überspringen können – jedoch erkennt Google die Städte besser als die yahoo-pipe. Probieren Sie es selbst einmal aus). Die nächsten Elemente „Rename“, „Regex“ und „Location Extractor“ finden Sie unter „Operators“). Auch diese ziehen Sie in Ihr Dashboard. „Rename“ ist dafür zuständig, dass die Namen der Städte als Titel und die Einwohner als Beschreibung in die KML-Datei eingetragen werden. „Regex“ verfeinert das Ganze, indem vor der nackten Einwohnerzahl noch das Wort „Einwohner“ steht, wobei „$1“ die Variable „Anzahl der Einwohner“ ist. Das nächste element „Location Extractor“ findet die Städte in der Landkarte (die funktioniert wohlgemerkt besser, wenn er auch mit longitude und latitude gefüttert wird). Sie verbinden noch die einzelnen Elemente (mit der Maus) bis zum letzten Element, dem „Output“. Im Debugger sehen Sie übrigens immer die Live-Ergebnisse.

Ich könnte über diese pipe noch Romane Schreiben – verzichte hier allerdings auf eine Vertiefung: Das müssen Sie einfach selbst mal probieren – Sie werden schon dahinterkommen. Nachdem Sie Ihre pipe gespeichert haben, können Sie diese mit „run pipe“ starten.

Zuguterletzt exportieren wir noch eine *.kml-Datei. Die kann dann bei Google-maps oder Google-earth importiert werden (siehe weiter unten). Die entsprechende Option finden wir unter „more options / get as kml“

Nach dem Speichern der KML-Datei öffnen wir google maps. Dort erstellen wir eine neue Karte und importieren die eben erstellte kml-Datei. Wahlweise können wir diese auch in Google-Earth importieren. Meine kml-Datei befindet sich hier (Rechtsklick: „Speichern unter“) und das Ergebnis sieht so aus:

Größere Kartenansicht

Wir haben unser Ziel erreicht 🙂 Das Ganze kann nun natürlich noch entsprechend verschönert und ausgeschmückt werden. Hier sind Ihren Fantasien keine Grenzen gesetzt.

Die Idee für diesen Artikel hab ich mir auf der Seite „ouselfull.info – the Blog“ geholt.

So …. und nun wünsch ich viel Spaß beim Nachahmen.

Bei Problemen bin ich (wie immer) sehr gerne bereit weiterzuhelfen. Ich warte auf Eure Kommentare 😉

Nachtrag vom 23.01.09: Mittlerweile wird dieser Artikel sehr häufig angeklickt –  Bin schon dabei ein weiterführendes Tutorial zu schreiben ….. Interesse?