IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQZ pipe delimited file with 1 column varying length


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Sun Nov 06, 2016 10:25 pm
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: 1348
Location: Israel

PostPosted: Mon Nov 07, 2016 4:11 pm
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
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
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 Member


Joined: 29 Apr 2008
Posts: 939
Location: Maryland

PostPosted: Wed Mar 15, 2017 10:52 pm
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 Member


Joined: 29 Apr 2008
Posts: 939
Location: Maryland

PostPosted: Wed Mar 15, 2017 11:38 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Writng matched records into output fi... DFSORT/ICETOOL 5
No new posts Compare the file record count percent... DFSORT/ICETOOL 4
No new posts compare two file to find position/dat... SYNCSORT 2
No new posts Column names in SYSIBM tables DB2 5
No new posts Converting a file from PD to display ... SYNCSORT 4
Search our Forums:

Back to Top