X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 40A969FB1CD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 24 Aug 2006 11:48:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 81328-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 24 Aug 2006 14:48:00 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168])
	by postgresql.org (Postfix) with ESMTP id C02A19FA6B1
	for <pgsql-sql@postgresql.org>; Thu, 24 Aug 2006 11:47:59 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id m3so525190ugc
	for <pgsql-sql@postgresql.org>; Thu, 24 Aug 2006 07:47:58 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=fi6UtW3DKz/99diG0bTXBATi3PgCpECfZPYLn00u0Kuz4jrEQRRTUjt+X8XhtFIHuOMzleaJi9QY7n27/PK3vwxRgrEGsMZ0ags4+h0XppOjFkuMNp/fNv/REbK6Hqsoy8p7jmJnwihFIj3guD7TbPVnwyi/EWupP/g4CZBHK2A=
Received: by 10.66.244.10 with SMTP id r10mr1024525ugh;
	Thu, 24 Aug 2006 07:47:57 -0700 (PDT)
Received: by 10.66.248.13 with HTTP; Thu, 24 Aug 2006 07:47:57 -0700 (PDT)
Message-ID: <7539aebb0608240747j6da083b2i3e1f380124504955@mail.gmail.com>
Date: Thu, 24 Aug 2006 10:47:57 -0400
From: Sumeet <asumeet@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Importing data from csv
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_150061_14626263.1156430877718"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=4.313 tagged_above=0 required=5 tests=HTML_40_50,
	HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL
X-Spam-Level: ****
X-Archive-Number: 200608/178
X-Sequence-Number: 26136

------=_Part_150061_14626263.1156430877718
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Folks,

sorry if this is a duplicate post, i've been tryin to find a solution of
importing data into postgres from a csv file. The problem is, I have a
database which consists of columns which contain newline characters (mac and
unix). now when i export these files to a csv format, there are some line
breaks (mixed unix and mac) in the data which breaks the copy procedure.

I also tried using the script posted in one of the previous posts..

#! /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";
}


cat data_file | perl scriptname.pl > outputfile.dat

and when i run the copy command i get messages like data missing for xyz
column.
any possible hints.......

--
Thanks,
Sumeet

------=_Part_150061_14626263.1156430877718
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Folks,<br><br>sorry if this is a duplicate post, i've been tryin to find a solution of importing data into postgres from a csv file. The problem is, I have a database which consists of columns which contain newline characters (mac and unix). now when i export these files to a csv format, there are some line breaks (mixed unix and mac) in the data which breaks the copy procedure.
<br><br>I also tried using the script posted in one of the previous posts..<br clear="all"><br>#! /usr/bin/perl<br>$inquotes = 0;<br>while (&lt;&gt;){<br>&nbsp;&nbsp;&nbsp;&nbsp; # Chop the crlf<br>&nbsp;&nbsp;&nbsp;&nbsp; chop ($_);<br>&nbsp;&nbsp;&nbsp;&nbsp; chop ($_);<br><br>&nbsp;&nbsp;&nbsp;&nbsp; # this first bit goes through and replaces
<br>&nbsp;&nbsp;&nbsp;&nbsp; # all the commas that re not in&nbsp; quotes with tildes<br>&nbsp;&nbsp;&nbsp;&nbsp; for ($i=0 ; $i &lt; length($_) ; $i++){<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $char=substr($_,$i,1);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if ($char eq '&quot;' ){<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $inquotes = not($inquotes);
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }else{<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if ( (!$inquotes) &amp;&amp; ($char eq &quot;,&quot;) ){<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr($_,$i,1)=&quot;~&quot;;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp; # this replaces any quotes<br>
&nbsp;&nbsp;&nbsp;&nbsp; s/&quot;//g;<br>&nbsp;&nbsp;&nbsp;&nbsp; print &quot;$_\n&quot;;<br>}<br><br><br>cat data_file | perl scriptname.pl &gt; outputfile.dat<br><br>and when i run the copy command i get messages like data missing for xyz column.<br>any possible hints.......
<br><br>--<br>Thanks,<br>Sumeet

------=_Part_150061_14626263.1156430877718--