AHK: How to Code a KML to Excel Exporter

I accidentally opened up a KML file in Notepad once. There are no words in the English language to describe that specific form of horror. It’s generally brought on by questions like: “Did I just delete every other file in my C drive?” or “How the shit did I disable my keyboard? That script wasn’t even sending keystrokes???”

Or when the IT guy at work remote accesses your computer, fixes the problem, then says, “Oh, hey, I can see you” and then HANGS UP.

I have no webcam on my work computer, guys. NO WEBCAM.

It appears my ADD has hijacked this post. Attempting to refocus.

My point is that I panicked for no reason.Well, not no reason. I mean, look at this:

But when I stopped to really look at it, I realized the innards of a KML are surprisingly readable. It’s literally latitude and longitude with some headers in tags. That’s it.

So today, in keeping with my recent VBA post, I am forgoing the lovely graphics once again and plowing my way through a script that is, in reality, only useful to me.

You’re welcome.

Step 1: Understanding the Problem

See all these points in Google Earth? Plotted them at random. Here’s a breakdown of what I want from the KML file:

Step 2: Prepping the File

Let’s start off by prompting the user for the KML file in question.

A quick review of AHK functions will get you to FileSelectFile, alls you gotta do is change the output variable to something memorable and filter for KMLs.

I talked about FileRead in a Web Scraper post a while back. Basically, this is how we get the computer to push text into a single variable that we can chop up.

After a long shouting match with my laptop, I discovered FileRead won’t read a KML. So before we continue, we have to turn the .kml into a .txt using FileCopy.

Step 3: Trimming the Variable

InStr() is really helpful here. Since we know the latitude and longitude will immediately follow the word “coordinate,” we can have the script search for that and then plug the Occurance value into a variable that jumps up by two with each loop–one for <coordinate> and one for </coordinate>.

Setting the return value of InStr to a variable allows you to plug that into StringTrimRight and StringTrimLeft easily enough.

Step 4: Save It Into A CSV

Using FileAppend, you can throw each set of coordinates into a .csv. The only frustrating thing here is remembering to escape any commas or n’s for new lines with the ` key.

The Code

In the interest of not staying up all night writing a GUI while binge watching ridiculously old episodes of Unsolved Mysteries, I’m posting the code as is.

Minus the GUI, the copied .txt and destination. csv files have to be manually inserted (they’re on the Desktop below), as well as the loop count for the number of points.

FileSelectFile, TheFile, , , KML to Convert, (*.kml)
FileCopy, %TheFile%, c:\Users\Desktop\KML.txt
FileRead, FileContent, c:\Users\Desktop\KML.txt
Occ := 1
loop, #
{
FoundPos := InStr(FileContent, "coordinates", false, 1, Occ)
FoundPos+=11
StringTrimLeft, TrimContent, FileContent, FoundPos
FoundPos := InStr(TrimContent, "coordinates", false, 1, 1)
StringTrimRight, AllTrimContent, TrimContent, StrLen(TrimContent) - FoundPos + 3
FileAppend, %AllTrimContent%`,`n, c:\Users\Desktop\KMLTest.csv
Occ+=2
}

Msgbox, Done
return

 

That’s all. Roll credits. I’m thinking I’ll post a One Lovely Blog Post at some point this week, and if not, Processing next weekend.