From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Oliver Vecernik <vecernik@aon.at>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: CSV import
Date: Wed, 29 Jan 2003 10:04:48 +0000
Message-ID: <200301291004.48919.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <3E376BB2.4010302@aon.at>
References: <3E3697AF.2030509@aon.at>
<3E376BB2.4010302@aon.at>
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
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: gary.stainburn@ringways.co.uk, vecernik@aon.at
Subject: Re: CSV import
In-Reply-To: <200301291004.48919.gary.stainburn@ringways.co.uk>
* 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