Removing Carriage Return or Line Feed in a Memo field

[Ted Giles]

I have to import data as a text file.
Usually there are no CR or LF's, however these have started creeping in.
Is there an easy way to scan all Memo fields and remove the line feeds and/or carriage returns?

[Stan Mathews]

Re: Removing Carriage Return or Line Feed in a Memo field

Line feeds are chr(10). Carriage returns are chr(13).

FUNCTION strip_crlf AS L (tablename AS C, fieldname AS C )
	tbl = table.open(tablename)
	tbl.fetch_first()
	tbl.batch_begin()
	while .not. tbl.fetch_eof()
		tbl.change_begin()
		eval("tbl."+fieldname) = strtran(eval("tbl."+fieldname),chr(10),"")
		eval("tbl."+fieldname) = strtran(eval("tbl."+fieldname),chr(13),"")
		eval("tbl."+fieldname) = strtran(eval("tbl."+fieldname),crlf(),"")
		tbl.change_end(.T.)
		tbl.fetch_next()
	end while
	tbl.close()
END FUNCTION

Not for rtf memo fields.

You might want to replace the special characters with spaces rather than just remove them.


strtran(eval("tbl."+fieldname),chr(10)," ")

[DaveM]

Re: Removing Carriage Return or Line Feed in a Memo field

Don't know if this would help, but look here: http://stevemiller.net/puretext/

[Tom Cone Jr]

Re: Removing Carriage Return or Line Feed in a Memo field

My own preference would substitute a blank space, instead of null. Can help readability in many cases.

[Ted Giles]

Re: Removing Carriage Return or Line Feed in a Memo field

Thanks gents.
I have inhereted a really messy interface process which has a load of manual intervention.
When I get it set up as a proper process I'll post the example as it might help others.

[Ted Giles]

Re: Removing Carriage Return or Line Feed in a Memo field

Stan, I have tried the process, and it strips the first occurrence of the 10 and 13 char$.

The problem has escalated to multiple occurrences, viz;

This is what is entered,

First name, Last name
Address Line 1
Address Line 2

This is what I need as a single line.
First name Last name Address line 1 Address line 2.

Is there any way to remove all of the line feeds etc?

[Stan Mathews]

Re: Removing Carriage Return or Line Feed in a Memo field

Nothing in the function limits it to the first occurrence. Just tested it and it removed 7 crlf's. Should do the same for chr(10) and chr(13).

Yep, just tested. Handles multiple occurrences of any of the three. You may have some other special characters.

[Al Buchholz]

Re: Removing Carriage Return or Line Feed in a Memo field

I've found that a hex editor is helpful in finding odd characters.

There are free ones available. Look via your favorite search engine.

[Tom Cone Jr]

Re: Removing Carriage Return or Line Feed in a Memo field

Ted,

Copying and pasting from third party word processors can be problematic. Extraneous printer and formatting control codes can cause BIG problems in Memo fields / files.

You say you must "import" data as a text file. Can you elaborate on that? How is this being done?

If you're pasting from the windows clipboard you might want to sanitize the text before pasting.

-- tom

[MoGrace]

Re: Removing Carriage Return or Line Feed in a Memo field

Hi Ted,
Try out Another Notepad from Pc-Shareware.com.

[Ted Giles]

Re: Removing Carriage Return or Line Feed in a Memo field

Stan. I added simple [Enter] into the Memo field using Alpha and it leaves the lines in.
Al, I need to automate this process so 3rd party stuff is not allowed.
Tom, the data string is all as .txt with bit positions defining what is where. Bytes 1-5 = ID, Bytes 6 - 250 = free text, Bytes 251-259 = numeric value.
Example like it SHOULD look;
A1234Ted Giles Cedar Lodge Peppin Lane Fotherby [fill this with blanks for this exercise]123456789

What I get when a user puts a CR in the text area is
A1234Ted Giles
Cedar Lodge
Peppin Lane
Fotherby
[blanks as described above]
123456789

Each text string is 2059 bytes long and only one free format area within that. The A1234 is the key sequence for each line item which is going into a finance system so it is pretty precise and the content is on a byte range basis. Bytes 2059-2060 are Bank Type for example.

Robin. See comment to Al.

If all else fails, I will need to go BizTalk on this one, but I know that AS can do it as Stan said. Just cannot get it to work for me.
I'll post what I've tried when I get a moment and the alligators are asleep.

[Tom Cone Jr]

Re: Removing Carriage Return or Line Feed in a Memo field

Ted, while the gators are sleepin' tell us how the users populate the memo fields. Are they pasting or typing, for example?

-- tom

[GGandhi]

Re: Removing Carriage Return or Line Feed in a Memo field

if you do
your_memo_field = strtran(crlf_to_comma(your_memo_field),",","")
will that help?

[Stan Mathews]

Re: Removing Carriage Return or Line Feed in a Memo field

I still see it working for me unless I misunderstand the situation.

atest.zip

[Al Buchholz]

Re: Removing Carriage Return or Line Feed in a Memo field

Ted

I know you need this automated.

I'm trying to help you figure out what is actually in the file that is causing the problems.

Once the issues are identified a solution can be designed.