Author |
Message |
09/01/2009 02:23:07
|
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 ! |
|
|
09/01/2009 07:41:48
|
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
|
|
|
|
09/01/2009 18:21:08
|
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 ! |
|
|
09/01/2009 22:59:07
|
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
|
|
|
|
10/01/2009 03:36:40
|
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 |
Download
|
Filesize |
24 Kbytes
|
Downloaded: |
2655 time(s) |
|
;o) don't beleive all you read like it is true ! |
|
|
03/02/2009 13:11:21
|
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
|
|
|
04/02/2009 22:13:53
|
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
|
|
|
|
09/02/2009 11:56:18
|
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...'
|
|
|
11/02/2009 11:02:29
|
vaseem
Joined: 03/02/2009 13:04:13
Messages: 2
Offline
|
thank you i got the solution
vaseem
|
|
|
18/02/2009 22:23:18
|
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
|
|
|
|