Hi All,
I have a question regarding removing trailing spaces of any field.
on VMS Focus (OpenVMS AXP V8.2) that we are currently on, FORMAT COM/COMT/TAB wont work, as well as those TRIM or squeeze from WF either.
I cant use table file car for this example so I will try my best with the example data here.

Let's say I have ID and Phone Number fields and Email Fields extracting. and I want to have in CSV format ( which we can do FORMAT LOTUS, then rename to .CSV)..Let's say we have this field headers in the first line..and the rest is data..
"ID",""EMAIL 1","EMAIL 2", "EMAIL 3","PHONE 1," "PHONE 2"
"11111111","abc@hotmail.com","abc@abc.com","abc@abc1.com","111-111-1111","111-112-1112"
"22222222","LONG@YAHOO.COM","LONG@LONG.COM","LONG@LONG123.COM","222-222-2222","2222222222222","0112220202020202"


etc, etc..This will be the desired output file. As you can see that none of the fields will have trailing spaces at all. but these email fields, for example, are at least A50 length and phone numbers are A20. As you can see, some are short, but some has longer string length. so even when I cut down the data length for email down to A32, I still would have trailing spaces. How do I get rid of these trailing spaces at all on VMS? I tried the POSIT, SUBSTR and one other function I cant think of right now, but does not seem to be working.


It still comes out like this:
"ID",""EMAIL 1","EMAIL 2", "EMAIL 3","PHONE 1," "PHONE 2"
"11111111","abc@hotmail.com ","abc@abc.com ","abc@abc1.com ","111-111-1111 ","111-112-1112 "

I have no problem with the header, because it is a defined field and I can have it fixed length exactly that, but how do I fix the emails and phone fields? Any suggestions is appreciated.

Thanks very much in advance!!!
KK

one thing I need to add also is that I cant use the hard concatenation either since on VMS the length allowed is only upto 180 or so I think..so when I have alot of fields on one row, || wont work at all.

Thanks, unfortunately though on VMS, STRREP function is not supported, at least on this version of the platform?
Substr is the only one availabe as well as Gettok..

Can you post-process the file with a VMS script and operating system commands or Perl?

Or you could post-process the file with Dialogue Manager. That would allow you to do POSITs and other cool stuff in a loop to remove spaces.

Might be fun.

Can you give an example of how to post-process a file with vms script?

Here's post-processing within Focus (on Windows -- but should work with other platforms (mutatis mutandis)

[See improved solution below]

This message has been edited. Last edited by: j.gross,

Hi,

Well, I looked in the manual (7.6) and found this:

quote:

Field-based reformatting works for alphanumeric fields in a HOLD file, although three
fields are stored in the file for each field that is reformatted. To prevent the extra
fields from being propagated to the HOLD file, specify SET HOLDLIST=PRINTONLY.

Full of hope I tried this:

  
-* File xlblanks.fex
FILEDEF HH DISK c:\TEMP\HH.CSV
SET HOLDLIST=PRINTONLY
DEFINE FILE CAR
LCOUNTRY/I2=ARGLEN(10, COUNTRY, 'I2');
FC/A8='A' | EDIT(LCOUNTRY);
LCAR/I2=ARGLEN(16, CAR, 'I2');
FR/A8='A' | EDIT(LCAR);
LMODEL/I2=ARGLEN(24, MODEL, 'I2');
FM/A8='A' | EDIT(LMODEL);
END
TABLE FILE CAR
PRINT COUNTRY/FC CAR/FR MODEL/FM
ON TABLE SAVE AS HH FORMAT LOTUS
END

Alas, the spaces are still there

In fact (look again) it actually expanded the field widths!

If you can not find a VMS script writer in your own company the following Focus example should do what you want.

It assumes that the ASIS option exists on VMS (may need to look at a work round if it does not)
It assumes that you do not want ANY SPACES in the output.

Health Warning
IF YOU HAVE VERY LARGE AMOUNTS OF DATA BECAUSE OF THE LOOPS IT COULD TAKE AN EXTENDED AMOUNT OF TIME TO RUN.

-* create a format prn file appending a usable terminator such as %
DEFINE FILE CAR
TERMCHAR/A1='%';
END
TABLE FILE CAR
PRINT
DC RC TERMCHAR
BY CAR
ON TABLE HOLD FORMAT LOTUS
END
-RUN
-* set a counter for the outer loop
-SET &OLINES=&LINES;
-* filedef the final output file
FILEDEF NEWHOLD DISK NEWHOLD.PRN (APPEND
-RUN
-* outer loop once for each row
-REPEAT ER1 &OLINES TIMES
-READ HOLD NOCLOSE &INLINE.A512.
FILEDEF TEMPLINE DISK TEMPLINE.FTM
-RUN
-WRITE TEMPLINE NOCLOSE &INLINE
- set the counter for the inner loop by getting the position of the terminator -3
-SET &TE=POSIT('&INLINE.EVAL', 512, '%', 1, 'I3') -3;
-* re-allocate the row file so that it is read as a single character file
FILEDEF TEMPLINE DISK TEMPLINE.FTM (LRECL 1 RECFM F
-RUN
-SET &NEWLINE='';
-* inner loop 1 for each character in the row
-REPEAT ER2 &TE TIMES
-READ TEMPLINE NOCLOSE &CHAR.A1.
-IF &IORETURN NE 0 GOTO ER2;
-IF ASIS(&CHAR) EQ ' ' GOTO ER2;
-SET &NEWLINE = '&NEWLINE.EVAL' || '&CHAR.EVAL';
-ER2
-ER3
-WRITE NEWHOLD &NEWLINE.EVAL
-ER1
-ENDJOB

serenekk --

Here is a generalized program (csvtrim.fex) to generate a "trimmed CSV" format file from an input Hold file. Below that is a sample driver (csvtest.fex); and sample output. The programs are a rearrangement of my earlier post, with the core process isolated and genericized as a reusable utility.

In csvtrim.fex
- the input and output ddnames are parametrized
- the input Hold file can be of any type (provided its segments form a single path)
- The TABLE code is dynamically generated (no hard-coded Field list) and the appropriate record-length is calculated, based on CHECK FILE ... HOLD.
- The code should port readily (to VMS or other platforms), with appropriate adjustments to FILEDEF.

The approach is to change the trailing blanks in individual alpha fields to nulls (x'00'), then generate a format-LOTUS hold file, and finally post-process its records to delete the nulls. All field and record manipulation is via DEFINE and TABLE (no dialog-manager looping); and leading and imbedded spaces are preserved. The use of || catenation at the field level should not pose a problem.

  
-* csvtrim.fex             Jack Gross  8/2008           JacobBGross@hotmail.com
-*******************************************************************************
-* Given Hold file &1 (default CSVHOLD), generate &2 (CSVFILE) in LOTUS format,
-* but with trailing blanks trimmed from alpha fields.
-* (All workfile names start with csv$)  
-*******************************************************************************
-DEFAULT &1=CSVHOLD, &2=CSVFILE
-SET &CSVIN=&1;
-SET &CSVOUT=&2;
-SET &Blank = BYTVAL(' x','I3');
-SET &Null  = 000;
-* analyze fields, calculate Lotus LRECL and size of largest alpha field;
-* issue defines to overlay trailing blanks with nulls; and get Field list:
  CHECK FILE &CSVIN HOLD AS CSV$CHK
  DEFINE FILE CSV$CHK
    IS_ALPHA/I1= (EDIT(FORMAT,'9') EQ 'A');  IS_EXTRA/I1= (FLDNO GT 1);
    AWIDTH  /I5L=EXLEN*IS_ALPHA;   CSVLEN  /I5L=EXLEN + 2*IS_ALPHA + IS_EXTRA;
-SET &Field= ' ''||FIELDNAME||'' ';
    DEF/A500 = 'Len$=LENV(&Field,Len$); &Field=SUBSTR(Len$,&Field ||Nulls,1,Len$,Len$,&Field );' ;
-*              Len$=LENV(MODEL ,Len$);  MODEL=SUBSTR(Len$, MODEL ||Nulls,1,Len$,Len$,MODEL );
  END
  TABLEF FILE CSV$CHK
    SUM CSVLEN       AND SAVE AS CSV$LRECL
  RUN
    SUM MAX.AWIDTH   AND SAVE AS CSV$WIDTH    IF IS_ALPHA IS TRUE
  RUN
    PRINT DEF        AND SAVE AS CSV$DEFS     IF IS_ALPHA IS TRUE
  RUN
    PRINT FIELDNAME  AND SAVE AS CSV$FLDS
  END
-RUN
-READ CSV$LRECL &Lrecl.A5.
-READ CSV$WIDTH &Width.A5.
-RUN
  DEFINE FILE &CSVIN ADD
    Len$/I3=;  Nulls/A&Width='';  Nulls=CTRAN(&Width,Nulls,&Blank,&Null,Nulls);
-INCLUDE CSV$DEFS
  END
  TABLEF FILE &CSVIN
    PRINT
-INCLUDE CSV$FLDS
                     AND SAVE AS CSV$WORK FORMAT LOTUS
  END
-RUN
  FILEDEF CSV$MAS  DISK csv$work.mas
-RUN
-WRITE CSV$MAS FILE=CSV$WORK,SUFFIX=FIX,$
-WRITE CSV$MAS  SEGNAME=CSV$WORK,SEGTYPE=S0,$
-WRITE CSV$MAS   FIELD=RECORD,RECORD,A&Lrecl,A&Lrecl,$
-RUN
  DEFINE FILE CSV$WORK
    Null/A1=HEXBYT(&Null ,'A1');
    TRIMREC/A&Lrecl = STRREP(&Lrecl,RECORD, 1,Null, 0,' ', &Lrecl,'A&Lrecl');
  END
  TABLEF FILE CSV$WORK
    PRINT TRIMREC    AND SAVE AS &CSVOUT      IF RECORD NE ' '
  END

/* in your driver, after you create a hold file with the fields to be produced, call CSVTRIM passig the input and output filenames as parameters. */
/* Use APP HOLD or a Copy command to capture the output file */

-* CSVTEST.fex
  APP PATH SESSION IBISAMP
  APP HOLD HOLD
  MATCH FILE CAR   WRITE WARRANTY    BY CAR
  RUN
  FILE CAR         WRITE SEATS       BY CAR BY MODEL
    AFTER MATCH HOLD AS CSVHOLD OLD-OR-NEW
  END
-*******************************************************************************
  EX CSVTRIM CSVHOLD,CSVFILE
-*******************************************************************************
  ? FILEDEF
  DOS COPY .\csv* \ibi\apps\hold\
  DOS dir \ibi\apps\hold\csv* /o:n


/* untrimmed LOTUS hold file [for car, warranty, model, seats] would contain: */

"ALFA ROMEO      ","                                        ","2000 4 DOOR BERLINA     ",  4
"ALFA ROMEO      ","                                        ","2000 GT VELOCE          ",  2
"ALFA ROMEO      ","                                        ","2000 SPIDER VELOCE      ",  2
"AUDI            ","12 MONTHS OR 20000 MILES                ","100 LS 2 DOOR AUTO      ",  5
. . .

/* trimmed output (csvfile.ftm): */

"ALFA ROMEO","","2000 4 DOOR BERLINA",  4
"ALFA ROMEO","","2000 GT VELOCE",  2
"ALFA ROMEO","","2000 SPIDER VELOCE",  2
"AUDI","12 MONTHS OR 20000 MILES","100 LS 2 DOOR AUTO",  5
. . .

This message has been edited. Last edited by: j.gross,

Thanks so much for all the replies. Apologies getting back to this late. Will try all the suggestions and will come back with what works and what does not for me. Thanks so much again! :-D