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

15 Kommentare

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?

Artikel bewerten

Bewertung fĂŒr: Wikipedia-Tabellen in Google Spreadsheet importieren und in Google-maps darstellen:
1 Stern2 Sterne3 Sterne4 Sterne5 Sterne
5,00 von 5 Sternen bei 2 abgegebenen Stimmen.
Loading ... Loading ...
  1. Hi,

    herzlichen Dank fĂŒr Dein Tutorial – allerdings flutscht es bei mir leider nicht. Übrigens schreibst Du zwischendurch ‘altitude’ statt ‘latitude’.

    Gruß
    Thorsten

  2. plerzelwupp

    vielen Dank fĂŒr den Hinweis. Ich werde das gleich korrigieren.
    Wo genau hĂ€ngt’s denn bei Dir? Vielleicht kann ich Dir helfen(?)

  3. Moin,

    inzwischen klappt es doch bei mir. Ich hatte beim URL-Kopien mir keinen CSV- sondern einen HTML-Link rausschmeißen lassen.

    Gruß
    Thorsten

  4. Wenn es ansteht, werde ich bestimmt auf diesen Artikel zurĂŒck kommen… :-)
    Im Gegensatz zu vielen anderen stöbere ich auch gerne mal in Ă€lteren Artikeln rum. Zumal wenn sie Tipps fĂŒr WP enthalten.
    Ich meine, einiges weiß ich selber und wenn ich mich damit beschĂ€ftige, wĂŒrde ich bestimmt auch einiges herausfinden. Aber warum soll man das Rad zweimal erfinden. Wenn schon jemand das ausfĂŒhrlich erklĂ€rt hat, kann man da ja durchaus “ablinsen” und kommt so schneller zu einem Erfolgserlebnis… :mrgreen:

  5. Donnerwetter, da hast du dir aber wirklich MĂŒhe gegeben. Viel Text, reichlich vorhandene Bebilderung und die Recherche hat -wie du selbst sagtest- ja auch durchaus ein “wenig” Zeit in Anspruch genommen.

    Und es ist durchaus im Bereich des Möglichen, dass ich frĂŒher oder spĂ€ter die Tipps aus diesem Tutorial brauchen kann, da ich hĂ€ufiger Dinge im Internet recherchieren und dann in entsprechenden Anwendungen weiterverarbeiten muss.

  6. Tolle Anleitung – nach so etwas habe ich schon lange gesucht. Spart mir jede Menge Arbeit und wird gleich ausprobiert – Vielen Dank!

    Frage: Wird von obiger Formel (Ermittlung des LĂ€ngen- und Breitengrades) auch eine komplette Anschrift in der Form: als Feldinhalt akzeptiert ?

    • plerzelwupp

      Vielen Dank :-)

      Leider wurde Dein code vom Backend verschluckt(?), aber prinzipiell kannst du jeden Feldinhalt auslesen, automatisiert in der pipe modifizieren und entsprechend ausgeben.

      • Ich habe da noch eine Frage, die mir auf den NĂ€geln brennt und zu der ich bisher keine Lösung bzw. Informationen gefunden habe.
        “title” und “description” scheinen von Google Maps als Variablen fĂŒr das Infofenster erkannt und interpretiert zu werden – wie stelle ich es an einen mehrzeiligen (formatierten) Text im Infofenster anzuzeigen ?; ich denke da an z.B. Name und Adresse (Straße, PLZ, Ort, Telefon etc.) die ich ebenfalls aus meiner Tabelle entnehme.

      • plerzelwupp

        Hallo Frank,

        vielen Dank fĂŒr Deinen Kommentar.

        Nun, es ist schon eine Weile her, aber wenn ich mich richtig erinnere, hab ich das damals ĂŒber das Feld “description” gemacht.

        Du kannst natĂŒrlich einen ganzen Schwung weitere Informationen hinzufĂŒgen und das Ganze erheblich verschönern. Hierzu gibt es auch eine Anleitung bei Google. Desweiteren gibt es hier ein Dokumentation ĂŒber die Bearbeitung der kml-Dateien fĂŒr Google-Maps.

        Ich hoffe, Du kannst mit den Informationen etwas anfangen(?)

      • Ich möchte mich noch fĂŒr deine Antwort bedanken – besser spĂ€t als nie ;-) Manchmal sieht man den Wald vor lauter BĂ€umen nicht mehr – ich war bisher nicht auf die Idee gekommen die “description” mittels HTML-Tags zu formatieren. Das klappt jetzt wunderbar.Die zĂŒndende Idee kam mir ĂŒbrigens hier

  7. Durch Links können in der Tabelle weiterfĂŒhrende oder vertiefende Artikel ĂŒbersichtlich mit einzelnen Begriffen verknĂŒpft werden. Beispiel: in der Tabelle Deutscher Kaiser verlinkt jede Zeile zum entsprechenden Artikel ĂŒber die einzelnen Kaiser, und die Links der Jahreszahlen zeigen wichtige Ereignisse zu jener Zeit.

  8. wow! ich habe schon soooo lange nach was Ă€hnlichem gesucht…
    vielen Dank fĂŒr die gute ErklĂ€rung

  9. HAllo Oliver,
    vielen Dan fĂŒr die sehr gute Beschreibung. Ich arbeite zum erstenmal mit diesem Tool.
    Habe 2 Fragen:
    Ich versuche in der Pipe eine 2te Beschreibung hinzu zu fĂŒgen, so dass mir bei der Karte zu jedem Ort 2 Beschreibungen angezeigt werden, geht das?

    Außerdem wĂŒrde ich gerne wissen, wie ich bei google maps das Design Ă€ndern kann. Ist es möglich die Art von http://www.flightradar24.com/ einzurichten, dass bei Klick auf den button mehrere infos links in einer Tabelel angezeigt werden.

    Vielen DAnk

  10. Hallo Ralf,

    ja, es ist natĂŒrlich prinzipiell möglich, mehrere Informationen zum Ort hinzu zu fĂŒgen.
    Was die Darstellung betrifft, so ĂŒbersteigt es die Lösungsmöglichkeit, die ich hier beschrieben habe. Vielleicht schaust du dir mal folgende Anleitung von Google an: http://earth.google.com/intl/de/outreach/tutorial_spreadsheet.html

    Viele GrĂŒĂŸe
    Oliver

  11. Puh, langer aber sehr aufschlussreicher Artikel. Daumen hoch! :-)

Trackbacks/Pingbacks

  1. Aktion “Never forget” - Meine besten Blogartikel - Die Leserschaft, AnfĂ€nger-Artikel, Lustig, Artikel, Sascha, Zeit - Plerzelwupps Erfahrungen - [...] der  Artikel: "Wikipedia-Tabellen in Google Spreadsheet importieren und in Google-maps darstellen". Bevor ich zu schreiben anfing, gingen Tage des …
  2. Hol Dir Google Web Elements auf Dein (Wordpress-)Blog - Klicken zum VergrĂ¶ĂŸern, functionsphp, Wert, Google, Euch, Blog - Plerzelwupps Erfahrungen - [...] als Beispiel eine "Excel-Arbeitsmappe", die für meinen lesenswerten Artikel "Wikipedia-Tabellen in Google Spreadsheet importieren und in Google-maps darstellen" [...]
  3. Umfrage erstellen ohne Plugin - Umfrage, einbinden, ohne, plugin, wp-polls, Google, form - Plerzelwupps Erfahrungen - [...] mit Google-Spreadsheets ausprobieren möchte, dem sei auch mein frĂŒherer Artikel empfohlen: Wikipedia-Tabellen in Google Spreadsheet importieren und in Google-maps …
  4. Google Maps – Daten aus Tabellen darstellen - Webmeister Blog - [...] aus Yahoo Pipes Noch einen Schritt weiter geht Plerzelwupp in seiner Lösung in der die Daten von Städten mittels …

Einen Kommentar schreiben