I have a report that is putting and output file in COMMA format. I need to get rid of the CRLF at the end of each line. Anyone know how this can be done.

This message has been edited. Last edited by: <Kathryn Henning>,

Actually I need to replace the CRLF that get generated at the end of a line with a space.

So you want a CSV with only one line from multiple records ?

Hi,

if you are running WF on a Windows-Server +.NET installed you should be able to use powershell to convert replace the \r\n chars / or join all lines together.

Something like:

 
TABLE FILE CAR
PRINT
  CAR MODEL
ON TABLE HOLD AS H_TMP FORMAT COMMA
END
-RUN

-SET &TMPPATH = TEMPPATH(255,'A255');
-SET &TMPPATH = TRUNCATE(&TMPPATH);

-SET &P='|';
-DOS powershell "[String]::Join(\"\", (Get-Content '&TMPPATH|h_tmp.prn')) &P| Out-File '&TMPPATH|h_tmp2.prn'"

FILEDEF H_TMP2 DISK &TMPPATH|h_tmp2.prn
-RUN

SET HTMLFORMTYPE=TXT
-RUN
-HTMLFORM BEGIN
!IBI.FIL.H_TMP2;
-HTMLFORM END

Should also be possible in on linux / unix using e.g. "cat ... | sed ..."

If you need to have all columns however in a single row and use them again in WebFOCUS, you might come across problems as WF might be limited in the total column length (e.g. to 32k)

Cheers Linne

Seem s like their should be something simpler than that

SQL:
REPLACE(REPLACE(COLUMN_NAME,CHAR(10),' '),CHAR(13),' ') AS NEW_NAME

WebFOCUS:
DEFINE
NEW_COLUMN/A50 = STRREP(50, COLUMN_NAME, 2, HEXBYT(13, 'A1')|HEXBYT(10, 'A1'), 1, ' ', 50, 'A50');

I don't think there's anything in WebFOCUS that can help with that as the CSV format as expected by most tools assumes "," to be used as field separator and new line as record separator.

TexasStingray has a particular requirement to get rid of the CRLF and in my experience that can only be done outside of WF via OS tools which is exactly what linnex suggested.

I have this Perl script sitting under a utility app folder in the reporting server:

# Perl Script to remove CR/LF character from a file
# Required to avoid characters automatically added by WebFOCUS
# when creating HTML/HTMTABLE HOLD files with columns that have more than 511 characters.
#
# Usage:  perl D:\ibi\apps\util\remove_crlf.txt <hold-file>
 
use File::Copy qw(move);
 
my $src = "$ARGV[0]" . ".tmp";
my $tgt = "$ARGV[0]";
 
# Rename original file as .tmp
move $tgt, $src or die "Can't rename file: $tgt";
 
open SRC, "<$src" or die "Can't open temporary file: $src";
open TGT, ">$tgt" or die "Can't create target file: $tgt";
 
while (<SRC>) {
   chomp; 
   print TGT ($_);
}
 
close SRC;
close TGT;

Perl is available in most Unix/Linux distributions and since we had it also on our Windows Server it seemed like the right tool to use.

When I have a file whose CRLF characters are to be removed, all I need to do is:

TABLE FILE ..
PRINT ...
ON TABLE HOLD AS HDATA FORMAT HTMTABLE
END
-RUN

-DOS perl &IBI_APPS_DIR\util\remove_crlf.txt  hdata.htm

After that finishes, hdata.htm no longer has CRLF characters in it! This can easily be used on other text-based HOLD files (ALPHA, COMMA, COMT, ...). You'll just have to use the proper file extension (.ftm for instance) when passing the file name as argument to the Perl script.

TexasStringray, you may change the following line in order to get the extra space you need after each record:

   print TGT ($_ . " ");

It may seem like too much but I needed a solution a found no simpler way at that time. Removing extra validations from the script may shorten its length but I'm usually paranoid so I'd rather have them there

Hope this helps.

Tom, that's a very nice way to get rid of CRLF embedded directly in some data fields. I may have some good use for it to deal with some free-entry fields in my DB.

I ended up creating a JAVA program to replace the CRLF. Now I'll I need to do is figure out how to have WebFOCUS send a TXT file. I know I can use href in an -HTMLFORM tag but the browser opens the file and they would like to download it instead.

I would use EDAGET for that.

EX EDAGET TXT,yourfile.txt,T