Re: Removing CR LF Characters

On 23-May-2016 01:24 -0500, Don Brown wrote:

Hoping someone may see what I am missing.

I have an extract from a MSSQL server that includes this line

isnull( replace( replace( convert( nvarchar(max)
, TR.Description )
, char(13) + char(10),' ' )
, ',', ';' )
, '' )
as [Info]

Which is to remove CR and LF as well as any commas.

The output is to a csv format file that is then loaded into a
database file on the IBM i via FTP and subsequently processed.

Today the file load failed and investigation identified multiple
records caused by the CR LF characters.

Looking at the file in notepad++ I can see the CR LF but the above
replace should have removed them ???

If you copy the following lines and paste into notepad++ you may see
the CR LF codes if they are not stripped out by the site software.

<Start>
special care.

AAN Account..
<End>

Any suggestions welcomed.

I am unsure what the CONVERT() scalar is doing, and to what. Regardless, seems that the data likely is coming from the qualified column name DESCRIPTION.

From seeing the data pasted [appearing as three lines], and the reference to that expression that was supposed to have replaced the CRLF data, seems the implication is that the pasted data is presumed to have come from the output of that expression.

Only because each fragment [first line and third line] ends with a period, that data seems unlikely to be from a\the DESCRIPTION field; i.e. is the data there really "special␠care.␍␊␍␊AAN␠Account..", or might that instead be data for three records, the first ending with "…special␠care.␍␊␊" the second an empty record [i.e. just "␍␊"and the third starting with "AAN␠Account..…"? And thus perhaps the actual problem is an empty record [being generated for the export]?

If "load failed" refers to effects from CPYFRMIMPF:

One of the corrections to the Copy From Import File (CPYFRMIMPF) import feature made long ago, should avoid the need to strip the CRLF; i.e. CRLF can be embedded, just as there had long prior been support for embedded commas. That requires however, that the character string data be delimited; e.g. if the character string for that column is delimited with double-quote characters, then the CRLF can remain embedded [just as can a comma, so then there would be no reason to replace each with colon] without ill-effect.