pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedimorting csv files into postgres
4+ messages / 3 participants
[nested] [flat]
* imorting csv files into postgres
@ 2001-01-18 18:03 mike <mike@cyborg-group.com>
0 siblings, 1 reply; 4+ messages in thread
From: mike @ 2001-01-18 18:03 UTC (permalink / raw)
To: pgsql-interfaces
Does anyone know if you can import csv text files into postgres
I have tried the COPY command and it needs to have an existing table,
while what I want to do is create a new table from a csv file.
Also is there any way to define the text seperater ala MSSQL eg
field seperator = ,
text seperator = "
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: imorting csv files into postgres
@ 2001-01-18 20:48 Lamar Owen <lamar.owen@wgcr.org>
parent: mike <mike@cyborg-group.com>
0 siblings, 1 reply; 4+ messages in thread
From: Lamar Owen @ 2001-01-18 20:48 UTC (permalink / raw)
To: mike <mike@cyborg-group.com>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: imorting csv files into postgres
@ 2001-01-18 21:33 Adam Lang <aalang@rutgersinsurance.com>
parent: Lamar Owen <lamar.owen@wgcr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Adam Lang @ 2001-01-18 21:33 UTC (permalink / raw)
To: ; +Cc: pgsql-interfaces
Yeah, but he doesn't want to have to create the table before hand, he wants
postgresql to do all the work. ;)
The only way that I know of to do that is write a program and feed it the
CSV file... but in the program it is going to need to create the table.
Sometimes you just have to do the work yourself. :)
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Lamar Owen" <lamar.owen@wgcr.org>
To: "mike" <mike@cyborg-group.com>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Thursday, January 18, 2001 3:48 PM
Subject: Re: [INTERFACES] imorting csv files into postgres
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: imorting csv files into postgres
@ 2001-01-18 21:55 Lamar Owen <lamar.owen@wgcr.org>
parent: Adam Lang <aalang@rutgersinsurance.com>
0 siblings, 0 replies; 4+ messages in thread
From: Lamar Owen @ 2001-01-18 21:55 UTC (permalink / raw)
To: Adam Lang <aalang@rutgersinsurance.com>; +Cc: pgsql-interfaces
Adam Lang wrote:
> Yeah, but he doesn't want to have to create the table before hand, he wants
> postgresql to do all the work. ;)
> The only way that I know of to do that is write a program and feed it the
> CSV file... but in the program it is going to need to create the table.
I have such a script for AOLserver Tcl that does all the work. But it
requires a working AOLserver/PostgreSQL installation, which will take
longer to set up than a simple perl script would take to be written.
The hard part is the CSV parsing -- creating the table and filling in
the data is easy in comparison, especially in the Pg perl interface.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-01-18 21:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-18 18:03 imorting csv files into postgres mike <mike@cyborg-group.com>
2001-01-18 20:48 ` Lamar Owen <lamar.owen@wgcr.org>
2001-01-18 21:33 ` Adam Lang <aalang@rutgersinsurance.com>
2001-01-18 21:55 ` Lamar Owen <lamar.owen@wgcr.org>
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