From Excel to KML via XML export

There are a number of different ways to get data from Microsoft Excel into Google Earth. There are various web-based tools to do the job, such as the one we mentioned in this post. Another way is to do the conversion entirely in Excel as demonstrated by GEB reader “Will from the UK” in this post.

Today we are looking at the technique we used for this post where we wanted to export a set of polygons that we had previously imported into Excel from a KML. Importing from KML is as easy as renaming the file from .kml to .xml and then using Excel’s built in import features.

We used Excel 2013, but the process should be similar in most recent versions of Excel. The first step is to enable the ‘Developer’ tab as described here. Next, make sure your data is arranged in the columns ‘name’, ‘description’, ‘styleUrl’ and ‘coordinates’. Open the ‘XML Source’ task pane by clicking on the ‘Source’ button found on the ‘Developer’ toolbar. Next, click the ‘XML Maps’ button. If you had previously imported data from KML, you may already have an XML Map listed. If so, delete it. Now download this file and add it as an XML Map. Excel will use it to create a schema. Close any dialog boxes and you should now have something like this:

Drag and drop the column names from the ‘XML Source’ pane to the appropriate column headers. It should now look something like this:

Click the ‘Export’ button found on the ‘Developer’ toolbar. Choose a file name and export the data. Edit the resulting file using any text editor. Replace all the text above the first <Placemark> tag with this:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">
<Folder>

Then scroll to the bottom and replace the </data-set> tag with this:

</Folder>
</kml>

Save the file and rename it from .xml to .kml. You should now be able to open it in Google Earth. Finally, you may wish to add in some style information to your KML, which you can either do by editing the KML file, or from within Google Earth.

The above procedure was specifically designed for polygons. For other types of KML objects, modify the XML Map file to match the types of objects you wish to export.

Although Excel readily imports KMLs with Placemarks arranged into folders, we were not able to get it to export folders. All attempts to do so resulted in an error stating that the XML map contained ‘denormalized data’.

We worked out how to do the above export with help from 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.






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. Surely it is the best trick. I have been looking such kind of idea for a long time. So thank you.

  2. Any idea why a Google Earth Pro KML export file, renamed to .XML, fails to import into Excel? I simply exported MyPlaces to KML, renamed to .XML and used the Excel Ribbon function: Data:Import from Other Source (XML Data Import) and pointed to the file.

    The error in excel is: Invalid File reference. The path to the file is invalid, or one or more of the referenced schemas could not be found.

    • Timothy Whitehead says:

      Which version of Excel are you using? I used Excel 2013 and it works correctly. It says Excel cannot find a schema but it will create one. Make sure you renamed a .KML and not a .KMZ

  3. Thank you very much, I was looking for this kind of answer!!! I did create a few cell sectors through this way, but I have to create a file bigger than this one, do you know if there is a limit with the file size or the lines in the file exported?



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.