Re: Stream data into Postgres via Perl
От | Ashish Lahori |
---|---|
Тема | Re: Stream data into Postgres via Perl |
Дата | |
Msg-id | 3FA1445378F3D4119120000629A87BD8BC4894@ITGCOMSRV обсуждение исходный текст |
Ответ на | Stream data into Postgres via Perl (Kevin Old <kold@carolina.rr.com>) |
Список | pgsql-general |
Hi, I think the best way of doing the Insertion is to use the copy command of postgres. You then have to insert Then nextval manually. this can be done by replacing and '|' with ',' and save it as .CSV. Open the file in Excel and insert the intial row as the way you want, i mean the starting index value, save again as CSV. If you are using Linux OS, save file (.CSV) as unix fileformat and use the following command while you are in the postgres command prompt. \copy table_name from 'filename' using delimiters ',' with null as ''; Hope this will Help you. rgds Ashish Lahori -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kevin Old Sent: Tuesday, 29 October 2002 11:23 PM To: beginners@perl.org Cc: pgsql Subject: [GENERAL] Stream data into Postgres via Perl Hello all, I'm writing a script that will read pipe delimited data from a text file and insert various fields into a Postgres table. Below is some code I'm trying to optimize: while (<FHD>) { chomp; #removes \n chop; #removes trailing pipe @line = split(/\|/, $_, 502); #The line has 502 "fields" so #them into an array $dbh->do("INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),\'" . join("\',\'", $line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18], $lin e[22],$line[25]) . "\')"); $dbh->commit(); } #end while Just wondering if anyone has a better way of accessing the data in the array or of storing the few fields I need temporarily until it gets inserted into the database. There's a better way to do this, but I'm just not thinking right.....any suggestions are appreciated. Thanks, Kevin kold@carolina.rr.com ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
В списке pgsql-general по дате отправления: