Received: from bush.rutgersinsurance.com (rutgersinsurance.com [38.138.71.194]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0ILVD011722 for ; Thu, 18 Jan 2001 16:31:13 -0500 (EST) (envelope-from aalang@rutgersinsurance.com) Received: from 6014cwpza006 ([10.10.10.51]) by bush.rutgersinsurance.com (8.9.3/8.9.3) with SMTP id QAA29803 for ; Thu, 18 Jan 2001 16:35:00 -0500 Message-ID: <020401c08196$40c677c0$330a0a0a@6014cwpza006> From: "Adam Lang" Cc: References: <3A67568B.59572A40@wgcr.org> Subject: Re: imorting csv files into postgres Date: Thu, 18 Jan 2001 16:33:09 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200101/206 X-Sequence-Number: 824 Yeah, but he doesn't want to have to create the table before hand, he wants postgresql to do all the work. ;) The only way that I know of to do that is write a program and feed it the CSV file... but in the program it is going to need to create the table. Sometimes you just have to do the work yourself. :) Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Lamar Owen" To: "mike" Cc: Sent: Thursday, January 18, 2001 3:48 PM Subject: Re: [INTERFACES] imorting csv files into postgres > mike wrote: > > > Does anyone know if you can import csv text files into postgres > > A perl script to do that was just posted to the GENERAL List.... > Hmmmm.... > > Tim Uckun just posted the following: > ------- > Here is your steps. > 1) Create your destination table in postgres > 2) Export your data to a comma delimeted format > 3) save this perl script to a file > > ------ > #! /usr/bin/perl > $inquotes = 0; > while (<>){ > # Chop the crlf > chop ($_); > chop ($_); > > # this first bit goes through and replaces > # all the commas that re not in quotes with tildes > for ($i=0 ; $i < length($_) ; $i++){ > $char=substr($_,$i,1); > if ($char eq '"' ){ > $inquotes = not($inquotes); > }else{ > if ( (!$inquotes) && ($char eq ",") ){ > substr($_,$i,1)="~"; > } > } > } > # this replaces any quotes > s/"//g; > print "$_\n"; > } > ----- > > 4) type this cat myfile.txt | perl myprog.pl > outfile.dat > 5) go to psql > 6) COPY "tablename" FROM '/path/to/outfile.dat' USING DELIMITERS '~'; > ----- > > See if that helps any. > -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11