PostgreSQL Discussion - Carriage return in text fields

Author

Carriage return in text fields

Ets ROLLAND

2005-05-30, 3:23 am

Hello !

I have to transfert data from MS-Access to PostgreSQL 8.03 under Windows 2003 server.
Text fields may contain a carriage return (CR-LF).
I use the copy instruction.
Under Pg-Admin III 1.21, I obtain an error message for that carriage return and a hint : use \n.
If I use a filter on the cvs file replacing carriage return with \n, copy works but I have the char \n
in the text fields, not the carriage return !?
If I use PSQL, the carriage return are replaced by a space !
In Pg-Admin III I can insert a carriage return (with SHIFT ENTER) in a text field.

How can I transfert my text fields WITH all the carriage return inside the text fields ?
Thanks for your tips...
Best regards.

Luc

Keary Suska

2005-05-30, 8:23 pm

on 5/30/05 2:19 AM, ets@rolland-fr.com purportedly said:

> I have to transfert data from MS-Access to PostgreSQL 8.03 under Windows 2003
> server.
> Text fields may contain a carriage return (CR-LF).
> I use the copy instruction.
> Under Pg-Admin III 1.21, I obtain an error message for that carriage return
> and a hint : use \n.
> If I use a filter on the cvs file replacing carriage return with \n, copy
> works but I have the char \n
> in the text fields, not the carriage return !?
> If I use PSQL, the carriage return are replaced by a space !
> In Pg-Admin III I can insert a carriage return (with SHIFT ENTER) in a text
> field.
>
> How can I transfert my text fields WITH all the carriage return inside the
> text fields ?

The line break in a DOS text file is two characters, and you will need to
escape both for a COPY command to work: \r\n. For best results, you may
want to use the COPY command from the psql command line.

I am not sure what you are using to convert the text file, but you should
create a new text file so you can inspect and ensure your filter is working
correctly. My thought is that if you are getting a literal '\n' in your
field content it means that something is re-escaping and PG is actually
getting '\\n'.

Best,

Keary Suska
(719) 473-6431
(719) 440-9952 (cell)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Ian Harding

2005-05-31, 3:23 am

In my experience, there is no way to tell an embedded carriage return
from and end-of-record carriage return other than counting intervening
tabs.

What I do, is fix it in access with a query for (believe it or not)

"*" & chr(13) & "*"

which returns all records that contain a CR. I then just delete the
extraneous CR before exporting the data.

If access could use a different record delimiter on export life would
be good, but it doesn't.

You could also run a script on your exported data that would count
TABs on a line before the CR/LF was encountered, and delete any that
appear too early. I am not smart enough to do that.

Good luck.

Ian
On 5/30/05, Ets ROLLAND <ets@rolland-fr.com> wrote:
>
> Hello !
>
> I have to transfert data from MS-Access to PostgreSQL 8.03 under Windows
> 2003 server.
> Text fields may contain a carriage return (CR-LF).
> I use the copy instruction.
> Under Pg-Admin III 1.21, I obtain an error message for that carriage return
> and a hint : use \n.
> If I use a filter on the cvs file replacing carriage return with \n, copy
> works but I have the char \n
> in the text fields, not the carriage return !?
> If I use PSQL, the carriage return are replaced by a space !
> In Pg-Admin III I can insert a carriage return (with SHIFT ENTER) in a text
> field.
>
> How can I transfert my text fields WITH all the carriage return inside the
> text fields ?
> Thanks for your tips...
> Best regards.
>
> Luc

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links