Start by downloading the GeoIP City database from MaxMind. They have a free version that you can download here. Download the one in binary format, and uncompress it.
Next, you need the library to access this database format. There's a pure Python library called pygeoip that you can download from google code. To install it, just uncompress it and run the installer: sudo python setup.py install
Next, you need to build a small script to convert the IP addresses to countries. Here's the script I used (note that the countries database should be in the same directory as the script).
#!/usr/bin/env python import pygeoip, sys gi = pygeoip.GeoIP('GeoLiteCity.dat') for line in sys.stdin: rec = gi.record_by_addr(line) print rec['country_name']
I used this script (geo.py) by copying the IP list from excel to a plain text file (ips.txt), where you get one address per line. Then just run it with something like python geo.py < ips.text and you get a list of countries on your terminal window. Copy/paste to excel and you're done!
If you want more than just the country, just play a bit with the print line. Here's a variation I did to get the state and the city. The output is tab separated so that you can copy it easily to excel:
#!/usr/bin/env python import pygeoip, sys gi = pygeoip.GeoIP('GeoLiteCity.dat') for line in sys.stdin: rec = gi.record_by_addr(line) print rec['country_name'] + '\t', if rec['country_code'] == 'US' and 'region_name' in rec: print rec['region_name'] + '\t' + rec['city'] else: print '-' + '\t' + rec['city']
As a side note, I tried another database from hostip.info, but it was only able to convert about half of the IPs I threw at it, so I recommend going with the one from MaxMind...