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.
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”.