Re: Importing data from csv

Well, being that there isn't a RFC for CSV...other than "defacto"
definitions...I am pretty sure that is widely agreed upon ;)

Phillip Smith wrote:
>
> There you go -- it was the quotes that did it, not the back-slashes. I
> knew someone else would shed some better light! J
>
>
>
> Cheers,
>
> -p
>
>
>
> -----Original Message-----
> *From:* pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Scot P. Floess
> *Sent:* Friday, 25 August 2006 10:00
> *To:* floess(at)mindspring(dot)com
> *Cc:* Phillip Smith; pgsql-sql(at)postgresql(dot)org
> *Subject:* Re: [SQL] Importing data from csv
>
>
>
> And if its contained with quotes...its considered a field
>
> Scot P. Floess wrote:
>
> A newline in CSV parlance denotes the end of a record....unless that
> newline is contained with quotes...
>
> Phillip Smith wrote:
>
> I recently did this by parsing the data through a VB program that
> appended a "\" in front of any Char(10) and/or Char(13) characters
> which tells Postgres to accept the next character as a literal part of
> the column value I believe -- must do because it worked! I also quoted
> the whole column as part of the VB prog...
>
>
>
> Worked for me but I'm not sure the exact science behind it so someone
> else might be able to be of some more detailed help.
>
>
>
> Cheers,
>
> -p
>
>
>
> -----Original Message-----
> *From:* pgsql-sql-owner(at)postgresql(dot)org
> <mailto:pgsql-sql-owner(at)postgresql(dot)org>
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Sumeet
> *Sent:* Friday, 25 August 2006 00:48
> *To:* pgsql-sql(at)postgresql(dot)org <mailto:pgsql-sql(at)postgresql(dot)org>
> *Subject:* [SQL] Importing data from csv
>
>
>
> 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
>
>
>
> ********************Confidentiality and Privilege
> Notice********************
>
> The material contained in this message is privileged and confidential
> to the addressee. If you are not the addressee indicated in this
> message or responsible for delivery of the message to such person, you
> may not copy or deliver this message to anyone, and you should destroy
> it and kindly notify the sender by reply email.
>
> Information in this message that does 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 consequential
> loss arising from transmission of this message or any attachments
>
>
>
> --
> Scot P. Floess
> 27 Lake Royale
> Louisburg, NC 27549
>
> 252-478-8087 (Home)
> 919-754-4592 (Work)
>
> Chief Architect JPlate http://sourceforge.net/projects/jplate
> Chief Architect JavaPIM http://sourceforge.net/projects/javapim
>
>
>
>
> --
> Scot P. Floess
> 27 Lake Royale
> Louisburg, NC 27549
>
> 252-478-8087 (Home)
> 919-754-4592 (Work)
>
> Chief Architect JPlate http://sourceforge.net/projects/jplate
> Chief Architect JavaPIM http://sourceforge.net/projects/javapim
>
> ********************Confidentiality and Privilege
> Notice********************
>
> The material contained in this message is privileged and confidential
> to the addressee. If you are not the addressee indicated in this
> message or responsible for delivery of the message to such person, you
> may not copy or deliver this message to anyone, and you should destroy
> it and kindly notify the sender by reply email.
>
> Information in this message that does 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 consequential
> loss arising from transmission of this message or any attachments
>

--
Scot P. Floess
27 Lake Royale
Louisburg, NC 27549

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

Chief Architect JPlate http://sourceforge.net/projects/jplate
Chief Architect JavaPIM http://sourceforge.net/projects/javapim