Carriage return in text fields

[Ets ROLLAND]

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]

Post by Ets ROLLAND
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
[phone]
[phone] (cell)


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

[Ian Harding]

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

Post by Ets ROLLAND
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