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

A newline in CSV parlance denotes the end of a record....unless that newline is contained with quotes...<br /><br />
PhillipSmith wrote: <blockquote cite="mid004a01c6c7d3$60de8320$9b0014ac@ITPhil" type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions
*/p.MsoNormal,li.MsoNormal, div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman";}
a:link, span.MsoHyperlink{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{color:purple;text-decoration:underline;}
span.EmailStyle17{font-family:Arial;color:navy;}
@page Section1{size:595.3pt 841.9pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10pt;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: 10pt;
font-family:Arial; color: navy;"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial; color: navy;">Worked for me but I’m not sure the exact science behind it so
someoneelse might be able to be of some more detailed help.</span></font><p class="MsoNormal"><font color="navy"
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color:
navy;">Cheers,</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;">-p</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial; color: navy;"> </span></font><p class="MsoNormal" style="margin-left:
36pt;"><fontface="Tahoma" size="2"><span lang="EN-US" style="font-size: 10pt; font-family: Tahoma;">-----Original
Message-----<br/><b><span style="font-weight: bold;">From:</span></b> <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql-owner@postgresql.org">pgsql-sql-owner@postgresql.org</a>[<a class="moz-txt-link-freetext"
href="mailto:pgsql-sql-owner@postgresql.org">mailto:pgsql-sql-owner@postgresql.org</a>]<b><span style="font-weight:
bold;">OnBehalf Of </span></b>Sumeet<br /><b><span style="font-weight: bold;">Sent:</span></b> </span></font><font
face="Tahoma"size="2"><span lang="EN-US" style="font-size: 10pt; font-family: Tahoma;">Friday, 25 August
2006</span></font><fontface="Tahoma" size="2"><span lang="EN-US" style="font-size: 10pt; font-family: Tahoma;">
</span></font><fontface="Tahoma" size="2"><span lang="EN-US" style="font-size: 10pt; font-family:
Tahoma;">00:48</span></font><fontface="Tahoma" size="2"><span lang="EN-US" style="font-size: 10pt; font-family:
Tahoma;"><br/><b><span style="font-weight: bold;">To:</span></b> <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br/><b><span style="font-weight:
bold;">Subject:</span></b>[SQL] Importing data from csv</span></font><p class="MsoNormal" style="margin-left:
36pt;"><fontface="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font><p class="MsoNormal"
style="margin-left:36pt;"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">Hi Folks,<br /><br />
sorryif this is a duplicate post, i've been tryin to find a solution of importing data into postgres from a csv file.
Theproblem is, I have a database which consists of columns which contain newline characters (mac and unix). now when i
exportthese 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 (<>){<br />      # Chop the crlf<br />      chop ($_);<br />     
chop($_);<br /><br />      # this first bit goes through and replaces <br />      # all the commas that re not in 
quoteswith 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 any quotes<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
messageslike data missing for xyz column.<br /> any possible hints....... <br /><br /> --<br /> Thanks,<br /> Sumeet
</span></font></div><br/><p><b>*******************Confidentiality and Privilege Notice*******************</b><p>The
materialcontained in this message is privileged and confidential to the addressee. If you are not the addressee
indicatedin this message or responsible for delivery of the message to such person, you may not copy or deliver this
messageto anyone, and you should destroy it and kindly notify the sender by reply email. <p>Information in this message
thatdoes not relate to the official 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
consequentialloss arising from transmission of this message or any attachments <br /></blockquote><br /><pre
class="moz-signature"cols="72">-- 
 
Scot P. Floess
27 Lake Royale
Louisburg, NC  27549

252-478-8087 (Home)
919-754-4592 (Work)

Chief Architect JPlate  <a class="moz-txt-link-freetext"
href="http://sourceforge.net/projects/jplate">http://sourceforge.net/projects/jplate</a>
Chief Architect JavaPIM <a class="moz-txt-link-freetext"
href="http://sourceforge.net/projects/javapim">http://sourceforge.net/projects/javapim</a>
</pre>