Content-Type: text/plain;
  charset="iso-8859-15"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Oliver Vecernik <vecernik@aon.at>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: CSV import
Date: Wed, 29 Jan 2003 10:04:48 +0000
User-Agent: KMail/1.4.3
References: <3E3697AF.2030509@aon.at> <3E376BB2.4010302@aon.at>
In-Reply-To: <3E376BB2.4010302@aon.at>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200301291004.48919.gary.stainburn@ringways.co.uk>

On Wednesday 29 January 2003 5:50 am, Oliver Vecernik wrote:
> Oliver Vecernik schrieb:
> > Hi again!
> >
> > After investigating a little bit further my CSV import couldn't work
> > because of following reasons:
> >
> > 1. CSV files are delimited with CR/LF
> > 2. text fields are surrounded by double quotes
> >
> > Is there a direct way to import such files into PostgreSQL?

Here's a simple command that will take

"hello","world","splat","diddle"
"he said "hello world" to ","his mate"
 and convert it to the following tab delimited file that can be COPYed using 
psql. It even handles quotes inside fields. (^m and ^i are done by typing 
CTRL+V CTRL+M and CTRL+V CTRL+I)

hello   world   splat   diddle
he said "hello world" to        his mate

sed 's/^"//' <t.txt|sed 's/"^m$//'|sed 's/","/^i/g'>t1.txt

Gary

>
> The answer seems to be no. But after googeling a bit a found a wonderful
> Python module called csv at:
>
> http://www.object-craft.com.au/projects/csv/
>
> A minimal script called 'csv2tab.py' for conversion to a tab delimited
> file could be:
>
> #!/usr/bin/env python
>
> import csv
> import sys
>
> def convert(file):
>     try:
>         f = open(file, 'r')
>         lines = f.readlines()
>         p = csv.parser()
>         for line in lines:
>             print '\t'.join(p.parse(line))
>     except:
>         print 'Error opening file!'
>
> if __name__ == '__main__':
>     convert(sys.argv[1]);
>
> Regards,
> Oliver

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000