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)
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

Msgbox, Done


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.



17 Replies to “AHK: How to Code a KML to Excel Exporter”

        1. Noooooo. When you say things like that, you make me think it’s easy even for me, but what’s actually going to happen is I’ll spend the next 14 hours beating my computer senseless instead of sleeping… I’ll try it this weekend and let you know how it goes šŸ™‚

          Liked by 1 person

    1. You know, I’ve had others suggest that I clean up the language. But programming makes my brain think in expletives. And since GifGuide is more of a progress journal (I write as I break down problems–my posts expand and contract a lot as I plow through), it’s just always seemed natural to not filter. That being said, I COMPLETELY understand the awkwardness of referencing a blog filled with rage rants, and I absolutely would not recommend it for children. Sidenote: I really did like your nominees–always looking for more tech oddballs on WordPress. Until next time ECG šŸ™‚

      Liked by 1 person

    2. Oh, the “others” were all in real life–you’re definitely the first WordPresser. What’s funny is that it’s pretty evenly split among brick-and-mortar people I’ve shown. The main argument against profanity is that if I ever tried to set the blog up on its own to sell a few ads (to at least cover my coffee expenses), who on EARTH is going to back a foul-mouthed amateur. The main argument in favor is who on EARTH is going to back an amateur at all. My main argument is that I have to take out my frustration SOMEWHERE. Better on the interwebs than smashing my computer with a mallet. Hey ECG, is there any chance that you go by the name Panda?

      Liked by 1 person

      1. HOLD IT, you just gave me an AMAZING idea for profanity web scraper to figure out if there’s a correlation between the amount of swearing in the post and the programming language and/or functions I’m using in a particular script. Or if the TYPE of swearing switches. To use as a sort of rage gauge, you know? Then I can track my progress over time by looking at whether it’s increasing or decreasing–I mean I have a year’s worth of posts. To be honest, I sort of thought they all had some cursing, but I’ve never actually gone through to see. It just occurred to me that this is going to be quite disheartening if the numbers indicate that my anger levels aren’t on a downward trend. Surely, you’d think the more exposure to programming, the less frustrated I’d be? Huh. Imma look into this and get back to you.


Leave a Reply to Anonymous Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: