I've been working with data that only has ZIP and ZIP+4 info so this new version is a huge help and works great! Much faster than batchgeocode.com.

Any way to add batch processing of data to get travel time and distace?? I would bow at your feet if this was possible in a similar fashion!!!!

Is there a way to support Canadian Postal codes? Yahoo can process them, but is there a way to change the tool to allow this?

Thanks!

Just the macro that spits out the kml from the spreadsheet is awesome.
Have you seen anything that could do the same thing from an MSaccess database?

For some reason the kml file is not opening automatically when excel spits it out.
I checked paths. Running GEbeta4. Output.kml will open fine when double clicked.

I guess placing the export button on the toolbar and then allowing user to select
ranges for the X,Y,label,and multiple attributes would make it all 100% perfect

Many many thanks!

[...] Excel Geocoding Tool v2 - Juice AnalyticsI m happy to announce a few small revisions to our Excel geocoding tool. The tool takes a list of addresses and will look up the latitude and longitude of those addresses. [...]

I keep getting an error saying:

Compile Error:
Can't find project or library

Do you have any advice?

Thanks. UD

hi

can this be used for other countries other than the USA , if it could be developed to do so it would be great

For some reason, this tool no longer works. That's a shame. I loved this tool. So fast and easy.

Great macros! Thanks.

What would need to be changed to make it possible to change the icon based on other data. For example, you could make republicans red and democrats blue.

Chris,

The http.send is preventing me from running the file. Could you tell me which Tools--> References to click to make this work ?

Works perfectly for me using a Yahoo API key. Thanks for a easy to use and great tool!

I'm in love with this! Sadly, I can only do 1,000 records at a time, then it bombs with a runtime error. I want to do 210,000 records.

Chirs,
Its seriously great work, I just cant tell how much its helping me. As Dawn said apart from the error, it just works great. I also want this to give be results for about a 100,000 records. Let me know if I can be of any help. I am working on my VBA Skills.

Chris,
I am probably doing something wrong but
I have clicked on the link above Geocoding Tool v3.1.xls which sends me to http://media.juiceanalytics.com/downloads/Geocoding%20Tool%20v3.1.xls
which just hangs.
Thanks in advance for your help.

Jay

Chris,

I have the same problem as Ujval Gandhi, the VBA bails on the http:send line. Any suggestions on how to solve the problem?

~ R. J.

I tried to sign up for a geocoder.us username/password and never got one. So I signed up for a yahoo! account at:
https://edit.yahoo.com/registration?.intl=us&new=1&.done=http%3A//mail.yahoo.com&.src=ym
and it worked great!
Thanks for this great resource!
Amanda

I'm not sure that the zip+4 is working. Shoud it show "zip+4" in the Precision field? When I compare a a zip+4 with just the same Zip i am getting the exact same XY coord and both precision fields say "zip". Please let me know if this is working correctly or what i can do to get Zip+4 working. Thank you.

Fantastic! Works well for UK and USA

Hello dear devoleper,

It seems a great program you wrote in excel. but is it possible to ad the field COUNTRY to the macro? than i can use it for the netherlands as well.
Now it is not working well for this country.

Thanks and i hope to hear from You.

Greetings

The link to the yahoo registration page for the geocoder seems to have moved (link broken). Not finding it on a quick google search. Can you post updated info?

I am not finding the spreadsheet to work using the provided sample data. I am getting all "address not found" results. Haven't modified the file other than adding in username and password, and selecting geocoder.us. Any ideas on how to debug? Hve internet connections, and hear the beep and see the status bar entry for the geocode address data.

Cool tool! -- I need to figure out how to adapt this for Access. Parsing the XML is my big stumbling block

Awesome Tool, So much faster than batchgeocode.com. Seems very accurate. i did 4,000 recodrs in one shot with no problems.

Thank you!!!

Great Tool!

I used this tool a while back, and it worked great!

However, I just tried to use it today, and it keeps bombing out on me. I am getting the following error:

"A connection with the server could not be established".

This is thrown in the mGeoCode Module, at the http.send command in the yahooAddressLookup Function.

Any ideas why this might be happening? I looked up the API documentation on Yahoo's website, and the URL path in the same function/module has changed from what is in the VBA code. Could that be the error? I tired to change it myself, but it still bombed out. Thanks!

Is it possible to include additional information in the KML output file to be displayed in various ways in Google Earth? For example, if I have information that details the income level of the head of household at each address, can that information somehow be included in the KML file so it can be color-coded in Google Earth? Or what if I have a flag that says a particular address is a "Friend" vs. "Business Partner" vs. "Family" for example. Can that information be included?

If so, how do I incorporate that information in the xls spreadsheet?

Thanks in advance for your help.
Kevin

Having the same connection issues. Seems to work fine as long as the address the querry address is well formed and findable, if the address cannot be found the script often errors out with "a connection could not be established"

EDIT: And now the script is working fine again. Strange. Seems odd that yahoo would be having server issues....

I try downloading the file and get errors:

Mod_python error: "PythonHandler django.core.handlers.modpython"

Traceback (most recent call last):

File "/usr/lib/python2.5/site-packages/mod_python/apache.py", line 299, in HandlerDispatch
result = object(req)

File "/usr/lib/python2.5/site-packages/django/core/handlers/modpython.py", line 177, in handler
return ModPythonHandler()(req)

File "/usr/lib/python2.5/site-packages/django/core/handlers/modpython.py", line 150, in __call__
response = self.get_response(request)

File "/usr/lib/python2.5/site-packages/django/core/handlers/base.py", line 59, in get_response
response = middleware_method(request)

File "/usr/lib/python2.5/site-packages/django/middleware/cache.py", line 55, in process_request
cache_key = get_cache_key(request, self.key_prefix)

File "/usr/lib/python2.5/site-packages/django/utils/cache.py", line 134, in get_cache_key
headerlist = cache.get(cache_key, None)

File "/usr/lib/python2.5/site-packages/django/core/cache/backends/memcached.py", line 19, in get
val = self._cache.get(key)

File "/usr/lib/python2.5/site-packages/python_memcached-1.40-py2.5.egg/memcache.py", line 619, in get
check_key(key)

File "/usr/lib/python2.5/site-packages/python_memcached-1.40-py2.5.egg/memcache.py", line 888, in check_key
raise Client.MemcachedKeyCharacterError, "Control characters not allowed"

MemcachedKeyCharacterError: Control characters not allowed

Same problem as Tanya above. Am I supposed to get a spreadsheet? Am I doing anything wrong?

Apparently I am not the only one with this problem. When I try to run the macro I am getting the message that the server name or address can not be resolved. How can I fix this?

I just tried to view my output.kml file and I'm getting a Google Earth error. Opening of file failed... parse error at line 11, column 20...not well-formed (invalid token). Is this due to the new version of Google Earth?

Thanks for this very usefull tool. I used the same idea to create my own Geocoding program in MS Access. I wrote an application that gets the lat, long and precision from both GOOGLE and YAHOO servers, determines which one is best precision, and even calculates the distance between them. I was wondering about the 5,000 daily limit. Lately I have been doing way more, about 30,000 - 40,000 a day with no problems. Does anyone know if they lifted the limit?

Nathan - Using the GeocodingTool v3.1.xls, I have been able to Geocode way more than the said 5,000 limit. So it would appear to me the limit has been lifted.

on a side note - does anyone know how to Geocode locations outside of North America?

I have been able to geocode 55K in one day with Yahoo. The API has been lifted in conjunction with Y! Search Boss.
http://developer.yahoo.com/search/boss/

Nathan - I am interested in your Access geocoding program. Do you have plans to make it available?

I'm italian and we use the decimal char = comma ",", so I have to edit the output.klm file and change the string
coordinates>12,90542,43,912535,0</coordinates> in
coordinates>12.90542,43.912535,0</coordinates> (change "," withh ".".
Is possible to change the program for European use?
Thanks very mutch.

All,

Can someone suggest a good geocoder that will also correct addresses and is in executable format ? I can't use API's.

Thanks,

Sal@khattak.com

Hello! very nice tools! Bravo!

We use it to map some of our point of sales. Plus, it is possible to change the icon and the color used allowing to display different POS in different way (red=bad, green=good, etc...).

I have the same problem then Mark. I lived in canada and our decimal separator is also a coma (65,0000). i have to manually change the KML file. however, i think I can manage modifying the macro by myself

I love the geocoding tool. Works great for addresses. Can this tool work for intersections? I have a list of motor vehicle accidents which only locational information is the intersections and need lat-long.

It does work with intersections, use "AND"
e.g., Main St AND North St

I get the "A connection with the server could not be established" error that people were getting awhile back. The error is the "http.send" line in the "mGeoCode" module. I get the error with Yahoo and geocoder.us no matter which one I have selected to use. (I have set up an account with both).

Jonathen - I am having the same issue. It appears this tool stopped working sometime within the past week or two.

Chris - any chance you can take a look at this?

Many thanks,
James

This is a GREAT TOOL.
I use it almost everyday to geocode properties. I am glad the guys at Juice took the time to develop this and save me countless hours... :-)

Very nice tool! Thank-you. Using Yahoo it also works for european countries provided you change the zip cleaning funtion [Function geocodecleanZip] in the code (Zipcodes in Belgium have 4 numbers, for exemple), and change "state" by "country" in the "URL =" line from the [Function yahooAddressLookup]in the mGeoCode Module. Some special character cleaning is also usefull. =New options for version 4?

if comma "," needs to be replaced by period "." you need to

1. open the Visual Basic Environment in Excel ( [Alt] + [F11] )
2. on the upper left corner expand the Tree to VBAproject (Geocoding) -> Module and double-click mKML
3. go to line 100 and insert
latitude = Replace(latitude, ",", ".")
longitude = Replace(longitude, ",", ".")
4. save and retry

Has anyone converted this to *.xlsx?

I was excited to find this tool, appears to be exactly what I need. Unfortunatley I can't seem to get it to work with either the supplied data examples or my data. After reading through these comments, I've run it with my firewall turned off ... still get the "not found" result. I'm using it with a Geocoder.us account.

Appreciate any thoughts or suggestions on how to overcome this problem.

Is there any chance that an option to use the Google geocoder can be added? The Yahoo! one almost never understands addresses given as street corners.

somehow i can't get color coded markers when i upload the file to google maps. i'd like to designate different levels of prospects or clients. please help!

I tried to follow the instructions and added the two lines of code to start using google for geocoding with google but where do I insert my google API key and where do I select google to geo code with google ?
I can really use help with this or if anyone already has the juice utility working with google, can you guys e-mail it to me please ?
Thank you in advance !
SAl

I tried to follow the instructions and added the two lines of code to start using google for geocoding with google but where do I insert my google API key and where do I select google to geo code with google ?
I can really use help with this or if anyone already has the juice utility working with google, can you guys e-mail it to me please ?
Thank you in advance !
SAl
sal@khattak.com

It stopped working;
Yahoo must have done something to their api or changed the syntax or something.
Dear Chris Gemignani, can something be done about that?
I have too little understanding of this matter to do something about it.

strangely enough http://www.batchgeocode.com/ still yahoo-geocodes my addresses. I just can't modify the kml-output and in my opinion it is too intricate for anyone in my office to use...

Recently, We have been able to geocode only around 350-360 address in the excel tool.. Is this a know problem?? Is there a way to bring the # back to 5K, Any repsonses would be greatly appreciated,
THanks!

If you can no longer geocode more then a few hundard address via yahoo. Please release / renew your IP address. You have to keep repeating this every minute.
Yahoo is treating constant requests as a DDOS attack I guess.

Does anyone have this utility working with GOOGLE YET ?

Please e-mail me:
sal@khattak.com

This is a great tool. Is there any way I can change it to work with Google Maps API?

Great tool! I'm modifying it for use in an Access database.
http://riceball.com/d/content/ms-access-geocoding-and-distance-reporting

I rec'd a error message in excel 2007 even with the dummy data, it reads: Microsoft Visual Basic - Run-time error '429' Active-x component can't create object

Hi, great tool. One problem I have though is I get a
Parse error at line 21. Not well formed (invalid token). Line 21 is where I have an api chart url for display as the point icon. The url works fine when I paste it into a browser or paste it directly into G Earth as a custom icon. However I can't add the kml which your tool creates. There are no blanks etc. Any help would be excellent,
ta,
Bernard

I'm looking to geocode all addresses by zip code or some other means. Is this possible?
One problem I have is that I have a list of address, but I know some are missing and address ranges don't help.
I also understand that the 2010 census is collecting lat/longs for all addresses collected. Any chance that these will be used in fucture geocoding applications?

hi there,
has anyone got a solution concerning the "http:send" line yet?
the macro always seems to stop at that point.
thanks for your help
marian

Hi Marian,

I'm running into the same problem and believe it's because I'm running the code while inside my company's firewall. if i'm at home and not connected to my company's network, everything runs beautifully. I'm currently investigating a workaround, and will post back here if I figure something out.....
Chris

hi folks,
in my case it doesn`t work at home either :( not sure whether the firewall is causing this problem. Chrispy, did you work it out yet? appreciate any help on this.
cheers
marian

Hi All,
I had the same problem at the company's network. I just unplugged the cable and went to the aircard and it worked. So it must be something related to firewalls.
Cheers
Fillipe

Hi,
When I use the latest geocoder tool (v3.1.xls) and click view in google earth, I get a message that says "could not open file documents output.kml" - how do I fix this? I don't want to have to click "ok" then navigate to the file and double-click it (which will work but is not ideal).

Many thanks.

Fantastic tool! I modified the code to accept Canadian postal codes. Very comprehensive programming - completely impressed for a free tool like this.