From: Scot P. Floess <floess@mindspring.com>
To: floess@mindspring.com
Cc: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Importing data from csv
Date: Thu, 24 Aug 2006 19:59:44 -0400
Message-ID: <44EE3D70.50302@mindspring.com> (raw)
In-Reply-To: <44EE3CD9.5000300@mindspring.com>
References: <004a01c6c7d3$60de8320$9b0014ac@ITPhil>
<44EE3CD9.5000300@mindspring.com>
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@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
>>
>>
>>
>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: floess@mindspring.com, phillips@weatherbeeta.com.au
Subject: Re: Importing data from csv
In-Reply-To: <44EE3D70.50302@mindspring.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox