Re: Importing data from csv - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy">I recently did this by parsing the data through a VB program that appended a “\”
infront of any Char(10) and/or Char(13) characters which tells Postgres to accept the next character as a literal part
ofthe column value I believe – must do because it worked! I also quoted the whole column as part of the VB
prog…</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">Worked for me but I’m not sure the exact science behind it so someone else might
beable to be of some more detailed help.</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">Cheers,</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy">-p</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal" style="margin-left:36.0pt"><font face="Tahoma"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:Tahoma">-----Original Message-----<br /><b><span
style="font-weight:bold">From:</span></b>pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
<b><spanstyle="font-weight:bold">On Behalf Of </span></b>Sumeet<br /><b><span style="font-weight:bold">Sent:</span></b>
</span></font><fontface="Tahoma" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:Tahoma">Friday, 25
August2006</span></font><font face="Tahoma" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:Tahoma">
</span></font><fontface="Tahoma" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Tahoma">00:48</span></font><fontface="Tahoma" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Tahoma"><br/><b><span style="font-weight:bold">To:</span></b>
pgsql-sql@postgresql.org<br/><b><span style="font-weight:bold">Subject:</span></b> [SQL] Importing data from
csv</span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt"> </span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt">Hi Folks,<br /><br /> sorry if this is a duplicate post, i've been tryin to find
asolution of importing data into postgres from a csv file. The problem is, I have a database which consists of columns
whichcontain 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
postedin one of the previous posts..<br clear="all" /><br /> #! /usr/bin/perl<br /> $inquotes = 0;<br /> while
(<>){<br/>      # Chop the crlf<br />      chop ($_);<br />      chop ($_);<br /><br />      # this first bit
goesthrough and replaces <br />      # all the commas that re not in  quotes with tildes<br />      for ($i=0 ; $i <
length($_); $i++){<br />          $char=substr($_,$i,1);<br />          if ($char eq '"' ){<br />             
$inquotes= not($inquotes); <br />          }else{<br />              if ( (!$inquotes) && ($char eq ",") ){<br
/>                 substr($_,$i,1)="~";<br />              }<br />          }<br />      }<br />      # this replaces
anyquotes<br />      s/"//g;<br />      print "$_\n";<br /> }<br /><br /><br /> cat data_file | perl scriptname.pl >
outputfile.dat<br/><br /> and when i run the copy command i get messages like data missing for xyz column.<br /> any
possiblehints....... <br /><br /> --<br /> Thanks,<br /> Sumeet </span></font></div><br
/><p><b>*******************Confidentialityand Privilege Notice*******************</b><p> The material contained in this
messageis privileged and confidential to the addressee. If you are not the addressee indicated in this message or
responsiblefor delivery of the message to such person, you may not copy or deliver this message to anyone, and you
shoulddestroy it and kindly notify the sender by reply email. <p> Information in this message that does not relate to
theofficial business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its
employees,contractors or associates shall not be liable for direct, indirect or consequential loss arising from
transmissionof this message or any attachments <br />