AHK: How to Code a KML to Excel Exporter

kmlconverter

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:

kml_innards

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

gescreenshot

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

kmlbreakdown

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.

fileselectfile

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

instri

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.

fileappend

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.

 

 

Advertisements
Tagged , , , , , , , ,

17 thoughts on “AHK: How to Code a KML to Excel Exporter

  1. ARJ says:

    Humm will try csv location visualisation as soon as possible!

    Liked by 1 person

  2. Anonymous says:

    You’re funny xD was a nice read šŸ™‚

    Liked by 1 person

  3. Hey gifGuide2Code! You don’t have to try this if you don’t want to but try just one or two blog posts that don’t contain swears. Because you could be awesome for next year’s one lovely blog ECG

    Liked by 1 person

    • 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

    • 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

      • No thank you, I don’t really like panda. And it is frustrating. You have actually done many posts no swears.

        Liked by 1 person

      • 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.

        Like

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: