• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Google Earth Blog

The amazing things about Google Earth

  • Home
  • About
  • Basics
  • Links
  • Tips
  • 3D Models
  • Sightseeing
  • Videos

Google Earth placemarks to Excel

January 19, 2017

It is often useful to be able to get Google Earth placemarks into Excel (or other spreadsheet application or even a database). One way to do this is to save your placemarks as a .KML file (do not use .KMZ) then rename the file to .XML. Then it will open in Excel. However, it will typically need quite a lot of cleaning up after that. So, we thought it might be useful to have a simple converter that takes a .KML or .KMZ and extracts all the placemarks (points only, ignoring polygons, paths and other features) and converts it to csv for easy import into Excel or other application. So, here it is:

 

It only extracts the placemark name, description, latitude and longitude.

If you need other fields, or features, or encounter any bugs, please let us know in the comments.

To convert the other way (Excel to Google Earth), the easiest method is to use Google Earth Pro’s import feature described here. Another more complicated technique can be found here.

About Timothy Whitehead

Timothy has been using Google Earth since 2004 when it was still called Keyhole before it was renamed Google Earth in 2005 and has been a huge fan ever since. He is a programmer working for Red Wing Aerobatx and lives in Cape Town, South Africa.

  • More Posts(754)

Filed Under: Site News Tagged With: KML javascript utilities

Reader Interactions






PLEASE NOTE: Google Earth Blog is no longer writing regular posts. As a result, we are not accepting new comments or questions about Google Earth. If you have a question, use the official Google Earth and Maps Forums or the Google Earth Community Forums.

Comments

  1. Stewart Young says

    January 19, 2017 at 11:39 am

    How long does the process take?

    • Timothy Whitehead says

      January 20, 2017 at 3:57 am

      It should take a few seconds. It should also say ‘Done’ once it is finished. If it does say ‘done’, then you may be missing the download (it depends on your browser how that is displayed). If it doesn’t get to ‘done’ then please let me know what browser you are using, and if the KML is not sensitive then send me a copy of the KML so I can check for bugs.

  2. DJ says

    January 21, 2017 at 2:01 pm

    Timothy… as you may remember, I’ve spent several years serving “offroading maps” and nearly all of them have been “handcrafted” using Google’s KML styles and functions because there has been almost no “transposing” software available. Excel, jEdit, XML notepad, GPS Visualizer and (when made free) GooglePro have been my best friends. And I’ve even dabbled with MS Access to store the various placemark data- although with little success. I’ve tried to write an XML “extractor” but of course I would want to bring ALL available data if it was presented including those in the and other methods – iterating through them and creating columns

    I’m glad to see you working on this – a lot of my files fly through your converter with flying colors. Some do not, and “hang” – although I never let them go longer than 60 seconds. It seems like the shorter ones make it and the “longer” ones do not but I can’t be sure. Responding to your plea for troubleshooting info I offer the two files below.

    I haven’t actually checked the “converted” files created thoroughly although it looks like when you bring over the the parser strips out the ” <![CDATA[ " code and adds a bunch of double quotes. I've got to look at that further to see what that means to being able to get them back into KML once they've been manipulated.

    Also, it would be more convenient if somehow a bit of the file structure could be captured (i.e. ) but I haven’t thought about how that could be reflected in CSV so that it would appear in Excel (unless a set up a value in a new column with its as the value which repeated for all lines until a new column was set and changed the name for the value.

    https://dl.dropboxusercontent.com/u/567495/Temp/Jawbone-Butterbredt%20ACEC.kml – hangs with “parsing”

    https://dl.dropboxusercontent.com/u/567495/Temp/Utah%20USGS%20Features.kmz – hangs with “processing”

    • DJ says

      January 21, 2017 at 2:03 pm

      sorry timothy – my website doesn’t have a hyphen http://offroadinghome.com/

    • dj says

      January 22, 2017 at 2:50 pm

      I notice that the parser has stripped out my references to KML tags so doesn’t make sense in parts. ( i.e. with its / pairs and — (let’s see if adding spaces helps)

      • dj says

        January 22, 2017 at 2:54 pm

        Nope – it didn’t, let’s try with backslashes: i.e. \ with its \ / \ pairs.

      • dj says

        January 22, 2017 at 3:02 pm

        NOPE trying with (amp)lt; and (amp)gt; only gave me an invalid security token.

        I’ll try substituting the angled brackets with parens:

        “i.e. (ExtendedData) with its (name) / (value) pairs”

    • dj says

      January 22, 2017 at 3:10 pm

      Timothy – another area the parser made difficult was my statement that “when the converter brings over the (description) it parses out the ” ” tag needed to be able to insert table’s and other HTML stuff.

      • dj says

        January 22, 2017 at 3:14 pm

        sorry, this is just bizzare – the parser left the ” <![CDATA[ " in above but took it out in my comment I just made. < ! [ C D A T A [ – is needed to get Earth to parse HTML code in the description tags correctly and the converter seems to remove it from them.

    • Timothy Whitehead says

      January 23, 2017 at 1:35 pm

      Hi DJ, I will go through your sample files and see if I can get them to work. My first question is whether or not they are generated by Google Earth, or you generate them yourself. I would like to know whether the tag found in the first sample is undocumented KML or an error.

  3. Adam Simmons says

    January 23, 2017 at 1:22 pm

    As a huge user of KML over the years my experience and users I represent has been converting KML back into a readable format such as CSV. As you stated it can indeed be messy. Uploading a KML to Google Drive’s Fusion Tables has been a good way to do something basic but it becomes an art to find a converter that also can handle the flexibility of HTML code one might encounter in the description field and break those apart into separate columns in a CSV. The tool that does this in a generic way and offers the most adaptability to the possible KML varities for what people put in the description could fulfill a good market gap as Google Sunsets Google Earth over the next couple years as the community attempts to restandardize KML into something else. Caveat: I’m still hoping on a gambler’s odds that Google rethinks their stance on Geo products and reinvests but SIGH.

  4. Anusha Sharma Pandey says

    February 23, 2017 at 6:47 pm

    It works. I am very excited. thank you so much

  5. Mok says

    March 9, 2017 at 9:56 pm

    Is it possible to also extract the name of the folder which my placemarks resides in? I have a KML file with many placemarks, sorted by folders.

  6. David Kettle says

    June 12, 2017 at 7:13 am

    Hey Timothy,

    I’ve been playing around with exporting KML files into Excel for a project I’m working on. Really want to be able to then push back the other way – use an excel file to create a KML file that will open in Google Earth. Have had success doing this with just markers but can’t figure out how to get it to work for Markers and Polygons. Are you able to point me in the right direction regarding how to structure it so that I can achieve this?

    Regards,

    David.

    • Timothy Whitehead says

      June 13, 2017 at 3:28 am

      Have a look at this post and let me know if you get stuck. Alternatively, if you have any programming experience, it is relatively easy to write a bit of JavaScript to do the job. I have an API for the KML side of it, but you would need to do the Excel part with one of the freely available libraries.

    • Timothy Whitehead says

      June 13, 2017 at 4:20 am

      I am considering doing a another post on import from Excel to Google Earth so check the blog over the next few days.



PLEASE NOTE: Google Earth Blog is no longer writing regular posts. As a result, we are not accepting new comments or questions about Google Earth. If you have a question, use the official Google Earth and Maps Forums or the Google Earth Community Forums.

Primary Sidebar

RSS
Follow by Email
Facebook
Twitter




Categories

  • 3D Models (792)
  • Applications (708)
  • Business (288)
  • Environment (353)
  • Flying (208)
  • GE Plugin (282)
  • Google Earth News (1,764)
  • Google Earth Tips (592)
  • GPS (136)
  • Navigation (227)
  • Network Links (214)
  • Sailing (121)
  • Science (499)
  • Sightseeing (1,903)
  • Site News (587)
  • Sky (67)
  • Sports (154)
  • Street View (50)
  • Tours (117)
  • Video (421)
  • Weather (180)

Get new posts by email

Get new posts by email:

Google Earth Satellites

Copyright 2005-© 2022 Frank Taylor. All Rights Reserved.

This blog and its author are not an official source of information from Google that produces and owns Google Earth Google and Google Earth are trademarks of Google Inc.. All image screenshots from Google Earth are Copyright Google. All other trademarks appearing here are the trademarks of their respective owners.