To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Export tab delimited from mysql to postgres. 
In-reply-to: <82E30406384FFB44AFD1012BAB230B5505F40D43@shiva.au.lpint.net> 
References: <82E30406384FFB44AFD1012BAB230B5505F40D43@shiva.au.lpint.net>
Comments: In-reply-to Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
	message dated "Tue, 12 Oct 2004 15:33:46 +1000"
Date: Tue, 12 Oct 2004 09:56:15 -0400
Message-ID: <1763.1097589375@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> As you can see row 2 has a value that holds a CR value which ends up
> wrapping around onto the third line. Postgres copy command does not like
> this and mysql is unable to replace the value with another type of
> delimiter, like a \r.

> So I gather I have to some how manually replace the carriage return with
> something postgres understand \r...

> columnA	columnB
> 1		What a day!
> 2		What a week it has \r been!
> 3		What the!

> How do I do this without getting a text file that looks like this
> 1	What a day! \r\n2		What a week it has \r been!\r\n3
> What the!\r\n

Looks like a simple sed problem to me:

	cat myfile | sed 's/\r$//' | sed 's/\r/\\r/g' >newfile

(Most likely you can do both steps in one sed process, but this is easy
to follow.)  This assumes that you only have newlines (\n) at the real
ends of lines, else you need to think harder about how to tell the
difference between data and formatting.

			regards, tom lane