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

Re: PG Admin - Import from URL?

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: PG Admin - Import from URL?
Date: 2012-03-16 14:29:00
Message-ID: 20120316142900.GA7621@tux (view raw or flat)
Thread:
Lists: pgsql-novice
James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:

> Hi Andreas,
> 
> Thanks for your reply, but I've no idea what WGET or STDOUT is. As the
> mailing list suggests, I'm a pgsql novice... :-)
> 
> No worries anyway, it was more a query/question than a particular problem.

1st: abswer to the list, not to me
2nd: please void top-posting, it's hard to read
3rd:

a simple example:

kretschmer(at)tux:~$ cat data.csv
1,data1
2,data2
3,data3
kretschmer(at)tux:~$ cp data.csv public_html/
kretschmer(at)tux:~$ echo "create table download (col1 int, col2 text)" | psql -X test
CREATE TABLE
kretschmer(at)tux:~$ wget http://localhost/~kretschmer/data.csv -O - -q | psql -X -c "copy download from stdin delimiter ','" test
kretschmer(at)tux:~$
kretschmer(at)tux:~$
kretschmer(at)tux:~$ psql test -c "select * from download";
 col1 | col2
------+-------
    1 | data1
    2 | data2
    3 | data3
(3 rows)


I have a file called 'data.csv', with "," as delimiter. I copied that to my local webserver-webspace.
With wget i can download the file, option -O means 'write the content to stdout, and piped that into
psql. The table contains now the content of my file. That's all, easy, right?



> 
> Cheers
> 
> James
> 
> 
> 
> On 15 March 2012 16:04, Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:
> > James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> >> I then import the data as below:
> >>
> >> COPY busroutes_raw from 'C:/Program Files
> >> (x86)/PostgreSQL/8.4/data/BusRoutes.csv' DELIMITERS ',' CSV;
> >>
> >> But I wonder whether it is possible to put a URL as the origin of the
> >> file rather than a path?
> >
> > Sure, use tools like wget or other to fetch the file from internet and
> > raise it to stdout, pipe that to psql -c "\copy ... from 'stdin'"
> >
> > (something like that, untestet)
> >
> >
> > Andreas
> > --
> > Really, I'm not out to destroy Microsoft. That will just be a completely
> > unintentional side effect.                              (Linus Torvalds)
> > "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> > Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
> >
> > --
> > Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-novice


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

In response to

pgsql-novice by date

Next:From: SharonDate: 2012-03-18 05:32:20
Subject: Warning console code page (437) differs from Window code page (1252)
Previous:From: sara.schaerrerDate: 2012-03-16 08:49:23
Subject: Re: repeatet summary querys per month over 5 years

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