Re: PG Admin - Import from URL?

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°