Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D74829FA184 for ; Tue, 28 Aug 2007 15:03:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 85983-02 for ; Tue, 28 Aug 2007 15:02:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id E37169FA224 for ; Tue, 28 Aug 2007 15:02:58 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so1463783nfh for ; Tue, 28 Aug 2007 11:02:57 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=tjqBWnIM5ENxsU3T0csX+P/5RtXDWfPrhzWKSAJT00uHsOVY16vmczKaTJ2b40FQDXxqW84AeDCWb+38omrL7qvS/pAsU1CecWEmLmuZLrDu6x1SSxuRoasY3HfCPTw0EkCsKw6KSm2oTiOaOMLA2l7JtB02o1JvVJIG5NthD7E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=YL3DfvSjyJB525j8HounndOEFGFYG/pOuYIgB4SPDsPh+2t3z2iX3BLkcJIbAIwq1EsmrM5nJ9SPWmHGUYd5x6z4PA+xkdRl//Xr+8zj/fY5ysfPl/WUv2AT352MWs9i2YMXY2VLyOHjDt5lT6Bh0ZJ+m7s1ByHJCVS75Bt4T68= Received: by 10.78.195.9 with SMTP id s9mr5082861huf.1188324176380; Tue, 28 Aug 2007 11:02:56 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Tue, 28 Aug 2007 11:02:56 -0700 (PDT) Message-ID: <1d219a6f0708281102g7d42c0e9od3b02787fba173fe@mail.gmail.com> Date: Tue, 28 Aug 2007 14:02:56 -0400 From: "Chris Hoover" To: "Medi Montaseri" Subject: Re: How to import CSV file? Cc: "pgsql-admin@postgresql.org Admin" In-Reply-To: <8078a1730708281003n30db4b2eu40f4865672ebab76@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_85802_19949151.1188324176184" References: <1d219a6f0708280838q1312632fg7cbd971cec7cf5ee@mail.gmail.com> <8078a1730708281003n30db4b2eu40f4865672ebab76@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/352 X-Sequence-Number: 26683 ------=_Part_85802_19949151.1188324176184 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Why go through all of this when COPY can handle this for you? All I need to know is the syntax to tell copy that the strings are quoted by a single quote. Thanks anyway. On 8/28/07, Medi Montaseri wrote: > > This might not be the quickest way, but it is a skill you'll be happy to > have gained. > > Perl has a class (or module) called CSV.pm, you feed it a file, and it > does the rest (parsing it) and gives an array for each row. You can then > alter them (however you wish) and write them to a file suitable for pg copy, > or just use DBI to insert it into the PG. Which is basically an ETL > (Extract, Transform, Load). I know insert is slower, but the point was to > show a general way that will always for. > > Cheers > Medi Montaseri > > On 8/28/07, Chris Hoover wrote: > > > > I need some help importing a csv file. I have been given a csv file > > full of data that is delimited by a comma, and the strings are delimited by > > a single quote. How do you write the copy statement to use a single quote > > delimiter? I have tried several things, but so far, no joy. > > > > Thanks for any help, > > > > Chris > > > > ------=_Part_85802_19949151.1188324176184 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Why go through all of this when COPY can handle this for you?  All I need to know is the syntax to tell copy that the strings are quoted by a single quote.

Thanks anyway.

On 8/28/07, Medi Montaseri <montaseri@gmail.com> wrote:
This might not be the quickest way, but it is a skill you'll be happy to have gained.

Perl has a class (or module) called CSV.pm, you feed it a file, and it does the rest (parsing it) and gives an array for each row. You can then alter them (however you wish) and write them to a file suitable for pg copy, or just use DBI to insert it into the PG. Which is basically an ETL (Extract, Transform, Load). I know insert is slower, but the point was to show a general way that will always for.

Cheers
Medi Montaseri


On 8/28/07, Chris Hoover < revoohc@gmail.com> wrote:
I need some help importing a csv file.  I have been given a csv file full of data that is delimited by a comma, and the strings are delimited by a single quote.  How do you write the copy statement to use a single quote delimiter?  I have tried several things, but so far, no joy.

Thanks for any help,

Chris


------=_Part_85802_19949151.1188324176184--