Skip site navigation (1) Skip section navigation (2)

Re: Stream data into Postgres via Perl

From: Medi Montaseri <medi(dot)montaseri(at)intransa(dot)com>
To: Ashish Lahori <ALahori(at)india-today(dot)com>
Cc: "'Kevin Old'" <kold(at)carolina(dot)rr(dot)com>,"'beginners(at)perl(dot)org'" <beginners(at)perl(dot)org>,"'pgsql'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Stream data into Postgres via Perl
Date: 2002-10-30 18:35:59
Message-ID: 3DC0268F.6010204@intransa.com (view raw or flat)
Thread:
Lists: pgsql-general
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
>  
>




In response to

pgsql-general by date

Next:From: Tom LaneDate: 2002-10-30 18:39:26
Subject: Re: maximum number of triggers on a table?
Previous:From: Peter EisentrautDate: 2002-10-30 18:32:12
Subject: Re: move 0 behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group