Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to import a KML Google Earth File to Excel and have it map individual locations to proper cells

Copper Contributor

Hello,

 

I am probably looking for help doing something that will wind up complicated and truly be easier to just copy/paste the URL addresess for each geo location into the proper cell in Excel but figured I would ask to see if there's an easy way to do what I am looking to do.

 

I have created a project using Google Earth and it contains so far 4 individual locations but will continue to grow.  I would like to know if there is an easy way to import the KML file and have it properly assign each location to a corresponding cell in Excel.  For example, I enter the address I am looking for in Google Earth (GE) and when I save the location to the GE project I edit the identifying information to include the full address and the website URL.I already have an Excel file with information on each location that includes the address and the URL address so there will be a 1 to 1 match and each match will need to be entered into column "D" (now named "Google Earth Geo Location).  I would like it to be either simply a clickable live URL or automatically open the GE location in that cell to give a visual of the location (if possible it would be AWESOME - but not a dealbreaker).

 

I worked a short while ago with a Microsoft 365 support person and he got me though the following:

 

  1. Rename a copy of the KML file exported from GE to .xml
  2. Import that file into Excel choosing the "XML Template" option and clicking yes
  3. Saving that file as "file name whatever" .xml
  4. That's as far as we got.

If it is going to be far easier for me to simply copy/paste the URL for the geolocation please feel free to tell me that.  I am looking for easy not for nuclear physics.

 

Is there any way do do this?  Full disclosure, I am not a scripter or a programmer.  I can however, follow directions and pick it up.  I have provided the master worksheet and since this system doesn't allow .xml files to be uploaded I have uploaded a screenshot of the Excel .xml worksheet I have created.

 

Thank you,

Wonder Woman

2 Replies
Perhaps you can upload that KML file after you first change the extension to -for example- .txt ?

@Wonder_Woman 

KLM is just XML file with lot of information but a direct link on the place. It could be parsed by Power Query, we may extract longitude and latitude from it, keep them in some helper table, after that generate links by formulae. Not easy way.

 

Easier to copy/paste URL.

 

One more way is to generate the link from the address, but you don't have separate cell with address only, lot of other information is in the same cell. For the address only links like

https://earth.google.com/web/search/1300+Macdade+Boulevard+Folsom+Pennsylvania

https://earth.google.com/web/search/401+east+1st+street+Sanford+Florida

https://earth.google.com/web/search/55+Broadway+New+York+NY+10006

etc

most probably could be generated by formulae.

They shall give the same as if we use the link with coordinates

https://earth.google.com/web/search/40.706798,-74.012901/