Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQZ pipe delimited file with 1 column varying length

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Rick Silvers

New User


Joined: 31 Mar 2012
Posts: 9
Location: USA

PostPosted: Sun Nov 06, 2016 8:11 pm    Post subject: SQZ pipe delimited file with 1 column varying length
Reply with quote

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
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Sun Nov 06, 2016 10:25 pm    Post subject: Reply to: SQZ pipe delimited file with 1 column varying length
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1243
Location: Israel

PostPosted: Mon Nov 07, 2016 4:11 pm    Post subject: Reply to: SQZ pipe delimited file with 1 column varying length
Reply with quote

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
View user's profile Send private message
Rick Silvers

New User


Joined: 31 Mar 2012
Posts: 9
Location: USA

PostPosted: Wed Mar 15, 2017 8:53 pm    Post subject: Re: SQZ pipe delimited file with 1 column varying length
Reply with quote

[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
View user's profile Send private message
Rick Silvers

New User


Joined: 31 Mar 2012
Posts: 9
Location: USA

PostPosted: Wed Mar 15, 2017 9:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 189
Location: Maryland

PostPosted: Wed Mar 15, 2017 10:52 pm    Post subject: Re: SQZ pipe delimited file with 1 column varying length
Reply with quote

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
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 189
Location: Maryland

PostPosted: Wed Mar 15, 2017 11:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am
No new posts Garbage on output file Hervey Martinez SYNCSORT 4 Wed Sep 27, 2017 12:50 am
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us