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 2A18C9FB228 for ; Thu, 24 Aug 2006 21:06:02 -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 55480-09 for ; Fri, 25 Aug 2006 00:05:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from weatherbeeta.com.au (mail.weatherbeeta.com.au [202.154.127.242]) by postgresql.org (Postfix) with SMTP id A743C9FA646 for ; Thu, 24 Aug 2006 21:05:37 -0300 (ADT) Received: (qmail 15645 invoked from network); 25 Aug 2006 00:05:35 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;5395973be83a945c99a668f8427ad9d7; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 25 Aug 2006 00:05:35 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d5b4f62 for pgsql-sql@postgresql.org; Fri, 25 Aug 2006 10:04:50 +1000 From: "Phillip Smith" To: Subject: Re: Importing data from csv Date: Fri, 25 Aug 2006 10:04:50 +1000 Message-ID: <000501c6c7da$15a01b10$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C6C82D.E74C2B10" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: <44EE3D70.50302@mindspring.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1156464335.441-15634-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.08/0.23 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.818 tagged_above=0 required=5 tests=HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL, UNPARSEABLE_RELAY X-Spam-Level: *** X-Archive-Number: 200608/194 X-Sequence-Number: 26152 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6C82D.E74C2B10 Content-Type: text/plain;charset="us-ascii" Content-Transfer-Encoding: quoted-printable There you go - it was the quotes that did it, not the back-slashes. I = knew someone else would shed some better light! :-) =20 Cheers, -p =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Scot P. Floess Sent: Friday, 25 August 2006 10:00 To: floess@mindspring.com Cc: Phillip Smith; pgsql-sql@postgresql.org Subject: Re: [SQL] Importing data from csv =20 And if its contained with quotes...its considered a field Scot P. Floess wrote:=20 A newline in CSV parlance denotes the end of a record....unless that = newline is contained with quotes... Phillip Smith wrote:=20 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. =20 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. =20 Cheers, -p =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Sumeet Sent: Friday, 25 August 2006 00:48 To: pgsql-sql@postgresql.org Subject: [SQL] Importing data from csv =20 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 =3D 0; while (<>){ # Chop the crlf chop ($_); chop ($_); # this first bit goes through and replaces=20 # all the commas that re not in quotes with tildes for ($i=3D0 ; $i < length($_) ; $i++){ $char=3Dsubstr($_,$i,1); if ($char eq '"' ){ $inquotes =3D not($inquotes);=20 }else{ if ( (!$inquotes) && ($char eq ",") ){ substr($_,$i,1)=3D"~"; } } } # 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.......=20 -- Thanks, Sumeet=20 =20 *******************Confidentiality and Privilege = Notice*******************=20 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.=20 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=20 --=20 Scot P. Floess 27 Lake Royale Louisburg, NC 27549 =20 252-478-8087 (Home) 919-754-4592 (Work) =20 Chief Architect JPlate http://sourceforge.net/projects/jplate Chief Architect JavaPIM http://sourceforge.net/projects/javapim =20 --=20 Scot P. Floess 27 Lake Royale Louisburg, NC 27549 =20 252-478-8087 (Home) 919-754-4592 (Work) =20 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 ------=_NextPart_000_0006_01C6C82D.E74C2B10 Content-Type: text/html;charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Scot P. Floess
Sent: Friday, 25 August = 2006 10:00
To: = floess@mindspring.com
Cc: Phillip Smith; pgsql-sql@postgresql.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<= /p>

 

-----Original Message-----
From: pgsql-sql-owner@postgresql= .org [mailto:pgsql-sql-owner@pos= tgresql.org] On Behalf Of Sumeet
Sent: Friday, 25 August = 2006 00:48
To: pgsql-sql@postgresql.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 =3D 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=3D0 ; $i < length($_) ; $i++){
         = $char=3Dsubstr($_,$i,1);
         if ($char eq '"' = ){
             $inquotes =3D not($inquotes);
         }else{
             = if ( (!$inquotes) && ($char eq ",") ){
            &= nbsp;    substr($_,$i,1)=3D"~";
             = }
         }
     }
     # 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

 

*******************Confidenti= ality 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/pr=
ojects/jplate
Chief Architect JavaPIM http://sourceforge.net/p=
rojects/javapim
  



-- 
Scot P. Floess
27 Lake Royale
Louisburg, NC  =
27549
 
252-478-8087 (Home)
919-754-4592 (Work)
 
Chief Architect JPlate  http://sourceforge.net/pr=
ojects/jplate
Chief Architect JavaPIM http://sourceforge.net/p=
rojects/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
------=_NextPart_000_0006_01C6C82D.E74C2B10--