I often get questions such as: “How do I export Google business data into a database?” or “How do I import Google locations data into an Excel sheet?” They’re both pointing to the same need. The scenario is that we can search on Google Maps or Bing Maps or just on any online Search engine to find business near a specific location, such as “find x near me” and we get the results almost immediately on our devices (phones, PCs, etc.) and we choose one and find the directions or phone number or their web site. However, to build a dataset that includes all the relevant results in a place is very useful. That way, when one is not suitable for our needs, we can move on to the next item in results list without having to search and click every time. Additionally, this dataset is useful information for analysis to marketing campaigns to many other use-cases.
To be able to do this first, we have to extract the information as text, not fancy 3 maps (we can always do that later or as needed on our devices), and save them in a structured format that can be used to build a database, to serve up the information as a web service, do analytics, and much more. In this post, I’ll show you to use Google’s data to accomplish just that with little effort and virtually no $$ cost to you for the information. Everybody wins! Businesses will be happy to have the expsore. Google will be happy to have users like you (more users->more traffic->more ad sales/subscriptions->more $$$ for them). And you’ll be happy to have the information for your intellectual and/or practical purposes.
This requires no coding knowledge really although understanding XML or JSON type formats will be useful, and having basic->intermediate knowledge of Excel is assumed.
The Steps
First, you’ll need Google Earth Pro application installed (not the web app, at least at the time of this writing, that doesn’t support the export feature as below)
Once launched search for the place(s). In this example, I searched for: “kayak rentals near me”.
Once the results show…add the results to My Places.
Do a Save As on that node and choose type: KML
Open the KML file in Visual Studio (preferred) or any text editor (won’t be color-coded or verified). It’ll look something like this:
<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom"> <Folder id="results"> <name>kayak rentals near me</name> <Placemark id="1.2.1"> <name>WhatsSup Stand Up Paddle and Kayak - Bothell Landing, Bothell</name> <address>9919 NE 180th St, Bothell, WA 98011</address> <phoneNumber>(425) 417-8637</phoneNumber> <snippet>9919 NE 180th St, Bothell, WA 98011</snippet> <description><![CDATA[<!DOCTYPE html><html><head></head><body><script type="text/javascript">window.location.href="https://www.google.com/earth/rpc/entity?lat=47.816313550000004&lng=-121.83447530000001&fid=0x54900e6b42663a6f:0x5153499332c39cc6&hl=en&gl=us&client=earth-client&cv=7.3.3.7786&useragent=GoogleEarth/7.3.3.7786(Windows;Microsoft Windows (6.2.9200.0);en;kml:2.2;client:Pro;type:default)";</script></body></html>]]></description> <styleUrl>#listing_A</styleUrl> <ExtendedData> <Data name="placepageUri"> <value>https://www.google.com/earth/rpc/entity?lat=47.816313550000004&lng=-121.83447530000001&fid=0x54900e6b42663a6f:0x5153499332c39cc6&hl=en&gl=us&client=earth-client&cv=7.3.3.7786&useragent=GoogleEarth/7.3.3.7786(Windows;Microsoft Windows (6.2.9200.0);en;kml:2.2;client:Pro;type:default)</value> </Data> </ExtendedData> <Point> <coordinates>-122.2089837,47.75786449999999,0</coordinates> </Point> </Placemark> ...
It turns out to be straight-up XML. We can import XML into Excel alright! So, in Excel, choose Get Data as XML.
Choose the xml object shown and double-click to open in Power Query Editor (PQE).
In PQE:
Before loading the data, Transform, then Expand the column titled Table.
Then Expand the column titled Placemark.
Then Close & Load
Sample loaded sheet will look like this:
It’ll have all the basic information such as name, address, phone, but also location information for GPS that you can render/fine-tune on your own if you wanted to. Delete the columns you don’t need. Rename the columns to your liking. And you’re done. Append to it with more dataset as you wish by launching PQE again and editing the query. Need more help? Read below…
This post is not meant to be a step-by-step, detailed tutorial, instead to serve key concepts, and approaches to problem-solving. Basic->Intermediate technical/coding knowledge is assumed.
If you like more info or source file to learn/use, or need more basic assistance, you may contact me at tanman1129 at hotmail dot com. To support this voluntary effort, you can donate via Paypal from the button in my Home page, or become a Patron via my Patreon site. A supporter/patron gets direct answers on any of my blogs including code/data/source files whenever possible.
