Message-ID: <3E370095.3020200@incentre.net>
Date: Tue, 28 Jan 2003 15:13:41 -0700
From: Guy Fraser <guy@incentre.net>
Organization: J I Reasearch
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: CSV import
References: <3E3697AF.2030509@aon.at>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Hi

You will need two text utilities {dos2unix and sed} to do this in the simplest 
way. They are fairly standard text utilities and are probably already on your 
machine.

This is how I would do it :

sed "s/\"//g" file_name.txt \
	| dos2unix \
	| pgsql -c "COPY table_name FROM STDIN USING DELIMITERS ',';" db

Where "file_name.txt" is the csv file you want to import and "table_name" is 
the previously created table you want to insert the data into and db is the 
database name.

How this works is "sed" {stream editor} removes all the double quote 
characters '"' then pipes the output through "dos2unix" which converts all the 
CRLF {DOS EOL} sequences into CR {UNIX EOL} characters, then pipes the data to 
"pgsql"  with a command that does a bulk insert into the table of the database 
you have selected.


Guy

Oliver Vecernik wrote:
> 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?
> 
> I would like to have something like MySQL provides:
> 
> LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
>    [REPLACE | IGNORE]
>    INTO TABLE tbl_name
>    [FIELDS
>        [TERMINATED BY '\t']
>        [[OPTIONALLY] ENCLOSED BY '']
>        [ESCAPED BY '\\' ]
>    ]
>    [LINES TERMINATED BY '\n']
>    [IGNORE number LINES]
>    [(col_name,...)]
> 
> Has anybody written such a function already?
> 
> Regards,
> Oliver
>