Carriage return. Such a wonderfully archaic term for the hidden byte that ends a line of text. My children have no idea what kind of carriage can return.
Speaking of archaic, applications like Microsoft Access have trouble with carriage returns. They can't easily be inserted into a text field, but ODBC imports from more sophisticated applications, such as FileMaker Pro, can insert carriage returns into Access fields.
Problem is, there's no easy way to remove them. Fields with embedded CRs behave oddly when edited, and exports and queries break. Search and replace won't work.
I found a method that works.
Export the key column and the troublesome field as XML (no XSD). Then use a text editor to replace every carriage return with a space. The result is a single line, but this doesn't affect XML import. Reimport the XML and the carriage returns are gone.
There are probably better methods.
Update 1/2/07: I ran into another issue where an Access field appeared empty, but it was not NULL. I used 'not null' and 'len=0' to identify these fields, then set them to NULL. Probably another character set problem. I have finally liked all the problems with creating a database that works with Outlook 2003, the PalmOS, sync to Palm, and FileMaker (via ODBC). More on that after I get some sleep.
3 comments:
Thank you so very much for this. I've 6 databases totalling around 7k records that I'm importing into a database from FMPro.
This has saved me.
Thankyou so very much for this - this has saved me.
6 databases, 7K records, all with multiple CR's in them.
Was tearing out my hair trying to get rid of those CR-LF's.
THANK YOU! This idea even worked in Nisus Writer Pro - just click on "Show Invisibles," copy the character that is shown at the end of the hard return, the do a find for that character and replace it with a space. You have saved me a lot of time and I appreciate your great solution!
Post a Comment