• Dave Foord
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,624 other followers

  • Dave Foords Twitter

  • Advertisements

Quickly creating a Google Map from a Spreadsheet of data

I think that Google Maps are a really useful tool, and everytime that I need to visit somewhere new, the first thing I do is put the postcode into Google Maps, and save the location. This way if I visit that town or city again, but to a different location I can see where the new location is in relation to the previous one, which helps me with my navigating. I recognise that many will use their in car sat nav systems, but because I mainly use the train I still use paper maps for these purposes.

One thing though that I have found hard in the past, is uploading lots of points to a map – which done manually takes an age, so I was relieved when I found a technique that does this for me.

We start of by using http://www.batchgeocode.com/ which looks a bit scary on first inspection, but is OK once you have used it a few times. Here are the steps to follow

  1. Create a spreadsheet with your table of information in – it can have whatever you want in there, as long as the postcode is in its own column, and the top row are column headings
  2. Copy the contents of your table (highlight and Ctrl-C) – including the column headings – Go back to http://www.batchgeocode.com/ and paste into the ‘table’ in step 2
  3. Validate the code by clicking the button
  4. In step 4 you tell it, what information to include and where – so for example next to ‘PostCode’  choose the post code column from your table – and for ‘Title’ Choose whatever you want the point on the map to be called. It may take a few attempts to get these settings right, but after a few goes makes sense.
  5. Run the geocoder by pressing the button – this will create a table similar to above, but with 2 extra columns giving longitudinal and latitudinal GPS co-ordinates
  6. At the bottom of Step 6, you should see your map, and below it a button to ‘Download to Google Earth (KML) file’ – click on this
  7. Save the KML file somewhere (e.g. desktop)
  8. Go to Google Maps
  9. if you don’t have a Google Account you may need to create 1, if you already have one, then go to My Maps
  10. Create a New Map
  11. Choose the mport option at the top left of the screen
  12. Browse to find the KML file that you found earlier
  13. And as if by magic, your map appears.

This I find very useful.


40 Responses

  1. hello
    i have made a map. It looks great.

    however what i would really like to do is save it to my maps in google maps (not google earth)
    then i can overlay the tube map and the universities.

    currently i can host the map on your website – but the URL wont open in google maps. If i save the file – this wont open on google maps either

    please can you help??


    • Hi Karen. I am glad that you liked the blog posting. I will point out that the system is not something that I can claim any credit for, I just found it.

      When you follow the process through, you should get the opportunity to export the output to Google Earth – this will generate a KML file which you save somewhere (e.g. your desktop). If you then go to Google Maps and create a map as normal (e.g. give it a name) you can then use the import button. Browse for the saved KML file and upload it, and hey presto – you have a google map. By default all the pins will be black and white circles, but you can edit them individually if you want to.

      I have done this many times and works well.

  2. […] and in the process other people may benefit from my posting as well, for example my posting on https://davefoord.wordpress.com/2009/09/07/quickly-creating-a-google-map-from-a-spreadsheet-of-data/ I wrote primarily as a set of instructions to myself (using Word in fact) I then looked at it and […]

  3. Dave,
    The idea of doing this is great. I am providing staff development for teachers in New York City using google maps. However, that link doesn’t work. Is there somewhere else to go?

    • I have had a look, and at first when I click on the link it looks like it won’t work, but then it does work? So please try again and let me know if still not working.

  4. Very helpful post. Thanks! However, when I imported the KML file into Google Maps it looks very distorted. The map viewed on the batchgeocode website looks perfect. Is this a problem for anyone else? I can’t seem to find a solution.

    Thanks for any help.

    • I don’t know about this one, it has always worked for me. Can I suggest that you try this with just a few points of data and see if it works? Then try again with say half the points, and build up that way. it could be you 1 bad row of data somehow which is messing up the output.

      Do any of your entries have a comma in it (e.g. part of the address) that could possibly effect things.

      • Thanks for the super fast response! I tried it with just 6 rows of data (no commas in address) and still the same distortion. Oh well, it looks great at the batchgeocode website and it’s still very useful. So that’s ok. Thanks again.

  5. […] Quickly creating a Google Map from a Spreadsheet of data « Dave Foord’s Weblog (tags: tutorials maps) […]

  6. […] to create a kml file which will plot the points for the birthing centres onto the map (click here to read an earlier blog post on […]

  7. […] have in the past blogged a couple of times about Google Maps – for example https://davefoord.wordpress.com/2009/09/07/quickly-creating-a-google-map-from-a-spreadsheet-of-data/ gives instructions on how to create a Google Map from a Spreadsheet of data, and […]

  8. […] I’m grateful to David Foord who included Batchgeo in a tutorial on exporting a spreadsheet of addresses to Google maps. […]

  9. It’s unbelievable! Thanks a lot! It’s still working perfect! Uploaded 400 businesses (I had a spreadsheet), made different categories, inserted phones, e-mails, web and post addresses and made a map for google for 15 mins. It can’t be true:)

  10. I cannot thank you enough! You have saved me numerous hours of sweat and tears every month. I’m so happy right now 🙂

  11. This is brilliant – have been trying to do this for ages. Saved me a HUGE amount of time. Just one thing – can you delete the map from batchgeo.com if you then decide you want to change it – or is it not worth worrying about?

  12. Thank you very much, just made the map, was/is incredible. My colleaguue spent the afternoon 3 months ago entering in hundreds of addresses and I was able to make a bigger map in…3 minutes. Thanks for the great internet tips!

  13. Oh kittens yes. This is the exact reason I was born.

    To find you, my long lost knowledge seeking soul-mate.

  14. This is GOLD, you read me. GOLD!
    I’ve been looking for a tool like this for a while now, and it’s the first that works fine at once, and is pretty and easy to use too!

  15. i cannot download the file after step 6, do you have to have the pro version?

    • I don’t have a pro version, once you have created map, view it, then scroll down to bottom of page – you should see a link that says ‘Download temp google Earth (KML)’ this is what you need.

      • Hi Dave, this all sounds wonderful, I have wanted to create a customer map for some time. Can it be done with the Uk network / post codes as well?

      • Hi, glad you find this useful – yes it works in uk – that is where I am based and have only ever used it here.

  16. Hi Dave
    I have a large number of maps of cities in the uk which I want to overlay with the postcodes. These maps are all scanned and we have them as jpegs on our computer. Being a bit of a computer numpty whereas you obviously aren’t can you advise on how I could possibly achieve this? Thanks

    • Hi – I am not sure I fully understand your request. My first question would be do you have the right to copy the maps, or are you breaking copyright by scanning them – if you are breaking copyright then i don’t want to offer any suggestions.

      If you do have the right to copy them – what do you want to overlay? Is it an area showing each postcode, or a single pin for each postcode at the centre of each postcode?

  17. Many thanks to you Davefoord for the article. The is great piece of information and it really worked for me. I had to upload few hundreds and this did the trick for me.

    However is there any way I can convert up-to or more than 1000 postcodes at a time for free.

    • Hi. If you are transferring that sort of number I think you should be paying for the service. BatchGeo has to make its money somehow, to keep running the service.

  18. As of my attempt, I could not find how to access the KMZ file or the lats and longs. I found that Google Maps Lite gave me this functionality within my Google Maps account.

  19. […] Quickly creating a Google Map from a Spreadsheet of data – September 2009 […]

  20. Many thanks. Your guidance was most useful and will save a lot of time for us.

  21. I just wanted to point out an useful and free software platform for converting Google Earth kml file to gpx format, when needed – kml2gpx.com .It doesn’t require installation, just upload your files and see the results. Thought it might be useful.

  22. Great! Do you know if Is there a less expensive version than Pro? I don’t need all the user accesses – just me. I haven’t got the monthly fee they need and have about 2000 postcodes I need to make a map for …

    • BatchGeo is a company that needs to make money to survive. They offer a certain level for free, and then charge for the more advanced features. Personally I am finding what I can do for free as being sufficient for my needs, and from your description is sounds like it should be OK for you. There are other tools out there, but I haven’t looked at them – so cannot comment in terms of cost or how they work, so sorry that I cannot be more help. One option for you is to use BatchGeo, then export the xml file, you can then import this into Google maps and do certain things in Google Maps. The downside is that as your data set changes you have to repeat this process each time which is time consuming. On Thu 21/05/15 23:38 , Dave Foord’s Weblog comment-reply@wordpress.com sent: a:hover { color: red; } a { text-decoration: none; color: #0088cc; } a.primaryactionlink:link, a.primaryactionlink:visited { background-color: #2585B2; color: #fff; } a.primaryactionlink:hover, a.primaryactionlink:active { background-color: #11729E !important; color: #fff !important; } /* @media only screen and (max-device-width: 480px) { .post { min-width: 700px !important; } } */ WordPress.com

  23. This is so easy but I wondered if it can be set up so that on permanent view next to the location pin, you could see a word that would identify which location it was (without having to click the pin)? Is this possible within batchgeo, or do you know of something where I can put in a list and get the points showing along with a label??

    • That is a very good question, and I don’t know if you can – it isn’t something I have ever done. I wonder if any of the other followers of my blog know the answer.

  24. This is brilliant! thank you 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: