I need to SQZ the whole data set but in 3rd field I need to leave the spaces between the ctrlid and descriptions and spaces between the descriptions words. The 3rd column will always start with the pipe at position 18 for a length of 400, but the descriptions can vary in length. So I need to SQZ the next field delimiter to end of Description3 and then SQZ last 5 fields to that
I have tried some suggestions from the forum but can't get it to work ...any help would be appreciated
Have you checked the "Smart DFSORT Tricks" manual ?
If not, google for it and download.
Using the "Deconstruct and reconstruct CSV records" and the "Squeeze out blanks or other characters" examples, you should be able to get around your problem.
I need to SQZ the whole data set but in 3rd field I need to leave the spaces between the ctrlid and descriptions and spaces between the descriptions words. The 3rd column will always start with the pipe at position 18 for a length of 400, but the descriptions can vary in length. So I need to SQZ the next field delimiter to end of Description3 and then SQZ last 5 fields to that
I have tried some suggestions from the forum but can't get it to work ...any help would be appreciated
/* REXX */
/*-------------------------------------------------------------------*/
/* REXX */
/* (Restructured EXtended eXecutor language) */
/*-------------------------------------------------------------------*/
/* Program Name...: RemSpace */
/* Original Author.: Rick Silvers at rsilvers.com */
/* Original Date...: 03-15-2017 */
/* Program Purpose: READ in a fixed width pipe delimited Teradata */
/* report file and remove leading/trailing spaces */
/* and concat (SQZ) with pipe delimiter '|' so */
/* data can be loaded back into Teradata with */
/* TPT (Teradata Parallel Transporter) delimited */
/* load. */
/* */
/* ( */
/* VARCHAR PRIVATELOGNAME = 'XXXX_WORK_FILE_LOG', */
/* VARCHAR FILENAME = 'DD:DATAIN', */
/* VARCHAR INDICATORMODE='N', */
/* VARCHAR OPENMODE='READ', */
/* VARCHAR FORMAT='DELIMITED', */
/* VARCHAR TEXTDELIMITER = '|', */
/* VARCHAR ACCEPTEXCCESSCOLUMNS = 'Y' */
/* ); */
/*-------------------------------------------------------------------*/
/* Maintenance Log.: */
/*-------------------------------------------------------------------*/
/* MM-DD-CCYY Programmer Comment */
/* ---------- ---------- ------- */
/* 03-15-2017 R.Silvers Created Program */
/*-------------------------------------------------------------------*/
/*-------------------------------------------------------------------*/
/* Allocat Input/Output */
/*-------------------------------------------------------------------*/
"ALLOC F(IN) DS('USERID.FILEIN') REUSE SHR"
"ALLOC F(OUT) DS('USERID.FILEOUT') REUSE SHR"
/*-------------------------------------------------------------------*/
/* Read the fixed length delimited file into STEM */
/*-------------------------------------------------------------------*/
"EXECIO 0 DISKR IN (OPEN"
"EXECIO * DISKR IN (STEM XX."
"EXECIO 0 DISKR IN (FINIS"
"FREE F(IN)"
/*-------------------------------------------------------------------*/
/* Loop thr STEM */
/*-------------------------------------------------------------------*/
maxlines =xx.0
Do a = 1 to maxlines by 1
parse value xx.a with FIELD1,
'|',
FIELD2,
'|',
FIELD3,
'|',
FIELD4,
'|',
FIELD5,
'|',
FIELD6,
'|',
FIELD7,
'|',
FIELD8,
'|',
FIELD9,
'|',
FIELD10,
'|',
FIELD11,
'|',
FIELD12,
'|',
FIELD13,
'|',
FIELD14
/*-------------------------------------------------------------------*/
/* Strip leading/trailing spaces */
/*-------------------------------------------------------------------*/
FIELD1 =strip(FIELD1,b,' ')
FIELD2 =strip(FIELD2,b,' ')
FIELD3 =strip(FIELD3,b,' ')
FIELD4 =strip(FIELD4,b,' ')
FIELD5 =strip(FIELD5,b,' ')
FIELD6 =strip(FIELD6,b,' ')
FIELD7 =strip(FIELD7,b,' ')
FIELD8 =strip(FIELD8,b,' ')
FIELD9 =strip(FIELD9,b,' ')
FIELD10 =strip(FIELD10,b,' ')
FIELD11 =strip(FIELD11,b,' ')
FIELD12 =strip(FIELD12,b,' ')
FIELD13 =strip(FIELD13,b,' ')
FIELD14 =strip(FIELD14,b,' ')
/*-------------------------------------------------------------------*/
/* Concat with '|' and write to output */
/*-------------------------------------------------------------------*/
lin.1 =FIELD1||,
'|'||,
FIELD2||,
'|'||,
FIELD3||,
'|'||,
FIELD4||,
'|'||,
FIELD5||,
'|'||,
FIELD6||,
'|'||,
FIELD7||,
'|'||,
FIELD8||,
'|'||,
FIELD9||,
'|'||,
FIELD10||,
'|'||,
FIELD11||,
'|'||,
FIELD12||,
'|'||,
FIELD13||,
'|'||,
FIELD14
"EXECIO 1 DISKW OUT (STEM LIN."
end
/*-------------------------------------------------------------------*/
/* Close output and FREE and exit */
/*-------------------------------------------------------------------*/
"EXECIO 0 DISKW OUT (FINIS"
"FREE F(OUT)"
exit
I need to SQZ the whole data set but in 3rd field I need to leave the spaces between the ctrlid and descriptions and spaces between the descriptions words. The 3rd column will always start with the pipe at position 18 for a length of 400, but the descriptions can vary in length. So I need to SQZ the next field delimiter to end of Description3 and then SQZ last 5 fields to that
SORT solution works as well
Code:
INREC PARSE=(%=(ABSPOS=5,ENDAT=C'|'),
%=(ENDAT=C'|'),
%1=(ENDBEFR=C'|',FIXLEN=400),
%2=(SUBPOS=1,ENDBEFR=X'00',FIXLEN=400)),
BUILD=(1,4, RDW
5,18, two fixed fields
%1,SQZ=(VL,MID=C' '), squeezed field
%2,JFY=(VL)) last fields shifted left
SORT FIELDS=COPY
END
