Thread: loading delimited files

Are there docs on how to load delimited files into the database?

thanks!

Troy Campano

> Are there docs on how to load delimited files into the database?

Look into the SQL reference for the "COPY" command or the \copy option in
the psql man page.

One thing to look out for is whether the file is readable by the
front/backend depending on which method you use.

If your data needs some massaging then, as always, perl is your friend.

- Richard Huxton

On Mon, Jan 13, 2003 at 01:27:29PM -0500, Campano, Troy wrote:
> Are there docs on how to load delimited files into the database?

closer than you might think. "\h copy"!

    psql somedatabase

    somedatabase> \h copy

    Command:     COPY
    Description: copy data between files and tables
    Syntax:
    COPY [ BINARY ] table [ WITH OIDS ]
        FROM { 'filename' | stdin }
        [ [USING] DELIMITERS 'delimiter' ]
        [ WITH NULL AS 'null string' ]
    COPY [ BINARY ] table [ WITH OIDS ]
        TO { 'filename' | stdout }
        [ [USING] DELIMITERS 'delimiter' ]
        [ WITH NULL AS 'null string' ]

\t (tab) is default field separator,
\n (newline) is default record separator,
\N is default for NULL

pick a small table in your current database and try

    psql> copy tinytable to stdout;

to see the format. note that ALL fields in the table are
exported, and should be included on the import, as well (in
field-definition order, from your create statements).

    psql> copy atable FROM '/this/file/here.tsv';

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

> > Are there docs on how to load delimited files into the database?
>
> to see the format. note that ALL fields in the table are
> exported, and should be included on the import, as well (in
> field-definition order, from your create statements).

And again, if using Pg 7.3 you are not even bound to original column
ordering, or are obliged to copy all the columns.

Also check psql command \copy, it's more often the one you need.

--
Antti Haapala
+358 50 369 3535
ICQ: #177673735