Re: text file import

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.

Here is an example from the file:

6603,^J.J. De Graaf^,^Achthovenerweg^,^1A^,^2351 AX^,^Leiderdorp^,^Revisievergunning (art. 8.4 Wm) (nieuw, de gehele inrichting omvattend)^,^2^,^50^,^1993-12-14^,^60^,^De Heer J.J. De Graaf^,^3^,^Veehouderijen (Niet Intensief)^,^3^,^96856^,^461861^,^^,^^,^2003-06-04^,^2005-09-28^,^16:22:00^,^^;
6605,^Neuteboom Tweewielers^,^Touwbaan^,^1E^,^2352 CZ^,^Leiderdorp^,^Meldingsplichtig ^,^1^,^50^,^1998-10-07^,^60^,^H. Neuteboom^,^1^,^Verkoop/Reparatie (Brom)Fietsen^,^10^,^95811^,^464378^,^M01^,^Detailhandel- En Ambachtsbedrijven^,^2004-01-06^,^2005-09-28^,^16:22:00^,^^;

Thanks in advance.

Best regards,
Bart

Bart van den Eijnden
Syncera IT Solutions
Postbus 270
2600 AG DELFT

tel.nr.: 015-7512436
email: BEN(at)Syncera-ITSolutions(dot)nl

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.

--
Richard Huxton
Archonet Ltd

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