Re: text file import

On Thu, 2005-06-10 at 16:14 +0100, Richard Huxton wrote:
> Bart van den Eijnden wrote:
> > Hi list,
> >
> > I am trying to transfer the following from MySQL to PostgreSQL:
> >
> > load data local
> > infile 'D:/tmp/InterAcces- MySQL/03102005/bedrijven.txt'
> > into table bedrijven
> > fields terminated by ',' optionally enclosed by '^'
> > lines terminated by ';\r\n';
> >
> > Is there a way to do this without changing the file (this file is delivered by a 3rd party)? Btw, I am on the win32 platform.
>
> Normally, I'd pipe it through a Perl filter into a COPY FROM STDIN
> command. Not sure what you'll have installed on Windows.
>
> Oh - and you might need to correct some of the data if MySQL isn't being
> strict enough.
>
I would have to second Richard on the last statement.

MySQL formats many data types in ways that may not be possible to
directly import into PostgreSQL, and has a bad habit of using non-
standard representations for NULL; such as '00-00-0000' for date, and
other similar invalid or wrong data values for NULL in other types.

Good Luck