|
View previous topic :: View next topic
|
| Author |
Message |
Rick Silvers
New User
Joined: 31 Mar 2012
Posts: 9
Location: USA
|
|
|
|
I have a pipe delimited data set with a layout like this, column 3 with varying length data
| Code: |
| 9999999 |9999999 |MEX.111 Description1 Description2 Description3 |11/06/2016 |11062016 | |record count |Y |
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
Thanks,
Rick
Code'd |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You mean you have one variable-length field, and the designer of the file made it the third field? Simplest (to say) is to get that rearranged.
SQZ/JFY have MID= to establish behaviour for embedded blanks.
However, does FINDREP to change " ¦" to "¦"give you what you want? |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
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. |
|
| Back to top |
|
|
Rick Silvers
New User
Joined: 31 Mar 2012
Posts: 9
Location: USA
|
|
|
|
[quote="Rick Silvers"]I have a pipe delimited data set with a layout like this, column 3 with varying length data
| Code: |
| 9999999 |9999999 |MEX.111 Description1 Description2 Description3 |11/06/2016 |11062016 | |record count |Y |
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
Thanks,
Rick
I fixed with Rexx |
|
| Back to top |
|
|
Rick Silvers
New User
Joined: 31 Mar 2012
Posts: 9
Location: USA
|
|
|
|
| Code: |
/* 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
|
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2276
Location: USA
|
|
|
|
| Rick Silvers wrote: |
| Code: |
| 9999999 |9999999 |MEX.111 Description1 Description2 Description3 |11/06/2016 |11062016 | |record count |Y |
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 |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2276
Location: USA
|
|
|
|
P.S.
If you need to squeeze every separate field, then more PARSE parameters are needed, with SQZ/JFY functions applied to each of them. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
