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

Google Maps API with large data sets (Millarian)
Open letter to recent college graduates (PamSlim)
How to SEO your Blog Titles (BlogGlue)

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

71 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
Robb Barrett

When I try to run the file (in PC Excel 2007) I get Run Time Error 76, Path Not Found. Any suggestions?

Sep 17th, 2010
denQers | Robert

Hi Tomas,

Great site, especially all the comments from people looking for a GIS solution on google maps for there problem. Especially Kevin with his link to http:/bit.ly/batchgeo For me it’s perfect: based on postal codes creating a map, and grouping if you like this.
Robert

Sep 19th, 2010
Jennifer

Wow this is really helpful! I was wondering, if I only have the zip code or city name for the locations I want to plot how would this work? (I’m not super concerned with getting the specific locations correct, just making sure the companies I have are located in the correct city/general vicinity). Thanks!
~Jennifer

Nov 12th, 2010
Colette

Great article, a note for those that don\’t have Excel or the like, you can use Sheetster.com to export your spreadsheet data online, it\’s open source and free. There\’s a YouTube video explaining how to do it, it\’s pretty simple, secure, and fast: http://www.youtube.com/watch?v=Q6m3CmMO7mM

Nov 22nd, 2010
Chris

Hi Thomas,

great program! My only problem is when I generate the klm file the coordinate format is not good:
the dot is replaced by a coma as below:
-73,704348, 41,132518,0

while it should be:
-73.704348, 41.132518,0

is there anything I can do to fix it
Thank you

Christophe

Nov 27th, 2010
Tomas Moore

Hi there,
I have just created a google maps of my ZIP adress from an excel spreadsheet without macro or programming.
It works well.
http://www.richanalysis.net/richcarto
Tomas

Dec 7th, 2010
Tim

Great program. Tried batchgeo first, but can’t change what it does very much. With your program I’ve learned enough about KML files to enhance the spreadsheet to add pin style and color, add point names, and add descriptions. And I suppose this can be just the beginning.

Thanks!

Jan 24th, 2011
Andy Furukawa

Hello,

I tried using your application. When I click on the “Geocode” button, I receive a Microsoft Visual Basic error message; Run-time error ‘-2147012867 (80072efd)’: A connection with the server could not be established. Your thoughts? Does your application work with just city, state and country info?

Thanks!

Feb 11th, 2011
fitobenitez

Hello and thanks for the post!

I am trying to create it for addresses in Finland. I have added all the info as in your example but when i try to generate the Geocode it gives me an error… Run-time error ‘-2147012867 (80072efd)’ A connection with the server could not be established.

Question: What could be the problem? Also, can I add a new tab for example for country? Would this help for geocoding? And if so, where should I add the column? I am a bit lost if I can add new columns and in which order they should be.

Thanks for any help in advance

Feb 16th, 2011
Carl

Sorry must be missing a key step somewhere but everytime i open the file i get a compile error.
Am running excel 2010 64 bit can anyone help me out

Feb 23rd, 2011
Faiz

Hi, will this tool works for other part of the world.? say Malaysia.?

Mar 1st, 2011
Danielle

Thanks for the detailed info. Do you know if there is a way to update a google map or google earth with our position via email? We will be sailing to Hawaii from California, and can only send emails over our SSB radio. Needless to say, we can’t really surf the web in the middle of the Pacific ocean, yet we would like to keep our friends and family informed of our whereabouts. Thank you!

Mar 1st, 2011
paul

Great program, but does anyone know how to add extra columns to it so I can expand the descriptions in the baloon?

Mar 4th, 2011
Frederik

Geocoding works very good. Managed to change some minor things to my own needs so that briliant. However, the automatic launch of the kml file in Google Earth won’t work. Search trough the internet but it seems I’m the only one having this problem. The start up of google earth itself is no problem but the kml file isnt opening. I can see some kind of msgbox appearing but it is only there for like 1 millisec so I can’t read what is says.
Can anyone help me out with this? It is quite essential for me that it runs when pushing the button. I don’t know whats wrong. In my own file and the file shared here it isnt working. Except for the Google Earth execute location I didn’t change anything..

Thnx in advance.

Mar 30th, 2011
Frederik

In addition:
I’ve found out that if I run the code to execute the kml file with notepad it works just fine. Only when I try to open it with GE it will not work…
Anyone?

Thnx

Mar 30th, 2011
nik

Hi there! Great Program dude!!! I was just wondering if i could use it with Greek characters? I did input the same address both in greek and in latin characters, but the greek input was way out! I have a large database of people i need to make a map of but it’s all in greek!!!

Many thanks!

Mar 31st, 2011
Aaron

This is really useful. I work for a university as an admissions counselor and have many schools within my area of responsibility. This really helps in managing my area. What helps most is having Google Earth on my iPhone and being able to see all the information I have on the school I am going to through my phone. Love it, thanks for this tool.

Apr 20th, 2011
Daniel Bloch

FYI, on 3-17-11, the folks at Dabble DB announced that they’re shutting the service down effective 5-18-11. Bummer.

May 6th, 2011
robi

Hey,, this is great.. But I want to know how mauch data can be handled. I mean my excell containing morethan 5000 rows, will it work for that. One more question..If I have excell containing valid what is the requirment to use Geocode here.?

May 23rd, 2011
Jorge

Por favor como puedo hacer para exportar coordenadas de google earth a excel

Jun 2nd, 2011
Dennis

Dear Sir,

Does the programm also work with other countries, like the Netherlands?
I have tried this but it doesn’t work.
Can you please help me?

Thanks,
Best regards,
Dennis

Jun 6th, 2011
Chrissy

Great information – thank you!

Jun 6th, 2011
Matteo

Great work, you saved hours and hours of work… and now my marketing collegues can work directly on the excel file and can generate the updated kml file with a click.

Thanks.

Jun 22nd, 2011
elma

@laurent, @Frederik

I too had that issue.

I still haven’t managed to get it to work from the ‘Run Google Earth’ button, but have managed to open the kml file from Google Earth itself (ensuring that the filepath in Step 1 ends in .kml)

@Tim – would you be able to share your knowledge with regards pin style and colour etc?

Jul 5th, 2011
Nick

It wouldn’t work when I intered C:\filename in cell C2. Replaced C: with D: and it works fine. Problably a permission issue.

When using it for other countries than the US, I’ve found it to be useful to enter the country in the “State” column.

The only problem left is the format of the longitude and latitude. It exports the longitude and latitude using a , (e.g., 6,1) rather than a . (6.1). A solution to this would be very much appreciated, since I now still have to manually change all of this.

Aug 16th, 2011
Mani

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!

Aug 22nd, 2011
Mani

kindly reply me as soon as possible
it will be much helpful if u replied soon..thanks…

Aug 23rd, 2011
Christian

I downloaded this excel file and was able to get the Yahoo geocoding to work but it won’t create an .kml file. I tried different folders and filenames to no avail – when i search my computer for a .kml file I get nothing.

The only thing I can think of that would still be a problem is that I’m using office 2010 and it looks like this file was created in Excel 2003. I tried it in compatibility mode, and also tried updating the file to 2010 and neither have worked so far. Does anyone else have this same issue and know of a fix?

Thanks

Christian

Aug 25th, 2011
Bruce

Here’s another way to map data directly from excel into yahoo, google, ovi and bing maps

http://ramblings.mcpher.com/Home/excelquirks/getmaps
Bruce

Sep 8th, 2011
albert

I tried it and it worked perfectly. A very useful tool.
Now I tried to export the data to an Arcmap project but I don’t know the spatial reference of the cordinates and I can’t see it. Is there an option to check it or can I get the data from yahoo server? I can’t find it!
Thanks!!

Sep 14th, 2011
Jonathan

I have been using this tool for about 2 weeks and everything has been working perfectly until today! When I input the address and press the Geocode button all I get is “not found” for the Lat, Long, and Percision fields. It will still let you create the KML file however all the points will be put in the same but random location on Google Earth. Any suggestions??

Sep 20th, 2011
albert

Is the yahoo service down? For how long? Is not working anymore..

Sep 21st, 2011
Jon C.

I would like to know whether it is possible to retrieve a map of locations and lat/long information based on addresses within Malta.

This is part of an academic project where I would then seek to compare parking spaces and bus routes within the Maltese Islands, and, provide travelling information such as:

distance between two points (on the road)
fuel consumed based on a price tariff
bus routes available within the area

Sep 25th, 2011
david

Hi,

for certain issues you can also use my tool:
http://winowin.de/product.php?pf=windows&prod_id=56628&xsearch=kml

(Demo is free)

Greets
David

Oct 3rd, 2011
Josh

Hi,

I have used this excel geocode in the past and now that I have office 10, geocode can not find long/lats for my addresses… I have all macros enabled and now wondering if it has to do the firewall or so on. Any info would be a great help. Thanks

Oct 6th, 2011
Tom

I am having the same problem as Jonathan but this is the first time I used it.

Oct 7th, 2011
albert

It’s not working anymore Jonathan. I also get the “not found” because the yahoo api is no longer functional. It should be a way to change the macro to use another aplication such Google Api but I don’t know how. If somebody can change it please, let me know.
Here there is another tool to geocode from excel
http://ramblings.mcpher.com/Home/excelquirks/getmaps

Oct 13th, 2011
bruce

Although Yahoo Maps was supposed to close last month, I just tried

http://ramblings.mcpher.com/Home/excelquirks/getmaps

which can geocode or map using all of Yahoo, Bing, Ovi, or Google- and the Yahoo option is still working as of today.

Bruce

Oct 13th, 2011
norma peralta

Was wondering if you could help me with the code changes/modifications needed so i can add different pin styles and colors.

Thanks you! I love this tool BTW!

Norma

Oct 20th, 2011
Samarth

TOMAS: Thanks for the great post. I’m using 64 bit OS as well as Office. and it gives me an error indicating that i need to change some things in the VBA code to make it work. the code in mClipboard is red so I think I need to change something there. Can you please guide me?

Oct 31st, 2011

Playing With Google Maps | Blog - Feb 11th, 2011