Export Excel Data to Google Maps

Export Excel Data to Google Maps!

February 8, 2009

Export Excel Data to Google Earth and Google Maps

Wouldn’t it be nice if you could export an Excel list of addresses to Google Earth and Google Maps? Thankfully, there are several ways to do this but the only question you need to ask yourself is, “How secure does my data need to be?”

Although Google Maps gives you the option to remove your saved maps from search results, a public URL of your map still exists and can be accessed by anyone with that URL. If in doubt, keep your map’s URL to yourself or use Google Earth instead.

Mapping Excel Data Using VBA

Process Trends created an Excel spreadsheet that incorporates the work of Juice Analytics and simon_a to geocode addresses and prepare them for Google Earth and Google Maps. To begin, download the MapExcelData.xls file and follow along.

**Attention Excel 2004 for Mac users, the MapExcelData.xls spreadsheet will not work due to the missing VBA Split function. Unfortunately, you’ll have to run MapExcelData.xls on a Windows version of Excel.

Step 1: With macros enabled, open the MapExcelData.xls spreadsheet and go to the KML_details tab. Change the document name in cell C3 to whatever you wish, and update the file path in cell C2 to indicate the name and save location of your KML file.

For example, if you want the file to be named digdug.kml and saved to your desktop, the file path will look something like this:

Step 2: Go to the data tab and starting at row 6, paste your address data in columns D through I. Leave columns A, B, and C blank since the macro will find this information for you.

Copy and Paste Address Data Into MapExcelData.xls

Step 3: Select the rows you would like geocoded, and click the Geocode button at the top of the spreadsheet. The latitude and longitude values will begin to fill columns A, B, and C.

Step 4: Once geocoding is done, click the Gen KML File button and your KML file will be created. If you have Google Earth installed, then click the Run Google Earth button in the spreadsheet or double-click the newly generated KML file to see your addresses plotted on the globe.

In the interest of time, I’ve created a pdf that shows you how to export Google Earth locations to Google Maps if needed, or…

Bonus Tip: You can open your KML file in Google Maps without the help of Google Earth. First, upload your generated KML file to a public web space like your blog or Dropbox public folder. Next, copy and paste the URL of the file to the maps.google.com search field—click the Search Maps button and you’re done.

Feel free to give it a try with this URL:
http://theclosetentrepreneur.com/wp-content/uploads/2009/02/digdug.kml

Once your addresses are in Google Maps, you can click the Save to My Maps link and have them available whenever you need them.

Other Ways To Map Excel Data

If you don’t want to use the MapExcelData.xls spreadsheet, then batchgeocode.com allows you to create a KML file from your Excel data using their online form.

Another option is to use Dabble DB, an amazing online database tool that can generate KML files for Google Earth. Unfortunately, your data is public when using their free plan so either pay for a plan if you need privacy, or take advantage of the free 1 month private trial.

In Conclusion…

I’m hoping you can make use of this blog post, but If you’ve read this far and feel that you have no use for this information, then consider…

Creating a map of your delivery locations
Mapping out a road trip
Creating a map of foreclosures or homes for sale
Map your favorite art galleries or hole in the wall restaurants
Map local coffee shops, cafés, and coworking spaces that offer free Wi-Fi
Create a map of local arcades that have coin-operated Galaga machines
Create a map of local restaurants that offer 24 hour service
et cetera…

See any uses for exporting Excel data to Google Maps that I may have missed? If so, feel free to leave a comment!

Sincerely,
The Closet Entrepreneur

Post Scriptum: A big Thank You goes out to the guys at REsolved LLC for the tips on their Get Your Data on Google Maps For Free video!

The Closet Entrepreneur Recommends

How Not To Use Social Media (The Arkayne Blog)
Google Maps API with large data sets (Millarian)
How to SEO your Blog Titles (The Arkayne Blog)

» This entry was filed under Excel and tagged with: Advice, tips, Tutorials

30 Comments

[Charlene]

Great post, Tomas, jam-packed with the nitty-gritty details. This is an area I’ve been itching to explore, and with your road map, I’ll be digging in soon.

Feb 9th, 2009
TOMAS

@Charlene – Thanks, and let me know how it works out for you!

Feb 9th, 2009
Lakuma

I was wondering if there is way to export the addresses from Google Maps into an Excel Document?

Feb 20th, 2009
TOMAS

@Lakuma – A few websites have mentioned that ZeeMaps can export Google Map data to CSV, which can then be imported to Excel but I personally haven’t tried it. You might also want to ask around on the GIS forum and Google Maps forum–hope that helps!

Feb 21st, 2009
Kevin

This is exactly what I’m looking for. However, I have a Mac but have updated to Office 2008. Do you know if the Macros issue has been resolved with that version?

Mar 11th, 2009
TOMAS

@Kevin – Unfortunately, Microsoft removed Visual Basic for Applications (VBA) and macro support from Excel 2008 for Mac—so in other words, macros cannot be run in Excel 2008 for Mac.

It’s a total bummer, but according to some of the comments by Erik Schwiebert (Software Design Lead at Microsoft Corporation in the Macintosh Business Unit) it looks like VBA may be removed from future version of Windows Office products as well.

Although Mr. Shwiebert does mention that the AppleScript object model and the old Visual Basic object model for Windows apps are roughly equivalent, so if it can be done in VB then it can be done in AppleScript.

If you’re still looking to try and get the macro to work in Excel 2008 for Mac, then you might want to check out this article from MACTECH that shows you how to transition from VBA to Applescript.

I hope this helps!

Mar 11th, 2009
Kevin

Tomas,

Thanks for the info. I did a bit of searching and, for what I need to do, I think I found something that’ll work out just fine. It’s a website that will convert Excel data into KML for me. I did a few test runs and it worked just fine. The website is: http://www.batchgeocode.com/. Thanks again!

Mar 12th, 2009
Excel export to Google Earth

Great Post. A video tutorial in german language can be found at

http://www.blogjack.de/endlich-gelost-excel-export-to-google-earth/

Mar 21st, 2009
George Petrolekas

Interesting…but have a different need though don’t know how to do it.

I have a county or city column and then a size (figure) column to depict the size of an organization in a country/city. What I’d like to be able to place in a map is a circle which is relative in size/scale to the size of other country date. i.e. If the US contributes 10 sheep to Mexico and Canada contributes five, then I’d like a circle in both Canada and the US with the US circle being double the size of the Canadian circle. Is this doable?

Mar 22nd, 2009
TOMAS

@Excel export to Google Earth – Thanks for the link!

@George Petrolekas – What you’re looking for is doable, but unfortunately I don’t have enough experience with Google Maps or image overlays. You may want to get a hold of Ahmed Farooq from iBegin.com (there’s a contact link on the site). He’s been trying to create a heatmap on Google Maps and you can see his progress by visiting his blog at:

http://www.techsoapbox.com/heatmaps-tough/

You’ll also see that he’s trying to code it using various methods but hasn’t quite perfected it just yet. Anyway, I hope that helps!

Mar 23rd, 2009
Paul

This is a great tool. I’ve been searching for a while to find something like this. I have been able to view the addresses in Google maps (which is what I really wanted, rather than GE) But I have to ask, is it possible to add additional fields to the spreadsheet, like:-
1. Different coloured icons for each group of addresses. I have 3 groups of companies that I want to display with different coloured icons; there are about 20 companies in each group
2. The address is used to geocode but it’s not shown when I click on the map icon, ok that’s not a real problem, I just add the address to the description column in the spreadsheet. But it can get a bit congested when I add the phone number as well.
3. An extra column for notes would also be good
Summary – An excellent piece of kit

May 3rd, 2009
TOMAS

@Paul – You might want to talk to the guys at Process Trends since they’re the ones who put this version of the spreadsheet together. I’m pretty sure what you’re looking for is doable, but I wouldn’t have the slightest clue on where to start making changes.

May 7th, 2009
Sameer

Hi, Its a Great tool, I have a question?

I am in Police department , for citizen’s help Can I link the data base of crime on Google maps with different symbol for different category . so that we can see area wise crime report and also add criminals photo and details.

Jul 21st, 2009
TOMAS

@Sameer – That’s a great question and unfortunately I’m not too familiar with Google Maps’ API, so wouldn’t be able to be of much help. Although I do know that you can add your own custom icons through the KML file; more info can be found here:

http://code.google.com/apis/kml/documentation/kml_tut.html

Hope this helps!

Aug 2nd, 2009
James

Hello, I just found your site and I am still trying to figure it out. I just want to have my kml file plot to Google Maps instead of through Google Earth. I keep receiving an error when I want to open my kml file. It is stating something about “Whitespace is not allowed at this location” any suggestions?

Thanks

Oct 17th, 2009
TOMAS

@James – XML has a special set of characters that cannot be used in normal XML strings, and this might be causing the error.

Check out this Special Characters and XML Strings tip to see if your file is being affected by a special character.

Oct 19th, 2009
Hemanth

Thank you very much. This is a wonderful link i guess. I have to still explore the link and upload my address data. Hopefully it work fine for me.

I just want to add my office address and check whether this works.

Oct 20th, 2009
Paul

Apart from the special characters and xml strings, I have found that if you try to upload to Google maps it often throws up an error. As an alternative, if you view it in Google Earth and then save the kml file from Google Earth, the kml file get re-ordered and can then be more easily uploaded without error to Google maps.
I have never understood why this works but it worked for me on several occasions.

Oct 20th, 2009
James

Tomas,
Thanks for your reply on the special characters. I have another situation. I enter all the addresses and receive the Geo Codes at no problem. When I receive the KML file, I click to open the kml file and the links go into a spreadsheet. If I enter more than one address, the kml file creates individual links per address. Is it possible to create just one link to input all the addresses on Google Maps at once? Also, when I click on the individual link and copy and paste into Google Maps and click on Search Maps, I receive the following error:

http://maps.google.com/mapf... could not be displayed because it is not a valid KML or KMZ file.

Here is the link that my KML file created:

http://maps.google.com/mapfiles/ms/icons/red-dot.png

Any suggestions?

Thanks

James

Oct 22nd, 2009
Pete

So as long as I open the kml file with Google Earth vs. Google Maps, the information will be secure?

Thanks,

Pete

Oct 29th, 2009
laurent

I do all the process
an when i click the google earth button it gave an error 53
File not found.
Can you help me?
Thanks

Nov 20th, 2009
Krishan

Tomas,

Do you know of any way of going the opposite direction. I’ve made a map on google maps of 150+locations that I would like to export to an excel file. Is there any way of doing this?

Thanks!

Nov 21st, 2009
Greg Kirby

Regarding uploading addresses to Google Maps, We have all of the addresses (approx 3000) loaded in and have data points for almost 100% of them. When we click on the google maps button, we get a error message that says “run time error 53. File not found. Could you help with the next step? If there is fee I dont mind paying. My son has been helping me with it and his e-mail is [ ]. If you could communicate with him and cc me in the correspondence.

Regards,

Greg

Jan 6th, 2010
Michael

Question:

I have a list of in excel of 150 address / Long/Lat in a specific order. How do I map that out so I get directions starting at the first address on the list and moving to the next and so on?

Mar 22nd, 2010
Jennifer

Hi there!
So I work for a non-profit that will not supply a laptop for me to use in the field. I’ve transferred all the documents I can to my Iphone and thankfully can update spreadsheets and retrieve data from it. I’ve been trying to customize a map (which I’ve been successful doing) of our clients and then send the map to my phone. It comes up as an internet link… but it’s really cumbersome and time consuming to zoom in, etc. Is there a simple way of taking an excel contact document and mapping it directly on my Iphone?
Thanks so much for your time!
-jen

May 21st, 2010
Franz

Hi
I have a slight different problem. I have the coordinates (latitude/longitude). What I need is to reverse geocode and get the address that goes with that coordinates to Excel. I also would need to get a map segment from GoogleMaps around the specified coordinates back into Excel.
The URL to request the address in the function ‘yahooAddressLookup’ in the vba module ‘mGeoCode’ is:

URL = “http://api.local.yahoo.com/MapsService/V1/geocode?appid=” & yahoo & “&street=” & street & “&city=” & city & “&state=” & state & “&zip=” & zip

How does this request have to look like to get the address from the Latitude/Longitude coordinates set ?

Jun 10th, 2010
David

How to retrieve the value from database and insert that value in Google map?

Jun 17th, 2010
Zahid

Great Post. I like the idea. I tried a different approach, I used As3xls llibrary and then used it with ESRI’s FLEX API. But you can use with google flash maps api or yahoo maps or anyother in FLEX/FLASH environment. here is the URL to my post
http://zachsgeospatial.blogspot.com/2010/06/mapping-point-data-from-excel.html

I think, it is step forward, in plotting gis data from clients computers using web browser..

Keep up the good work

Jun 25th, 2010
Adam

I tried your Excel template, but for some reason the macros couldn’t run. I checked the security settings and made sure the macros were there but I got a “Run-time error ’5′ Invalid procedure call or argument” in the macro “mGeoCode” line 350.

I then used BatchGeo as you recommended and it worked great.

Thanks for the reference that met my needs albeit indirectly. I’m mapping sales for my company.

Jul 23rd, 2010
Pam

Hi There,

I just read this blog and comments. A lot of things talked about here can be done easily with GmapGIS. It seems to be a new web application to load lat/long data to Google maps in addition to many other functions. You can geocode any place, draw lines, polygons, markers, labels. Above all you can save your drawings/markers on your computer as a file or send it to someone as link.
The web address is http://www.gmapgis.com or just search for gmapgis.
Pam

Jul 27th, 2010