Skip site navigation (1) Skip section navigation (2)

Re: imorting csv files into postgres

From: Lamar Owen <lamar(dot)owen(at)wgcr(dot)org>
To: mike <mike(at)cyborg-group(dot)com>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: imorting csv files into postgres
Date: 2001-01-18 20:48:11
Message-ID: 3A67568B.59572A40@wgcr.org (view raw or flat)
Thread:
Lists: pgsql-interfaces
mike wrote:

> Does anyone know if you can import csv text files into postgres

A perl script to do that was just posted to the GENERAL List....
Hmmmm....

Tim Uckun just posted the following:
-------
Here is your steps.
  1) Create your destination table in postgres
2)  Export your data to a comma delimeted format
3)  save this perl script to a file

------
#! /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";
}
-----

4) type this cat myfile.txt | perl myprog.pl > outfile.dat
5) go to psql
6) COPY "tablename" FROM '/path/to/outfile.dat' USING DELIMITERS '~';
-----

See if that helps any.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

In response to

Responses

pgsql-interfaces by date

Next:From: Adam LangDate: 2001-01-18 21:33:09
Subject: Re: imorting csv files into postgres
Previous:From: Craig BrozefskyDate: 2001-01-18 18:24:16
Subject: Re: some libpq interface issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group