Re: Stream data into Postgres via Perl

Why you say using Excel is better, note how you just changed a batch
program that
could potentially run at 3:00 AM into an interactive operator needed
task ....

My vote is, stay with the automation, its hard at the begining but pays
later...

Ashish Lahori wrote:

>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(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Kevin Old
>Sent: Tuesday, 29 October 2002 11:23 PM
>To: beginners(at)perl(dot)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(at)carolina(dot)rr(dot)com
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>