Maxmind worldcitiespop.txt in SQL 2012 format
Account Home | Help | Blog | Contact us | Log Out


Welcome to Kbytes > Articles

Maxmind worldcitiespop.txt in SQL 2012 format

Posted By: siteadmin on 25/09/2012 10:58:00

Maxmind the IP Geolocation people also do a massive 3 million plus row text file called worldcitiespop.txt which has all the world's towns, even down to the smallest hamlet, and some of the larger ones populations.

This is very useful of course for some internet applications.   The size of the file can present some problems however.

http://www.maxmind.com/download/worldcities/  is where you download it from.

I tried to import it directly into MS SQL 2012 via their  SQL Server Import and Export Wizard, but it hit some rows that the default length is insufficient.

I increase the row length for the text columns and it went in fine!

If you are having trouble I have a zipped version in SQL 2012 format which I'll send to you for a modest sum of GBP 20 payable via Paypal!  :)

Once your done importing you will need these two links to decode the rest of the file:

http://dev.maxmind.com/static/maxmind-region-codes.csv

http://www.iso.org/iso/home/standards/country_codes/country_names_and_code_elements_txt.htm

Thanks for Maxmind for making available such a useful product!

Here is my import log from SQL 2012 Import Wizard:

 

The execution was successful
 
- Initializing Data Flow Task (Success)
 
- Initializing Connections (Success)
 
- Setting SQL Command (Success)
 
- Setting Source Connection (Success)
 
- Setting Destination Connection (Success)
 
- Validating (Success)
 
- Prepare for Execute (Success)
 
- Pre-execute (Success)
Messages
Information 0x402090dc: Data Flow Task 1: The processing of file "C:\Users\Administrator\Documents\worldcitiespop.txt" has started.
 (SQL Server Import and Export Wizard)
 
 
- Executing (Success)
 
- Copying to [dbo].[worldcitiespop] (Success)
3173958 rows transferred 
 
Messages
Information 0x402090de: Data Flow Task 1: The total number of data rows processed for file "C:\Users\Administrator\Documents\worldcitiespop.txt" is 3173959.
 (SQL Server Import and Export Wizard)
 
Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination - worldcitiespop" has started.
 (SQL Server Import and Export Wizard)
 
Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "Destination - worldcitiespop" has ended.
 (SQL Server Import and Export Wizard)
 
 
- Post-execute (Success)
Messages
Information 0x402090dd: Data Flow Task 1: The processing of file "C:\Users\Administrator\Documents\worldcitiespop.txt" has ended.
 (SQL Server Import and Export Wizard)
 
Information 0x4004300b: Data Flow Task 1: "Destination - worldcitiespop" wrote 3173958 rows.
 (SQL Server Import and Export Wizard)
 
 
 

blog comments powered by Disqus

Kbytes Home | Privacy Policy | Contact us | Testing Area

© 2004 - 2018 1 Oak Hill Grove Surbiton Surrey KT6 6DS Phone: +44(020)82617371