Removing Carriage Return or Line Feed in a Memo field

[G Gabriel]

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

Instead of strtran()
use:
strtran_multi()
Or, use regex

[Ted Giles]

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

Backing the trolley up.
Situation. An application built in SQLServer2008 has a field in one of the tables which is a "comments" field.
This is VarChar 255.
A process extracts the data from several tables and formats it in text blocks - no delimiters, like this;

A12345TedGiles Cedar Lodge Peppin Lane Fotherby 9999.99 9999.99 AP CL 20150114

It all works on byte position start and end.

My problem is that the comments field allows a Carriage Return and the extract process interprets this as the start of a new line and starts a new line with the remaining data.
This is not my design, it is a major suppliers design in the social care software market.
The extracted file is flawed, so when it is imported into Oracle Financials, there are lines which fail.
The supplier is not good at resolving problems like this and say it is a user issue "stop them hitting enter", which as they are NOT computer literate won't work. What is being done is that the extracted files are being examined manually and the CR's removed. It really is a simple process, but it takes 2 people nearly full time to correct these problems with the extracts. There are other simple issue I can fix using AS, but I had hoped - and I will try Stan's upload - to import the text file into AS, use a Memo field to examine the text in the Comments field and remove the CR's.

It is entirely possible that I have misunderstood Stan's suggestion so I'm off to try it now.

Oh, by the way, Oracle doesn't want to import � signs so they have to go too.

Edit:
It was me Stan, trying too hard with the code. Your kind example set me straight.
Now to see if I can get them to adopt it!

[Stan Mathews]

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

I don't see why they need to adopt it.
You have a form with a button that runs the operation to import the extract into a table with a memo field? The button uses action scripting to run the operation? Add an inline xbasic element which executes the function on the imported table.

[Ted Giles]

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

I'm dealing with Government Stan.
They all need to be consulted just in case there is a more convoluted and expensive way of doing something.
With your help, and the others who chipped in, it took about 1� hours to put a form together with the options for removing CR's and spaces, swapping - and + signs, adding a unique qualifier to a field and laying it out nicely.

Importing, processing and Formatting the output will probably take a bit longer as the files will need some pre-processing I'm sure.
They have a "wealth of BizTalk experience" it has been said, so maybe they want to go for the long winded and approved version as opposed to me and my Alpha toolkit.
Story of my life.
However on a separate subject, I have built them an installable data Dictionary which they have to get from my server, so we'll see what happens.


My next hurdle is removing Chr(156) � sign. I put it in the function but it is being ignored.

eval("tbl."+fieldname) = strtran(eval("tbl."+fieldname),chr(156),"")

Any ideas?

[Stan Mathews]

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

Try

eval("tbl."+fieldname) = strtran(eval("tbl."+fieldname),chr(163),"")

[Ted Giles]

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

That did it.
Alt 156 creates a � Alt 163 creates �
Chr(156) won't remove it, Chr(163) will remove it.
Thanks Stan.

[Stan Mathews]

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

Confusing to me as well

? chr(163)
= "�"

? asc("�")
= -93

[DVSOL005]

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

? asc("�", .t.)
= 163

[Ted Giles]

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

Why the need for [.t.] Jo?
Also, can I put an Octal or Hex value in as a replacement?

[DVSOL005]

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

Ted,
Please check the wiki for the asc function. The .T. returns a positive number for high-order characters.
As for Octal or Hex values, I did try but didn't find a possibility.

[Stan Mathews]

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

Also, can I put an Octal or Hex value in as a replacement?

No, but you can convert on the fly.

? chr(*from_hexn("A3"))
= "�"