My Google Map Blog

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.

The post From Excel to KML via XML export appeared first on Google Earth Blog.