Import KML File into MS Access

[M_T]

Hello.

We have a system which generates a KML file. We'd like to import this file to MS access to run some queries on the data, and create a new KML file from access.

I don't believe creating the KML file would be an issue.

But, importing the KML into Access may be another issue. Any ideas on how to import the KML into access? I've tried changing the extension to xml, but the data isn't imported property. The data is split into many different tables: Folder, Icon, LabelStyle, Placemark, etc.

Any Ideas?

- MT

[simon_a]

Hello,

Welcome to the GE Community!

I've not tried importing into Access but I often import into Excel using the same method you mentioned: changing the extension to XML first.

Excel also includes lots of extra rows and columns that I wouldn't want but where the data (about style, for example) is reflected in the code.

I guess you could either try pre-processing it first manually - maybe by importing to Excel and playing about with it - or writing a script to automate that process - either before or after it imports to Access. If you don't have to do this often, I'd probably go for the manual option.



Cheers,

Simon.

[ManoM]

Hi MT,

It depends on what you want to do with Access. Access importing takes a very relational database approach to importing XML documents. As you said, the import tool breaks up the file into different tables based on element names. There does not seem to be a way around this using the direct import feature.

There are three things I can think of, however.

1) You can store the KML file as an "OLE Object", or what in other databases would be called a "BLOB". You store the file object directly in a field in the database. This does not allow you to index or do manipulation easily on the file, but if you want you can add additional fields storing metadata about the file.

2) You can parse it using Visual Basic for Applications, the scripting language for Microsoft Office. You can set up importing functions that break up the file how you want it broken up, and then put it in your table however you want it. Try doing a search on XML VBA Microsoft Access.


3) You can use Access to index the file location. Instead of storing the data in a table, you store pointers to the file location using a Hyperlink file type or a OLE Object with a link.

I hope that's helpful. Let us know what you did, I'm curious.

ManoM

[M_T]

Thanks for the replies.

I was trying to be lazy and was hoping there were some other solutions out there, but it looks like I'm going to have to write a parsing utility using VBA.

The goal is to import this file, do a join on this data with some data in a legacy system, and then re-create the kml file to plot everything in google. There isn't any way to link to live data from a database from Google Earth, is there? The database would have addresses and other building info. That would make this whole process easier.

I'll try to remember to post a follow up regarding my progress in a few days.

Thanks again!

- MT

[ManoM]

Hi MT,

No, there's isn't a way to link directly to a database for data. However, you can use NetworkLink to link to a script served over HTTP. That might be simpler to implement.

ManoM