Export Excel Data to Google Maps!

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:

Local KML File Path

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.

Export Excel Data to Google Earth

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

Open a KML File Using Google Maps

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!

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

81 Comments

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

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

  3. Lakuma

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

  4. @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!

    :)

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

  6. @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! :)

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

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

  9. @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!

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

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

    ;)

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

  13. @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! :)

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

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

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

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

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

  19. Pete

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

    Thanks,

    Pete

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

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

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

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

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

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

  26. David

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

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

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

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

  30. Robb Barrett

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

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

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

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

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

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

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

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

  38. 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 :)

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

  40. Faiz

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

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

  42. paul

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

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

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

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

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

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

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

  49. Jorge

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

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

  51. Chrissy

    Great information – thank you!

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

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

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

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

  56. Mani

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

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

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

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

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

  61. albert

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

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

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

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

  65. Tom

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

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

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

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

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

  70. Eddy

    Hi, thanks for that however i’ve got a question

    Does this only work in USA or where i am too – New Zealand ?

    It doesn’t seem to be getting the Lat. & Long. any reason why ?

    Thanks!

  71. alberto

    Hello, I have an excel dataset with origin and destination of trips in two columns. I would like to add a column with the distance between the two points. Is there a way to get google maps to calculate these withouth me having to do it manually for all the trips (they are more than 1,000)

    thank you very much
    Alberto

  72. mucrick

    I’ve been using MapExcelData.xls with great success for a couple years. I’d like to know what I need to edit in the file that will allow me to set the height and width of the pop-up balloon.

    I welcome this groups’ input.

    Regards,

    John

  73. Drew

    I’ve spent tons of time looking around for the best solution to this. Batchgeo was the best for a while since it made it simple to create a quick map of my data. Or TotallyAwesomeMapping.com was perfect for creating Google Earth KML (similar to this post above).

    I’ve since moved on to http://Topo.ly. They not only build the map from Excel data, but then they let me do all kinds of analysis on top of it. I can add multiple layers of data on my map and give them different icons for each data set. Then I can filter each layer with values from any of the columns from my spreadsheet.

    You can also build heat maps so that I can show high performing zip codes or counties based on my spreadsheet data.

    Anyway, definitely worth a look. Thanks for the post, Tomas!

  74. Mitch

    Maybe someone can help me with this question. How come when I put in my address on the “data” tab and try to geocode them for the latitudes/longitudes it says “not found”. I would love to be able to geocode my data in a .kml file. I have already set my filepath name and document name. I’m following the directions to the letter and it always says “not found” for the latitude and longitude. Why is this?

  75. Kango

    @ Mitch: I have the same problem as you. In case you have found by the meantime a solution, it would be great to share it with me (the_kango@hotmail.com)

    If someone else would know what to do, please feel free to help us :-)

    Thanks,

    Kango

  76. Mitch

    Kango,

    Yeah. I believe the link that’s going out from the excel spreadsheet is going to yahoo or bings latitude/longititude creation area that has been closed. I don’t think this excel spreadsheet will work unless theres a patch done or something with it is fixed. Which is a shame because we were so close to creating that .KML file which would have been the last step before plotting it on Google Earth without having to upload my business data onto the web (which I don’t feel comfortable doing). Now I will have to buy mapping software -$$$. I have scoured the internet for a program like this for hours and hours without any real luck. Please anyone let me know if you find anything that fixes this issue or if you can find a different method for doing this.

    Mitch

  77. Hello

    There are various capabilities here that might help
    http://ramblings.mcpher.com/Home/excelquirks/getmaps

    Someone previously asked about changing colors/pins etc.
    That’s is described here
    http://ramblings.mcpher.com/Home/excelquirks/getmaps/mapping/circles

    Finally here is a quick start on the various topics that may help to get going quickly.
    http://ramblings.mcpher.com/Home/excelquirks/snippets/steps

Leave a Comment

*
Please type the answer to the math equation. Click the pic to hear the numbers.
Click to hear an audio file of the anti-spam equation