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.
How long does the process take?
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.
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”
sorry timothy – my website doesn’t have a hyphen http://offroadinghome.com/
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)
Nope – it didn’t, let’s try with backslashes: i.e. \ with its \ / \ pairs.
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”
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.
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.
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.
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.
It works. I am very excited. thank you so much
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.
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.
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.
I am considering doing a another post on import from Excel to Google Earth so check the blog over the next few days.