GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
import countyInfo.txt postgresql  XML
Forum Index -> General
Author Message
tonton



Joined: 09/01/2009 02:04:06
Messages: 3
Offline

newbee asking maybe

i use some post here and found i am not the only one in trouble to importe this file in the table

some change the table
some change the script or the file

but into some line there something like that :

##^(?:AD)*(\d{3})$

wich not like by psql

i remove line starting with # for comments

but what means this ##^(?:AD)*(\d{3})$ is it to be interpreted by some database functions ?
did we need to replace by something before import to countryInfo table

thanks for your works sure and you help ...

tonton

;o) don't beleive all you read like it is true !
marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

These are two columns describing how the postal codes in the country look like. Delete them if you don't like them.

Marc

[WWW]
tonton



Joined: 09/01/2009 02:04:06
Messages: 3
Offline

high marc thanks for your answer

i'am french and not sure my translate

i just search to understand how it is made and what it's mean !

it is something like having a fish and knowing phishing

so how to import that data into countryinfo tables

cause psql hanging with data starting with ##

what i have to delete for correct import
with table creat with the faq

http://forum.geonames.org/gforum/posts/list/67.page

copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,languages,currency,geonameId) from 'countryInfo.txt' null as ''; not match cause
##^(?:AD)*(\d{3})$

does i delete a column
just ## and then ### #### so on
all things between # and $
i right quote in file for beeing accept data



;o) don't beleive all you read like it is true !
marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

The faq entry is refering to a previous version of the file.
The file is tab separated, to remove columns you can use a lot of tools, excel for instance.

Marc

[WWW]
tonton



Joined: 09/01/2009 02:04:06
Messages: 3
Offline

OK it's made thanks

i create a table in my database
Code:
  CREATE TABLE "countryinfo" (
       iso_alpha2 char(2),
       iso_alpha3 char(3),
       iso_numeric integer,
       fips_code character varying(3),
       country character varying(200),
       capital character varying(200),
       areainsqkm double precision,
       population integer,
       continent char(2),
       tld CHAR(10),
       currency_code char(3),
       currency_name CHAR(15),
       phone character varying(20),
       languages character varying(200),
       geonameId int,
       neighbours character varying(50),
       equivalent_fips_code character varying(3)
  );
 

I change file countryInfo.txt in the file join 1countryInfo.txt

and use Code:
 
  copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,country,capital,areainsqkm,population,continent,tld,currency_code,currency_name,phone,languages,geonameid,neighbours,equivalent_fips_code)from '1countryInfo.txt' null as '';
 

result : COPY 247

tonton
 Description remove postal column and comment [Disk] Download
 Filesize 24 Kbytes
 Downloaded:  2652 time(s)


;o) don't beleive all you read like it is true !
vaseem



Joined: 03/02/2009 13:04:13
Messages: 2
Offline

hi ,
i am importing the alcountries.txt into mysql using gui sql tool, i used the command load

LOAD DATA INFILE'C:\Documents and Settings\kim\Desktop\king-test-thu\geonames-66000-test.txt' INTO TABLE geonames.geoname (geonameid,name,asciiname,alternativenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo,timezone,moddate);

i am getting errors as table not found File 'Cocuments and SettingskimDesktopking-test-thugeonames-66000-test.txt' not found (Errcode: 2)

thanks in advance
khan
marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

could be a problem with the backslash. Did you trying putting two of them?

Marc

[WWW]
francisoud



Joined: 09/02/2009 11:54:04
Messages: 2
Offline

vaseem you should try to use simple slash with 'C:/Documents and Settings/kim...' or double backslash 'C:\\Documents and Settings\\kim...'
vaseem



Joined: 03/02/2009 13:04:13
Messages: 2
Offline

thank you i got the solution

vaseem
bwakkie



Joined: 25/11/2008 12:49:52
Messages: 7
Offline

nice, fixed it in my script too:
http://forum.geonames.org/gforum/posts/list/926.page#5449

cheers,
Bastiaan
 
Forum Index -> General
Go to:   
Powered by JForum 2.1.5 © JForum Team