Thread: convert text file to database
Readers, I re-formatted an extract from my file of chess matches to: Event,Online Game,Site,www.flyordie.com,Date,yyyy/mm/dd,Round,1,White,djride,Black,kasparov777,Result,1-0,Termination,normal,UTCDate,2008/01/05,TimeControl,1200,moves,1. c4 e5...25. Qh7# 1-0 Event,Online Game,Site,www.flyordie.com,Date,2008/01/05,Round,1,White,pawnsrow,Black,djride,Result,0-1,Termination,normal,UTCDate,yyyy/mm/dd,TimeControl,1200,moves,1. d4 d5...33. Bf3 Rxe5 0-1 I tried to import the csv file using the command: chessmatches=# COPY chessgames FROM '/absolute/path/filename.csv'; ERROR: invalid input syntax for integer: "Event..." CONTEXT: COPY chessgames, line 1, column id: "Event,..." My first thoughts are that I need to create a first column entitle 'column id' but there is no reference to the requirement for this in the section of the manual COPY. SO what is required please? Thanks,
e-letter <inpost@gmail.com> writes: > I tried to import the csv file using the command: > chessmatches=# COPY chessgames FROM '/absolute/path/filename.csv'; > ERROR: invalid input syntax for integer: "Event..." Umm ... you didn't tell it the data is in CSV. I think you need at least the HEADER option too. See the COPY manual page: http://www.postgresql.org/docs/8.3/static/sql-copy.html regards, tom lane
I amended a copy of my csv files as follows: 2,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1200,1d4d5...33Bf3Rxe50-1 3,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1,player1,player2,1200,1e4d52e5d4...Nxf2#0-1 Compared to my original csv file, I had to remove the field names that were in the csv file (e.g. event,onlinegame,site,www...,date,yyyy.... I also had to remove all full stop (.) characters, such as in the url name and within the list of moves (e.g. 1. e4 d7 2. gf3...). Instead of removing the full stops, could I have prefixed the character with the backstroke (\), e.g. to become www\.flyordie\.com? The user guide says so but when I tried the csv file import failed. Moreover, I had to add an id number to represent the id primary key, as the first column of data. I thought there would be a way to automatically add this column to the csv file?
i think you want convert text file to database. You can use COPY
command and you can add a column whose value is id number such as
1,2,3....you can set the column is series type or you can first create
a sequence and set default value of new column be sequence .
SQL is :
1. ALTER TABLE xxxxx
ADD COLUMN id series;
2.
CREATE SEQUENCE public. mile_danxianhe_gid_seq
INCREMENT 1 MINVALUE 1
MAXVALUE 9223372036854775807 START 1
CACHE 1;
ALTER TABLE public.yantian_2006_mile
ADD COLUMN id integer DEFAULT nextval('mile_danxianhe_gid_seq'::regclass)；
wish you can understand my explain.
best regard.
windwxc
----- Original Message -----
From: e-letter <inpost@gmail.com>
To: pgsql-novice <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] convert text file to database
Date: 08-04-21 22:52:40
I amended a copy of my csv files as follows
2,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1200,1d4d5...33Bf3Rxe50-1
3,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1,player1,player2,1200,1e4d52e5d4...Nxf2#0-1
Compared to my original csv file, I had to remove the field names that
were in the csv file (e.g. event,onlinegame,site,www...,date,yyyy....
I also had to remove all full stop (.) characters, such as in the url
name and within the list of moves (e.g. 1. e4 d7 2. gf3...). Instead
of removing the full stops, could I have prefixed the character with
the backstroke (\), e.g. to become www\.flyordie\.com? The user guide
says so but when I tried the csv file import failed.
Moreover, I had to add an id number to represent the id primary key,
as the first column of data. I thought there would be a way to
automatically add this column to the csv file?
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
-------------------------------------------------------------------
想玩得精彩？快来惠普"我的电脑 我的舞台"(
http://d1.sina.com.cn/sina/limeng3/mail_zhuiyu/2008/mail_zhuiyu_20080421.html
)
-------------------------------------------------------------------
注册新浪2G免费邮箱（http://mail.sina.com.cn/）
> I amended a copy of my csv files as follows: > 2,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1200,1d4d5...33Bf3Rxe50-1 > 3,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1,player1,player2,1200,1e4d52e5d4...Nxf2#0-1 > Compared to my original csv file, I had to remove the field names that... You can insert the full url with no special formating needed if you respect the csv standard: it says strings should be enclosed in pairs of double quote characters. So if you change 2,OnlineGame,wwwflyordiecom,yyyy/mm/dd,1,player1,player2,0-1,normal,yyyy/mm/dd,1200,1d4d5...33Bf3Rxe50-1 to 2,"OnlineGame","http://www.flyordie.com/","yyyy/mm/dd",1,"player1","player2","0-1","normal","yyyy/mm/dd",1200,"1d4d5...33Bf3Rxe50-1" it will work. You can use other characters than double quote (") for string delimiters; in that case you should use the QUOTE option of the CSV option to specify the new string delimiter. If a string delimiter exists in the string, it can be "escaped" by doubling it: x said "Go away" could be written as "x said ""Go away""" -- Szentpáli János -------------------------------------------------- easynet.ro - Best free webmail service hosted by Idilis ......................................................... Idilis - Internet Provider :: www.idilis.net Inchiriem conexiuni radio si in zone neracordate la coloana - Broadband Wireless Idilis -