From: Scot P. Floess <floess@mindspring.com>
To: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Importing data from csv
Date: Thu, 24 Aug 2006 19:57:13 -0400
Message-ID: <44EE3CD9.5000300@mindspring.com> (raw)
In-Reply-To: <004a01c6c7d3$60de8320$9b0014ac@ITPhil>
References: <004a01c6c7d3$60de8320$9b0014ac@ITPhil>
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
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: <44EE3CD9.5000300@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