A few post have been made with complex solutions for importing information from GeoNames.org into SQL Server. These involved downloading converters to change the file from UTF-8 encoding and downloading additional software (EditPad Pro) to complete the conversion to UTF-16.
A much simpler way is to open the txt file in Microsoft Excel. Let Excel perform it’s magic on the tab delimited file.
Add the headers to the top row:
The main 'geoname' table has the following fields : --------------------------------------------------- geonameid : integer id of record in geonames database name : name of geographical point (utf8) varchar(200) asciiname : name of geographical point in plain ascii characters, varchar(200) alternatenames : alternatenames, comma separated varchar(5000) latitude : latitude in decimal degrees (wgs84) longitude : longitude in decimal degrees (wgs84) feature class : see http://www.geonames.org/export/codes.html, char(1) feature code : see http://www.geonames.org/export/codes.html, varchar(10) country code : ISO-3166 2-letter country code, 2 characters cc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters admin1 code : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20) admin2 code : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) admin3 code : code for third level administrative division, varchar(20) admin4 code : code for fourth level administrative division, varchar(20) population : bigint (4 byte int) elevation : in meters, integer gtopo30 : average elevation of 30'x30' (ca 900mx900m) area in meters, integer timezone : the timezone id (see file timeZone.txt) modification date : date of last modification in yyyy-MM-dd format Then save the Excel file. Use SQL Server import to import the Excel file and your done.
That was the first thing I tried, but Excel only loads the first ~1 million rows.
allCountries.txt has ~7.5 million entries, so you will need those other tools/scripts (unless you want to import the individual country files).
Yes, unfortunatly I did end up importing the countries that I needed. I too ran into the limitations you are talking about. It still a good solution if you are only importing one country.