blankblank blank




Convert An Excel Address Spreadsheet Into A KML File (And Then Into A Geocoded CSV Text File) With KMLGeocode And KMLReport



KMLGeocode (available here under the Google Geocoder listing) takes an address file in Excel (or XML) format, and creates a Google Earth KML file that plots geocodable addresses in the correct position. Load an address XLS file into the program (like this sample data also available on the download page):

KMLGeocode

You have to specify the column names that contain the key address fields required for geocoding, as well as the output KML filename. Once done, create the KML output file and open it in Google Earth. Addresses that could be geocoded will be plotted with orange pushpins:

geocoderesults

Clicking on  pushpin will bring up all the data fields for a record in the spreadsheet, not just the specified ones. Addresses that couldn’t be geocoded are given yellow pushpin designations, and will either show up plotted in a general location, or not plotted at all in Google Earth (though they will be listed in the Place pane under the KML file listing:

geocodeplaces

To embed the actual geographic coordinates for each of these addresses, right-click on the KML file listing (people.kml in the above example), and save it as another KML file with a different name. You can now open this different KML file in the KML Geocode Report program:

geocodereport

Specify the name of a text output file; this output will be a spreadsheet-readable CSV file that contains the original spreadsheet data with the longitude and latitude of geocodable addresses appended at the end as X and Y coordinates:


ID,Name,Group,Street,Street2,City,State,Zip,Phone,Email,X,Y
 11, Suzanne White, Office of Information Resources, 312 8th Ave North, Suite 1600, Nashville, TN, 37243, 615-253-4799, suzanne.white@state.tn.us,-86.784031,36.164133
 12, Kurt Snider, US Fish and Wildlife Service, 446 Neal St, , Cookeville, TN, 38501, 931-528-6481, kurt_snider@fws.gov,-85.497903,36.138056
 14, David Tirpak, Comptroller of the Treasury, 505 Deaderick St, Suite 1700, Nashville, TN, 37243-0277, 615-401-7820, david.tirpak@state.tn.us,-86.781603,36.165238

The “UnMatched” file is supposed to contain the non-geocodable addresses in KML format (at least, according to the PDF manual for these programs), but it didn’t show up for the sample data when I tried it. When you “Process” the files, in addition to the output file above, you’ll get a pop-up telling you the overall geocoding success percentage:

matchpercent

Note: When installing both programs, the installation directory is listed simply as “C:\Program Files\”; however, unless you specify the new name, it is installed in the directory “C:\Program Files\BRalston”, and “BRalston” is the folder in the Start Menu where you’ll find the program shortcut icons.


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




7 Responses to “Convert An Excel Address Spreadsheet Into A KML File (And Then Into A Geocoded CSV Text File) With KMLGeocode And KMLReport”


  1. 1 Austin

    Thanks to Dr. Ralston at the University of Tennessee Dept of Geography for these tools!… they came in extremely handy when I needed geocoded addresses for a point pattern ananlysis which is part of my thesis project.

  2. 2 Leszek Pawlowicz

    There are a bunch of other free geocoding tools available as well; check the geocoding category on this blog for more info.

  3. 3 land surveyor

    Here is a new online map for ya made with batchgeo… http://landsurveyorsunited.com/page/new-lsu-member-map
    this map has almost 1000 land surveyors all over the globe based on level of experience
    user contributions on LSU are up 93% I am hoping that the software you provided above (thank you) will render a map that lays out a little faster…

  4. 4 Ryan

    WOW this application is just what I was looking for! I’m sending a thank you note to the author right now..

  5. 5 Oto

    Doesn’t work on W7 even with sample data :(

    See the end of this message for details on invoking
    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************
    System.InvalidOperationException: The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine.
    at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
    at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
    at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.OleDb.OleDbConnection.Open()
    at xml2kml.Form1.ProcessXLSFile(String& thefile)
    at xml2kml.Form1.btnInput_Click(Object sender, EventArgs e)
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

  6. 6 Leszek Pawlowicz

    Works on my Windows 7 system without issue; you might try contacting the program author.

  7. 7 Oto

    OK, I fixed it. It didn’t work because I have x64 CPU and JET is absolute and works only for 32-bit so xls files can’t be imported.

    To fix: Use coreflags utility to set the program to work in 32-bit compatiblity.
    Download:http://www.apexsql.com/zips/CorFlags.zip

    run: corflags kmlgeocode.exe /32bit+

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