• Skip to primary navigation
  • Skip to main content
  • Skip to footer
Landviser

Landviser

Enlightening Research

  • About
    • USA
    • EUROPE
    • Partners
  • LandMapper
    • helps farmers
    • maps Golf Courses
    • helps civil engineers
    • grounds safely
    • FINDs treasure
    • locates burials
  • Equipment
    • Electrical Resistivity Instruments
    • LandMapper: RES/EC/SP Handheld
      • Previous Models
    • SibER: RES/IP Subsurface Imaging
    • AEMP-14: Multi-frequency EM Scanner
    • GEOVIZER: ergonomic EM scanner
    • RTK GPS
  • Software
    • RES2DINV and RES3DINV Geophysical Software
  • Solutions
    • Agriculture
    • Environmental Science
    • GIS Portal
    • Subsurface Mapping in 3D with Geophysics
  • Blog
  • DIY
    • Calibrate lab EC cell
    • Make four-electrode probe for soil mapping
    • EC Mapping with LandMapper and Phone GPS
    • GPX to KML to Excel | Lat-Long data transformation
    • 1D VES – Vertical Electrical Sounding
    • 2D Electrical Tomography
    • Self Potential Mapping Procedure
  • CONTACT
    • FAQs
    • Support
    • Larisa Golovko
  • Sale
  • en English
    af Afrikaanssq Albanianam Amharicar Arabichy Armenianaz Azerbaijanieu Basquebe Belarusianbn Bengalibs Bosnianbg Bulgarianca Catalanceb Cebuanony Chichewazh-CN Chinese (Simplified)zh-TW Chinese (Traditional)co Corsicanhr Croatiancs Czechda Danishnl Dutchen Englisheo Esperantoet Estoniantl Filipinofi Finnishfr Frenchfy Frisiangl Galicianka Georgiande Germanel Greekgu Gujaratiht Haitian Creoleha Hausahaw Hawaiianiw Hebrewhi Hindihmn Hmonghu Hungarianis Icelandicig Igboid Indonesianga Irishit Italianja Japanesejw Javanesekn Kannadakk Kazakhkm Khmerko Koreanku Kurdish (Kurmanji)ky Kyrgyzlo Laola Latinlv Latvianlt Lithuanianlb Luxembourgishmk Macedonianmg Malagasyms Malayml Malayalammt Maltesemi Maorimr Marathimn Mongolianmy Myanmar (Burmese)ne Nepalino Norwegianps Pashtofa Persianpl Polishpt Portuguesepa Punjabiro Romanianru Russiansm Samoangd Scottish Gaelicsr Serbianst Sesothosn Shonasd Sindhisi Sinhalask Slovaksl Slovenianso Somalies Spanishsu Sudanesesw Swahilisv Swedishtg Tajikta Tamilte Teluguth Thaitr Turkishuk Ukrainianur Urduuz Uzbekvi Vietnamesecy Welshxh Xhosayi Yiddishyo Yorubazu Zulu

KML to Excel | Extracting Geographical Lat-Long data from Google Earth

Extracting Geographical Latitude - Longitude data from Google Earth to use in the professional GIS software for combining with other data layers and building geospatial models

You are here: Home / Equipment / DIY / KML to Excel | Extracting Geographical Lat-Long data from Google Earth

August 10, 2012 By Landviser

If you have some data in table format with IDs for fields or point locations on the map and Google Earth KML file with those fields having same IDs, it is generally possible to bring spatial information to Excel and merge those data sources together to use in further spatial analysis in other GIS software and in newly released FREE ESRI Maps for MS Office Beta 2. There are a few little tricks to it, though…

Google Earth KML files are essentially XML files with geographical information, but Excel will not open them directly.

image

If you click Yes, Excel will recognize the file as XML, but will fail to import it nevertheless.

imageimage

So, you will need to re-name the KML file so it has XML extension. Right click the KML file and select “Open with” Notepad:

image

It will show XML formatted text in Notepad.

image

Go to File/Save As and select “all files” option.

image

then type NEWFILENAME.xml together with extension

image

Open a new MS Excel file (or the file with your tabulated data information you want to bring KML location data in). In File tab, hit Open and open the NEWFILENAME.xml file. It will recognize it as XML table and suggest schema. Agree to everything! Winking smile If all goes well you should see the table with all your data similar to

image

If your data had polygons, each field will have two lines, sort by coordinates column and erase empty lines. You will end up with a table like this:

image

Notice, that both Latitude and Longitude in in the same column, separated by comma. In Excel 2010 you can separate those rather easily. Insert empty column to the right of the merged coordinates column. Highlight the coordinates column, go to  Data tab and click Text To Columns, pick Delimited, and Separated by Comma.

image

Now you have it – two separate columns with exact geographical coordinates in Decimal Degrees, to merge with your other data in Excel. Name them X-Longitude and Y-Latitude (remember, in western hemisphere, Longitude is negative!)

image

Converting KMZ file to KML

The procedure described above will not work on KMZ files directly. When you open KMZ (zipped KML file) in Notepad, you will not see XML formatted text as in example above, but some gabled code like:

image

To convert KMZ to KML you will need Google Earth installed. Bring KMZ to the map, right click on the layer you want to convert and select “Save Place As”

image

“Save as KML” from drop down list. This will generally blow up the file size about 3 times. To bring those Coordinates to Excel, follow the instructions above for KML file.

Troubleshooting

image

If import fails read the Log info, for me it has happened when field names had spaces, like "11Z365 D"… Thanks to readers of this blog (Michael Bruening) there is a solution how to get rid of spaces in Excel: you could always select the column/row within the excel spreadsheet and do a “Find and Replace” of a [space] with an [underscore].

If you have ESRI ArcMap installed, you can go to Toolbox\Conversion Tools\From KML to Layer

image

Then go to Point Table, add XY coordinates to Centroids (procedure in previous post), and finally export that table to Excel.

If you need futher assistance with your KML data, please, do not hesitate to contact Landviser, LLC - post comment, email info@landviser.com, or call 609-412-0555.

Filed Under: DIY, Geography, GIS

Footer

Landviser - Enlightening Research

CONNECT WITH US

Solutions

  • Equipment
  • Software
  • Analytics
  • Maps

Products

  • LandMapper
  • SibER
  • AEMP-14
  • RES2DINV / RES3DINV
  • RTK GPS
2008-2021 (c) Landviser, LLC. Powered by JetRank Agency
en English
af Afrikaanssq Albanianam Amharicar Arabichy Armenianaz Azerbaijanieu Basquebe Belarusianbn Bengalibs Bosnianbg Bulgarianca Catalanceb Cebuanony Chichewazh-CN Chinese (Simplified)zh-TW Chinese (Traditional)co Corsicanhr Croatiancs Czechda Danishnl Dutchen Englisheo Esperantoet Estoniantl Filipinofi Finnishfr Frenchfy Frisiangl Galicianka Georgiande Germanel Greekgu Gujaratiht Haitian Creoleha Hausahaw Hawaiianiw Hebrewhi Hindihmn Hmonghu Hungarianis Icelandicig Igboid Indonesianga Irishit Italianja Japanesejw Javanesekn Kannadakk Kazakhkm Khmerko Koreanku Kurdish (Kurmanji)ky Kyrgyzlo Laola Latinlv Latvianlt Lithuanianlb Luxembourgishmk Macedonianmg Malagasyms Malayml Malayalammt Maltesemi Maorimr Marathimn Mongolianmy Myanmar (Burmese)ne Nepalino Norwegianps Pashtofa Persianpl Polishpt Portuguesepa Punjabiro Romanianru Russiansm Samoangd Scottish Gaelicsr Serbianst Sesothosn Shonasd Sindhisi Sinhalask Slovaksl Slovenianso Somalies Spanishsu Sudanesesw Swahilisv Swedishtg Tajikta Tamilte Teluguth Thaitr Turkishuk Ukrainianur Urduuz Uzbekvi Vietnamesecy Welshxh Xhosayi Yiddishyo Yorubazu Zulu