blankblank blank




Importing Spreadsheet Data Into Google Earth



One of the features offered by Google Earth Plus is the ability to import data in spreadsheet format, specifically the CSV format: Comma-Separated Values a simple text format where data values are separated by commas, and each set of data is in a different line. But Google Earth Plus has a limit of 100 points for data import. When you consider that there are free options that will convert spreadsheet data into Google Earth’s KML format without this size limitation, then it’s not all that much of a “Plus”.

There are two different kinds of data that can be imported into Google Earth:

1. Data with geographic positions already determined. The data needs to be in latitude and longitude position in the WGS84 datum, Google Earth’s native coordinate system. I’ll be talking about coordinate converters at length in an upcoming series, but if your spreadsheet position data is in a different datum, CorpsCon is capable of reading it in and converting it to NAD83 in the US, which is almost identical to WGS84 (for worldwide conversions, try GeoTrans). Don’t know which datum your data is in, or is all this talk of datums going over your head? Don’t worry too much about it – plot the data, and see if the plotted positions are reasonably close to the features in Google Earth, within GE’s limits of error. If they are, and they probably will be, then your data is most likely in WGS84. Don’t forget that in Google Earth, positions have to be entered in decimal format, e.g. 30.5 degrees, not 30 degrees 30 minutes.

2. Data with street addresses. The Google Earth Plus spreadsheet import can convert these into geographical coordinates, a process known as “geocoding”, but there are some free options for this to be covered in a follow-up post.

Starting off with importing spreadsheets that already have geographic coordinates in them:

csv2kml (freeware; Windows only)

Note: I’ve received a report that the last version of this program doesn’t work correctly; if you have this problem, I’ve uploaded an older version here.

There are at least two other programs with this name, but this is the one to have. Converts all the data in .csv text format into a KML file. The file name needs to have .csv as the suffi; to confuse things, some programs want CSV files to have a .txt suffix, and you’ll have to rename the file extension with those. The first line of the .csv file should be the names of the data fields, separated by commas, then every successive line should have a set of data in an order that corresponds to the order of data fields in the first line. Here’s a screenshot:

You specify the data fields that correspond to latitude and longitude, elevations if it has that data in meters (otherwise it pins the point to the ground, which you can also require by using the checkbox), and the name you want the point to have when displayed in Google Earth. Latitude should be positive for north, negative for south; longitude is negative for West, positive for East. Checking a data field name under “Description” will include that data in the info that pops up when you click on a point. “Linestring” creates a path, while “Individual points” creates …. individual points.

Most spreadsheet programs will allow you to open, edit and save data in .csv format. Looking for an editor designed specifically to work with files in CSV format? Try CSVed or CommaWorks.

Excel2GoogleEarth (free)

If you have a copy of Microsoft Excel, you can use this free spreadsheet macro; it uses VBA, so macros have to be enabled, and OpenOffice won’t work. The biggest advantage of this spreadsheet is that if your data is in UTM coordinates, it can automatically be converted into the required latitude and longitude in the KML file. Open up the spreadsheet, click on the button that says “Create KML File”, and a window will open up. The first order of business is to select the column order in which the data appears in the spreadsheet. Click on the columns order arrow, and you’ll get a dropdown box showing you the only possible combinations of data columns:

And that’s the biggest limitation for Excel2GoogleEarth – the only sets of data that can be converted to KML are ones that conform to these sets of data columns. Got more data than can be fit into the maximum number and types of data columns available? Tough. Note also that “Easting” comes first in all of these; for geographic coordinates, this means that the longitude column should come first, before the latitude (“Northing”) column.

To select the data you want to convert into KML, first click on one of the Sheet tabs at the bottom, and then enter or copy the data into the spreadsheet, with the data in the order specified by the Columns Order dropdown selection. Then click on the horizontal bar at the right of the Data box:

A small window will appear, with the misleading title “Create A Google Earth File?”. What you actually do is go to the sheet with your data, select the spreadsheet cells you want to convert, then click on the button at the right in that small window. As in the window above, you will see the selected data cell description now in the Data box. Other options allow you to set the default symbol for display (though you can also set this with one of the data columns), use the Altitude to plot the data relative to the ground, include Description data in the data pane for the point in Google Earth, etc..

If you’re wedded to Excel, have to convert from UTM coordinates, or you need to specify different symbol types for every point, Excel2GoogleEarth might be a good choice. Otherwise, you’re probably better off using csv2kml.

GPS Visualizer (website; donations accepted)

The GPS Visualizer website can also accept text data, either in CSV or tab-delimited format, and convert it to a KML file. Be sure to read the tutorial on how to create and format text files for waypoints; a similar tutorial for tracks is “coming soon”.

Coming soon here: converting spreadsheet data in street address form to KML, aka “geocoding”.


Looking for something else? Enter some keywords below, then click "Search".    




12 Responses to “Importing Spreadsheet Data Into Google Earth”


  1. 1 shawn

    Thanks so much for this! This will make my life a lot easier.

  2. 2 Omar Maldonado

    Could you be more specific on this?

    “To select the data you want to convert into KML, first click on one of the Sheet tabs at the bottom, and then enter or copy the data into the spreadsheet, with the data in the order specified by the Columns Order dropdown selection. Then click on the horizontal bar at the right of the Data box:”.

    I couldnĀ“t make the spreadsheet to enter the data, and I couldnĀ“t select the data to convert to kml

  3. 3 Leszek Pawlowicz

    Having checked the macro, Excel, my explanation, and the explanation on the Zonums website, everything works fine. Clicking on a sheet tab brings up an empty data sheet you can type your data into, or copy and paste it from another spreadsheet, selecting the data also works if you follow the procedure described above, and a KML file is created successfully. You need to find someone at your end to show you how to use Excel; I can’t do that here in the comments section. Sorry.

  4. 4 JackieS

    Data with street addresses/geocoding. You mention that you plan to do a follow-up post on this topic — have you written it yet?
    I’m a volunteer for a non-profit. I know how to add places one by one, have an Excel spreadsheet with addresses of all the sites, and want to add sites to Google Maps.

    Thanks.

  5. 5 Leszek Pawlowicz

    Take a look in the “geocoding” category on this blog, and you’ll find many solutions for this. And there’s a new post up today on the new version 3 of MapChannels, which makes it easy to add spreadsheet data to Google Maps and display it on your website.

  6. 6 colaboy

    Thanks for this great find, saved hours of work.

    Just a note to users of localised versions of excel: CSV really means comma separated – in my Swedish version the default separator is semicolon and csv2kml is picky.

  7. 7 Tom Waste

    Very helpful indeed, but I still don’t know how to convert street+number+city to longitude+latitude. Does anyone know how to do this?

    Thanx, Tom.

  8. 8 Leszek Pawlowicz

    Search the “geocoding” category on this blog for many different ways to do this.

  9. 9 Nav Kaur

    Hi,

    Are there any legal implications with Google for using this. I work for a non-profit client and I would like to know. It seems too good to be true so far! Thanks for the blog.

    Nav

  10. 10 Leszek Pawlowicz

    Google Earth strongly supports use by non-profits; see their outreach page for more info:

    http://earth.google.com/outreach/index.html

  11. 11 Nav Kaur

    No I meant using csv2kml to import files into Google. There are no legal implications of doing that are there?

  12. 12 Leszek Pawlowicz

    As long as the data you’re importing isn’t proprietary or commercial, there’s no problem at all – import away.

Comments are currently closed; feel free to contact me with questions/issues.