RE: Re: Load Text File into DB - Mailing list pgsql-general

From Jeff Eckermann
Subject RE: Re: Load Text File into DB
Date
Msg-id 08CD1781F85AD4118E0800A0C9B8580B094A27@NEZU
Whole thread Raw
In response to Load Text File into DB  ("Karl J. Stubsjoen" <karlflips@kstub.com>)
List pgsql-general
COPY expects tab delimiters by default, so specifying them is not strictly
necessary in this case.
Specifying "null as xxx" is a good idea, because COPY interprets an empty
field as an empty string, which will cause failure for non text-type fields.
A couple of other things to watch out for when bulk-loading data with COPY
(based on my painful experiences):
*    Embedded delimiters, i.e. tabs in your data.  Start by grepping for
lines with more than the expected number of tabs.
*    Fields ending with a backslash.  Sounds unlikely, but this has
happened to me several times.  The result is that your delimiter is escaped,
shifting all of your following fields down by one.  This will either cause
COPY to fail (because of data type mismatches), or messed up data in that
record.
*    For files imported from a Windows machine, better edit out the
carriage returns.

> -----Original Message-----
> From:    Chris Jones [SMTP:chris.jones@romancompany.com]
> Sent:    Friday, April 13, 2001 1:27 PM
> To:    pgsql-general@postgresql.org
> Subject:    [GENERAL] Re: Load Text File into DB
>
> This is what I have used with success..
>
> copy {table name} from '{delimited file name}' using delimiters '\t' with
> null as '';
>
> Of course, don't put in the brackets and you can use whatever null
> character
> you might need...
>
> Hope this helps
>
> Chris
> Karl J. Stubsjoen wrote in message <9b20s1$1b3d$1@news.tht.net>...
> >Hello,
> >
> >I have a tab delimited text file located on the server and would like to
> >load this into a table.  How do I accomplish this?
> >
> >Thanks!
> >
> >Karl
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

pgsql-general by date:

Previous
From: Tulio Oliveira
Date:
Subject: Driver ODBC postdrv.exe fails on 7.1
Next
From: Justin Clift
Date:
Subject: Re: Success: PostgreSQL 7.1 on MacOS X (Darwin 1.3)