Re: [GENERAL] Carriage return in text fields
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 <[EMAIL PROTECTED]> 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
Re: [GENERAL] Carriage return in text fields
on 5/30/05 2:19 AM, [EMAIL PROTECTED] 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 [EMAIL PROTECTED])
[GENERAL] Carriage return in text fields
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
